VBA公共模块级变量初始化：如何仅计算一次最后非空行

我的VBA模块包含多个子过程，多数子过程都需要计算当前工作簿第二个工作表的最后非空行，以此避免处理不必要的单元格。因为整个过程中工作表内容不会被修改，所以我希望仅初始化一次这个变量，而非在每个子过程中重复执行Cells(Rows.Count, "A").End(xlUp).Row这段代码。

目前的代码能正常运行，但每次子过程都会重新计算最后行：

Option Explicit

Public lastrow As Long

Public Sub CleanOrderNumbers()

 With Worksheets(2)
 
        Dim range As range
           
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set range = .range("A2:A" & lastrow)
        range.Replace What:="#", Replacement:="OrdNo"

 End With

End Sub

我尝试用函数初始化全局变量，但没有成功：

Option Explicit

Public lastrow As Long

Public Function myrow()

lastrow = Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row
'alternatively as a constant?
'Const lastrow As Integer = Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row

End Function

Public Sub CleanOrderNumbers()

 With Worksheets(2)
 
        Dim range   As range
           
        Set range = .range("A2:A" & lastrow)
        range.Replace What:="#", Replacement:="OrdNo"

End With

End Sub

想问是语法有遗漏，还是需要换一种实现方法？

可行解决方案 #

方法1：初始化子过程+自动触发

写一个专门的初始化子过程，让它在工作簿打开时自动运行，或者在第一个子过程执行前手动调用：

Option Explicit

Public lastrow As Long

' 初始化全局变量的子过程
Public Sub InitLastRow()
    lastrow = Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row
End Sub

' 放在ThisWorkbook模块中，实现打开工作簿时自动初始化
Private Sub Workbook_Open()
    InitLastRow
End Sub

Public Sub CleanOrderNumbers()
    With Worksheets(2)
        Dim range As range
        Set range = .range("A2:A" & lastrow)
        range.Replace What:="#", Replacement:="OrdNo"
    End With
End Sub

' 其他需要用到lastrow的子过程示例
Public Sub AnotherProcess()
    Debug.Print "最后非空行：" & lastrow
End Sub

方法2：静态函数缓存结果

如果不想用全局变量，可以用静态函数缓存计算结果，确保只计算一次：

Option Explicit

Public Function GetLastRow() As Long
    Static cachedRow As Long
    ' 缓存值为0时，说明还未计算过
    If cachedRow = 0 Then
        cachedRow = Worksheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    End If
    GetLastRow = cachedRow
End Function

Public Sub CleanOrderNumbers()
    With Worksheets(2)
        Dim range As range
        Set range = .range("A2:A" & GetLastRow())
        range.Replace What:="#", Replacement:="OrdNo"
    End With
End Sub

为什么之前的方法失败？ #

你写的myrow函数只是给全局变量赋值，但从未调用过这个函数，导致lastrow一直是默认值0，自然无法正常工作。另外，Const不能用来定义这个值——常量必须是编译时就能确定的固定值，而工作表最后行是运行时才能获取的动态值，所以Const在此场景不适用。

内容的提问来源于stack exchange，提问作者Aline