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?

Try Debug.Print styles(0)...Debug.Print styles(1)....Debug.Print styles(2)....Debug.Print styles(3)..... — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Feb 27, 2018 at 10:14
this answer is not working for me, it does look elegant: stackoverflow.com/questions/1654281/vba-public-array-how-to — Brilsmurfffje
– Brilsmurfffje, Commented Feb 27, 2018 at 10:14

user4039065 · Accepted Answer · 2018-02-27 10:28:50Z

9

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.

answered Feb 27, 2018 at 10:28

user4039065

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

1 Comment

Brilsmurfffje Over a year ago

Works like a charm! Thanks for the extra explanatory code!

Vityata · Accepted Answer · 2018-02-27 10:22:51Z

2

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

edited Feb 27, 2018 at 10:22

answered Feb 27, 2018 at 10:17

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

8 Comments

Brilsmurfffje Over a year ago

Is there a way to declare a public array like you would do in python or java like: array = ("value1", "value2", etc..)

Brilsmurfffje Over a year ago

The ultimate goal of the array is to verify styles of an excel and if they are in an array they need to be written to a txt file

user4039065 Over a year ago

I don't know if the OP was ever expecting a fifth array element but styles(4) leaves 5 elements for a zero-based array.

Vityata Over a year ago

@Brilsmurfffje - it should be in a procedure (within a Sub or Function).

Vityata Over a year ago

@Brilsmurfffje - this is because you do not have Dim Styles As Variant within the sub, but you are using the Public styles(4) As String outside the sub. Write Public styles As Variant outside or Dim Styles As Variant within.

|

Collectives™ on Stack Overflow

VBA Define a simple static public array

2 Answers 2

1 Comment

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related