forms - Excel VBA代码理解

Question

我正在尝试构建一个基于 excel 的输入表单，我在网上找到了一些东西，我正在尝试理解这些代码:

Dim Hsheet,Isheet As Worksheet
Dim NextRow, oCol As Long
Dim MyRng, MyCell As Range
Dim MyCopy, ClearCells As String

Set Hsheet = Worksheet("InputForm")
Set ISheet = Worksheet("Database")

这是我不明白的部分，有人可以给我解释一下吗？

With Hsheet
     nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With Isheet
    Set myRng = .Range(MyCopy)

    If Application.CountA(myRng) <> myRng.Cells.Count Then
        MsgBox "Please fill in all the cells!"
        Exit Sub
    End If
End With

还有这部分，有人可以向我解释一下吗？

With Hsheet
    .Cells(nextRow, "a").Value = Application.UserName
    oCol = 1
    For Each myCell In MyRng.Cells
        Hsheet.Cells(NextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With

提前致谢 :)

With Isheet
  On Error Resume Next
     With .Range(ClearCells).Cells.SpecialCells(xlCellTypeConstants)
          .ClearContents
          Application.Goto .Cells(1) ', Scroll:=True
     End With
  On Error GoTo 0
End With

Answer 1

我可以解释代码的作用，但我想提的很少:)

一个

Dim Hsheet,Isheet As Worksheet
Dim NextRow, oCol As Long
Dim MyRng, MyCell As Range
Dim MyCopy, ClearCells As String

这不是声明变量/对象的正确方法例如，如果您考虑这一行

Dim Hsheet,Isheet As Worksheet

在这里，只有 Isheet已被声明为 工作表 而不是 Hsheet . Hsheet自动变为 变体 .正确的方法是

Dim Hsheet As Worksheet, Isheet As Worksheet
Dim NextRow As Long, oCol As Long
Dim MyRng As Range, MyCell As Range
Dim MyCopy As String, ClearCells As String

乙

With Hsheet
     nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

这段代码的作用是尝试找到在 Col A 中有数据的最后一行，然后向下偏移一行以获得下一个空行，以便您可以写入它。

提到了另一种编写相同内容的方法 here所以上面的代码也可以写成

With Hsheet
     nextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
End With

中号

With Isheet
    Set myRng = .Range(MyCopy)

    If Application.CountA(myRng) <> myRng.Cells.Count Then
        MsgBox "Please fill in all the cells!"
        Exit Sub
    End If
End With

我相信 MyCopy应该保持一些我在你的代码中看不到的值。假设它拥有一个有效的单元格地址，代码试图做的是通过比较单元格计数与填充的单元格数来确保所有单元格都被填满。

电话

With Hsheet
    .Cells(NextRow, "a").Value = Application.UserName
    oCol = 1
    For Each myCell In MyRng.Cells
        Hsheet.Cells(NextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With 

这也很简单。代码存储 UserName在 Col A 的下一个可用单元格中，然后将 Range MyRng 中的值存储在 Sheet Isheet 中在表 Hsheet 的 Col A 中

高温高压