VBA Define a simple static public array

Question

I'm trying to accomplish this obviously simple thing, but somehow VBA keeps serving me weird errors. I would like to have a global array named styles containing the following strings: Settings, Titles, Comment and Direct Copy. in Code:

Public styles(4) As String

Directly assigning the array was not working for me so i did this via a sub:

sub Populate()
  styles(0) = "Settings"
  styles(1) = "Titles"
  styles(2) = "Comment"
  styles(3) = "Direct Copy"
  Debug.Print styles
End Sub

However this does not work as it gives a compile error: Type mismatch on the debug.print line... The expected result was something like: ("Settings", "Titles", ..) etc like any programming language would return.

So how do I get a public array containing strings in VBA Excel such that I can use them in the same module across functions and subs?

Answer 1

Try this form of public declaration and array element assignment.

Option Explicit

Public styles As Variant

Sub printStyles()

    styles = Array("Settings", "Titles", "Comment", "Direct Copy")

    Debug.Print LBound(styles) & "to" & UBound(styles)

    Dim i As Long
    For i = LBound(styles) To UBound(styles)
        Debug.Print styles(i)
    Next i

    Debug.Print Join(styles, ", ")

End Sub

BTW, there a reserved Styles Object which you may have difficulty using if you continue to use reserved words as the names of your public and private variables.