vba VBA中的指针
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9468848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pointers in VBA
提问by Mark
Are there something like pointers in VBA?
VBA中是否有类似指针的东西？
I'm trying to make an excel macro, and want a simple variable to keep track of column count. wst.UsedRange.Columns.Count takes up a lot of space every time I need it, and I'd like to just replace that with a simple name like last_col.
我正在尝试制作一个 excel 宏，并且想要一个简单的变量来跟踪列数。wst.UsedRange.Columns.Count 每次我需要它时都会占用大量空间，我想用一个简单的名字来代替它，比如 last_col。
回答by Alex K.
There arebut not in a way useful in your situation (or much else beyond special cases), why not
这里有，但不是在您的情况非常有用（或者什么其他东西超出了特殊情况下）的方式，为什么不
public function ccount() as long
ccount = wst.UsedRange.Columns.Count
end function
or set a global variable reference to wst.UsedRange.Columnsand read var.countas needed.
或根据需要设置全局变量引用wst.UsedRange.Columns并读取var.count。
回答by Jerry Beaucaire
I'm no fan of UsedRange in most instances. If you were to create a Function to be used over and over by your other macros, I would lean toward this syntax which finds the last column with an actual value in it.
在大多数情况下，我不喜欢 UsedRange。如果您要创建一个由其他宏反复使用的函数，我会倾向于这种语法，它会找到包含实际值的最后一列。
Private Function LastCOL(ws As Worksheet) As Long
With ws
LastCOL = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With
End Function
Private Function LastROW(ws As Worksheet) As Long
With ws
LastROW = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
End With
End Function
Then your other macros can call it by simply feeding the macro a sheet reference:
然后您的其他宏可以通过简单地向宏提供工作表引用来调用它：
Sub RowTest()
On Error Resume Next 'in case the sheet is empty
LR = LastROW(ActiveSheet)
Debug.Print LR
End Sub
