So today's problem is getting me mad because that should be easy and i can not find the answer :
How to declare a public array in VBA ? I'm using an array with the letters A, B, C,... because i'm working with Excel cells, and i don't want to declare it in every function i create, right ? I've tried to look on the web first and i read that you have to declare it in a different module, so that's what i've done :
Public colHeader As String
colHeader = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
But Visual Basic doesn't like it...
So what shall i do ?
Thank's a lot :)
Edit : the problem is more about asigning values to the array than to declare it
Declare array as global across subs in a application:
Public GlobalArray(10) as String
GlobalArray = Array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L')
Sub DisplayArray()
Dim i As Integer
For i = 0 to UBound(GlobalArray, 1)
MsgBox GlobalArray(i)
Next i
End Sub
Method 2: Pass an array to sub. Use ParamArray.
Sub DisplayArray(Name As String, ParamArray Arr() As Variant)
Dim i As Integer
For i = 0 To UBound(Arr())
MsgBox Name & ": " & Arr(i)
Next i
End Sub
ParamArray must be the last parameter.