VBA Public Array how to

Question

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

User · Accepted Answer

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.

User · Answer

You are using the wrong type  The Array      function returns a Variant  not a String   Thus  in the Declaration section of your module  it does not need to be a different module    you define  Public colHeader As Variant   and somewhere at the beginning of your program code  for example  in the Workbook Open event  you initialize it with  colHeader   Array  A    B    C    D    E    F    G    H    I    J    K    L     Another  simple  alternative would be to create a function that returns the array  e g  something like  Public Function GetHeaders   As Variant     GetHeaders   Array  A    B    C    D    E    F    G    H    I    J    K    L   End Function   This has the advantage that you do not need to initialize the global variable and the drawback that the array is created again on every function call

User · Answer

Option Explicit      Public myarray  1 To 10       Public Count As Integer      myarray 1     A       myarray 2     B       myarray 3     C       myarray 4     D       myarray 5     E       myarray 6     F       myarray 7     G       myarray 8     H       myarray 9     I       myarray 10     J  Private Function unwrapArray        For Count   1 to UBound myarray         MsgBox  Letters of the Alphabet      amp  myarray Count       Next  End Function

User · Answer

This worked for me  seems to work as global    Dim savePos 2 To 8  As Integer   And can call it from every sub  for example getting first element    MsgBox  savePos 2

User · Answer

Try this   Dim colHeader 12  colHeader     A    B    C    D    E    F    G    H    I    J    K    L     Unfortunately the code found online was VB NET not VBA

User · Answer

Well  basically what I found is that you can declare the array  but when you set it vba shows you an error   So I put an special sub to declare global variables and arrays  something like   Global example 10  As Variant  Sub set values    example 1    1 example 2    1 example 3    1 example 4    1 example 5    1 example 6    1 example 7    1 example 8    1 example 9    1 example 10    1  End Sub   And whenever I want to use the array  I call the sub first  just in case  call set values  Msgbox example 5    Perhaps is not the most correct way  but I hope it works for you

[arrays] VBA Public Array : how to?

Examples related to arrays

Examples related to vba