12

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

Improve this question

8 Answers 8

Reset to default
10

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

9

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.

Improve this answer

4 Comments

First line of example 1 gives a very annoying compile error in Access 2007.
I copied the very same code to VBA to test it out. When doing so I receive a compilation error. While highlighting the "A" in the array to be assigned it says that this is not allowed outside of a procedure. Any hints?
Yes, I don't know why this answer is the most upvoted as it is wrong. You cannot modify an array outside of a sub. As other answers point out, you should create a sub that modifies it, or modify it with Workbook_Open() or the like
First 2 lines give following problem (because of single quote ' I think): Erreur de compilation (Compilation error) Attendu : Expression (Waiting for : Expression) Changing to double quotes give a compilation error when lauching the code : Instruction incorrecte à l'extérieur d'une procédure. (Incorrect instruction outside of a procedure)
1 
Option Explicit

Public colHeader

Sub test()

    ReDim colHeader(11)
    colHeader = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
End Sub

Sub verify_test()

    Dim i As Integer
    For i = LBound(colHeader) To UBound(colHeader)
        MsgBox "colHeader( " & i & " ) = " & colHeader(i)
    Next
End Sub
Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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))
Improve this answer

Comments

0 
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 : " & myarray(Count)
     Next 
End Function
Improve this answer

1 Comment

This won't work - you cannot modify array elements outside of a function or sub. You would get a compile error on line 4.
0

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

Improve this answer

Comments

0

What actually worked for me was to declare a public function (anywhere, really):

Public Function return_colheaders() As Variant
    Dim colheader
    colheader= Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
    return_colheaders= colheader
End Function
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.