VBA 公共(public)变量

Question

我在公共(public) i 作为代码的整数部分时遇到了麻烦。我使用 i 来保留当前行的值，这样我就可以使用这个范围我的程序。在我的 for 循环中，它会递增 i，因此它将逐步遍历一列并搜索 v但是，当我尝试在另一组代码中使用“i”时，“i”不再具有值。我不确定全局/公共(public)变量在 VBA 中如何工作或导致此错误的原因。

问题出现 int Sub "yes"和 sub "no"在代码中

Cells(i,lcol).value=" ok " 

和

Cells(i,lcol).value = " updated "

第一组代码如下，它获取我的“i”值

Public V As Integer
Public i As Integer

Private Sub Enter_Click()
Dim EmptyRow As Long

'Audit will only search Master Sheet
Worksheets("Master").Activate

'Find empty row value so we can use that for limit of search
With Sheets("Master")
     EmptyRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
End With

'i needs to be set to minimum limit
'Begin loop of search
For i = 12 To EmptyRow + 1

    If Cells(i, 1).Value = V Then  'AssetNum.Value Then

        'Go to compare userform to display

        Compare.AssetDisplay.Value = AssetNum.Value
        Compare.LocationDisply.Value = Cells(i - 1, 2).Value
        Compare.Show

     End If
  Next i

    'If i gets to emptyrow num then go to non found asset userform
    Unload Me
    NonFoundAsset.Show

 End Sub

Private Sub UserForm_Initialize()

'Read in value from asset num to be comapre in loop
AssetNum.Value = V

End Sub

第二组代码我试图使用公共(public)变量调用“i”，但它没有值

Private Sub No_Click()

Dim ws As Worksheet
Dim lcol As Long

'Make Master Sheet Active
 Worksheets("Master").Activate

Set ws = ThisWorkbook.Sheets("Master")

'Finds next empty column

  With ws
        lcol = .Cells(11, .Columns.Count).End(xlToLeft).Column - 1
  End With

'If the displayed location is not the same as the actual location "No" will be
'selected and the Change User Form will be displayed
'The value under the current audit column will be displayed as updated
 Cells(i, lcol).Value = " Updated "
 Unload Me
 AuditChange.Show

End Sub

Private Sub Yes_Click()

 Dim ws As Worksheet
 Dim lcol As Long

'Make Master Sheet Active
 Worksheets("Master").Activate

Set ws = ThisWorkbook.Sheets("Master")

'Finds next empty column

  With ws
        lcol = .Cells(11, .Columns.Count).End(xlToLeft).Column - 1
    End With

    'If the location displayed is correct "Yes" will be selected and
    'The value "ok" will be displayed in the current audit column

    Cells(i, lcol).Value = " Ok "
    Unload Me
    'Returns to Assetlookup to look for a new asset
    Assetlookup.Show
End Sub

感谢您的帮助，我是 VBA 新手，不明白为什么这不起作用。

Answer 1

我相信用户窗体中的公共(public)变量仅在用户窗体正在运行(已加载)时才可用。要拥有真正的全局变量，请在普通模块中声明它。

可能该变量不可用，并且 VB 无法在其作用域中找到它。如果工具、选项、需要变量声明被关闭，VB将在当前范围内创建一个具有该名称的新变量。因此看起来好像它已经“失去”了它的值(value)。

提示:不要将全局变量称为 i、j、n 等。这些变量通常用作循环和计数器的局部变量。使用明确变量是全局变量的命名约定。我总是在此类变量前加上表示“全局”的 g 前缀，例如:

Public giLoopCounter As Integer;