Declare Public Variablen and assign values - VBA

Question

Is there a way to declare a public variable and also assign value?

This is the code in a sub which works perfectly:

Option Explicit

Sub test()


    Dim arrStatistics As Variant

    'Set arrStatistics
    arrStatistics = Array("H/T - Goal Scored", "H/T - Goal Conceded", "H/T - Both teams Scored", "H/T - Over 0.5", "H/T - Over 1.5", "H/T - Over 2.5", "H/T - Result" _
                , "F/T - Goal Scored", "F/T - Goal Conceded", "F/T - Both teams Scored", "F/T - Over 0.5", "F/T - Over 1.5", "F/T - Over 2.5", "F/T - Result")

End Sub

This is how a declare the variable to act as Public:

Public arrStatistics As Variant

BUT when i use this i receive an error:

Public arrStatistics As Variant

'Set arrStatistics
arrStatistics = Array("H/T - Goal Scored", "H/T - Goal Conceded", "H/T - Both teams Scored", "H/T - Over 0.5", "H/T - Over 1.5", "H/T - Over 2.5", "H/T - Result" _
                , "F/T - Goal Scored", "F/T - Goal Conceded", "F/T - Both teams Scored", "F/T - Over 0.5", "F/T - Over 1.5", "F/T - Over 2.5", "F/T - Result")

For a non-array value that doesn't change (i.e. it is constantly the same value ), you can also use a Const, like so: Public Const SomeText As String = "This is a Constant!". But, to reiterate, this won't work with an Array. — Chronocidal
– Chronocidal, Commented Sep 12, 2019 at 11:47

Pᴇʜ · Accepted Answer · 2019-09-05 13:15:12Z

4

No, you must declare your variable public outside the procedure

Option Explicit

Public arrStatistics As Variant

And then initialize the content with a procedure.

Public Sub InitPublicVariabels()

    arrStatistics = Array("H/T - Goal Scored", "H/T - Goal Conceded", "H/T - Both teams Scored", "H/T - Over 0.5", "H/T - Over 1.5", "H/T - Over 2.5", "H/T - Result" _
            , "F/T - Goal Scored", "F/T - Goal Conceded", "F/T - Both teams Scored", "F/T - Over 0.5", "F/T - Over 1.5", "F/T - Over 2.5", "F/T - Result")

End Sub

But instead of having large data in the code it might be an option to put the data into a hidden worksheet, that you can read into the array easily. Much more convenient to edit the data, and a good practice to seperate code and data.

edited Sep 5, 2019 at 13:15

answered Sep 5, 2019 at 13:00

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

Declare Public Variablen and assign values - VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related