VBA Define a simple static public array

3
votes

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?

2
excelvba
Try Debug.Print styles(0)...Debug.Print styles(1)....Debug.Print styles(2)....Debug.Print styles(3)..... - Foxfire And Burns And Burns
this answer is not working for me, it does look elegant: stackoverflow.com/questions/1654281/vba-public-array-how-to - Brilsmurfffje

2 Answers

7
votes

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.

2
votes

This is how to print the array (twice):

Public styles(4) As String

Sub Populate()
    styles(0) = "Settings"
    styles(1) = "Titles"
    styles(2) = "Comment"
    styles(3) = "Direct Copy"

    '1. print is here ---v
    Dim cnt As Long
    For cnt = LBound(styles) To UBound(styles)
        Debug.Print styles(cnt)
    Next cnt
    '--------------------^        
    '2. print is here ---v
    Debug.Print Join(styles, vbCrLf)
    '--------------------^
End Sub

This is what you get (twice):

Settings
Titles
Comment
Direct Copy

The first time you loop by the elements and print each of them on a new line. The second time you return a string created by joining a number of substrings contained in the array.

Join MSDN Reference