时间戳

首页 > 解决方案 > VBA_多个公共或全局变量以一种简单的方式

excel - VBA_多个公共或全局变量以一种简单的方式

问题描述

我正在做一个项目，我想使用将在不同模块中使用的全局变量。现在我的代码非常冗长，因为需要使用多个变量，即超过 10 个。每次我不得不使用“Function FiltCol As Variant”和“Function LastRow As Long”。有没有办法简单地将所有变量包含在一个“函数”中？

Option Explicit
Function FiltCol() As Variant
    FiltCol = Rows("1:1").Find(What:="Emp ID", LookAt:=xlWhole).Column
End Function
Function LastRow() As Long
    LastRow = Cells(Rows.Count, FiltCol).End(xlUp).Row
End Function

Function FiltCol1() As Variant
    FiltCol1 = Rows("1:1").Find(What:="Emp Name", LookAt:=xlWhole).Column
End Function
Function LastRow1() As Long
    LastRow1 = Cells(Rows.Count, FiltCol1).End(xlUp).Row
End Function

Function FiltCol2() As Variant
    FiltCol2 = Rows("1:1").Find(What:="Designation", LookAt:=xlWhole).Column
End Function
Function LastRow2() As Long
    LastRow2 = Cells(Rows.Count, FiltCol2).End(xlUp).Row
End Function

Function FiltCol3() As Variant
    FiltCol3 = Rows("1:1").Find(What:="Country", LookAt:=xlWhole).Column
End Function
Function LastRow3() As Long
    LastRow3 = Cells(Rows.Count, FiltCol3).End(xlUp).Row
End Function

等等....我想使用超过 10 个变量。请告知是否所有这些变量都可以容纳在单个“功能”中，以便在不同的模块中使用。

标签： excelvba

解决方案

“全局变量”和“非常冗长的代码”是代码维护者最害怕的短语！您可能应该使用类模块并采用面向对象的方法，但如果您只想从函数返回多个变量，则可以使用数组。

Option Explicit

Sub test()
   Dim rng As Range, s As String, ar
   s = "Emp ID"
   Set rng = Rows("1:1")
   ar = FiltCol(rng, s)
   MsgBox s & " Col=" & ar(0) & " Rows=" & ar(1)
End Sub

Function FiltCol(rng As Range, s As String) As Variant
    Dim ws As Worksheet, found As Range
    Dim iCol As Integer, iLastRow As Long
    
    Set ws = rng.Parent
    Set found = rng.Find(What:=s, LookAt:=xlWhole)
    If found Is Nothing Then
        FiltCol = Array(0, 0)
    Else
        iCol = found.Column
        iLastRow = ws.Cells(Rows.Count, iCol).End(xlUp).Row
        FiltCol = Array(iCol, iLastRow)
    End If
    
End Function

推荐阅读