Declare Global Array Variables in VBA-Excel

Question

I'm trying to work with global dynamical arrays in vba excel. the idea: I fill in information via an userform:

Then I click on the button ADD. There the "columnletter" and "name column" is saved in 2 arrays (arrBoxColumnLetters, arrBoxColumnNames). When I click on Start I want to use the information from the arrays above. But the arrays are empty...

My code:

My global variables:

Public i As Integer
Dim arrBoxColumnLetters() As Variant
Dim arrBoxColumnNames() As Variant

Private Sub UserForm_Initialize()

    'empty textbox
    TxtBoxExcelName.Value = ""
    TxtBoxStartRow.Value = ""
    TxtBoxTitleRowCR.Value = ""
    TxtBoxTitleRowCG.Value = ""
    TxtBoxTitleRowCB.Value = ""
    TxtBoxHeaderRowCR.Value = ""
    TxtBoxHeaderRowCG.Value = ""
    TxtBoxHeaderRowCB.Value = ""
    TxtBoxCLetter.Value = ""
    TxtBoxCName.Value = ""
    TxtBoxColumn.Value = ""
    TxtBoxTab.Value = ""
    'set focus on TxtBoxStartRow
    TxtBoxExcelName.SetFocus

    'initialize variables
    i = 0
    End Sub


    Private Sub BtnAddC_Click()
    Dim ColumnDataOri, ColumnData As String

    ReDim Preserve arrBoxColumnLetters(i + 1)
    ReDim Preserve arrBoxColumnNames(i + 1)

    ColumnDataOri = TxtBoxColumn.Value
    ColumnData = TxtBoxCLetter.Value & vbTab & vbTab & TxtBoxCName.Value
    TxtBoxColumn.Value = ColumnDataOri & vbCrLf & ColumnData

    arrBoxColumnLetters(i) = TxtBoxCLetter
    arrBoxColumnNames(i) = TxtBoxCName

    TxtBoxCLetter.Value = ""
    TxtBoxCName.Value = ""
    TxtBoxCLetter.SetFocus
    i = i + 1

    End Sub

    Private Sub BtnCancel_Click()
        Unload Me
    End Sub

    Private Sub BtnClear_Click()
        Call UserForm_Initialize
    End Sub

    Private Sub BtnSart_Click()
        Dim sh As Worksheet
        Dim wbori As Workbook
        Dim strRGBTitleRow, strRGBHeaderRow, strFilenameOrigineel, strBoxColumnInfo As String
        Dim arrBoxColumnInfo(), arrBoxColumnLetters(), arrBoxColumnNames() As String
        Debug.Print "i=" & i
        ReDim Preserve arrBoxColumnLetters(i + 1)
        ReDim Preserve arrBoxColumnNames(i + 1)

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        TxtBoxSave.Value = ""

        strRGBTitleRow = TxtBoxTitleRowCR.Value & TxtBoxTitleRowCG.Value & TxtBoxTitleRowCB.Value
        strRGBHeaderRow = TxtBoxHeaderRowCR.Value & TxtBoxHeaderRowCG.Value & TxtBoxHeaderRowCB.Value
        strFilenameOrigineel = TxtBoxExcelName.Value


        Debug.Print arrBoxColumnLetters(0)
        Debug.Print arrBoxColumnNames(0)

Don't use Dim at module-level. Use Public for public/global variables, and Private for private ones. Dim works exactly like Private, and Public works exactly like the obsolete/deprecated Global scope. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 18, 2018 at 14:12
Also, anything declared in a form's code-behind is instance state, since a form is a class that defines an object and so its public members need to be accessed through an instance of that class. Global variables are declared in standard/procedural modules, not classes/forms/worksheets. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 18, 2018 at 14:16

Vityata · Accepted Answer · 2018-06-18 14:17:33Z

2

Global variables are rarely a good idea in VBA. However, to make sure that your variables work, declare these in a separate module, not on the form. Like this:

Public i As Integer
Public arrBoxColumnLetters() As Variant
Public arrBoxColumnNames() As Variant

Furthermore - write Option Explicit on the top of your code. It will let you know, that the variable arrBoxColumnLetters is declared twice, thus this is a bit problematic.

Option Explicit MSDN

edited Jun 18, 2018 at 14:17

answered Jun 18, 2018 at 14:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

6 Comments

VeVi Over a year ago

Thank you for the tips. If you say that global variables are a bad idea, how do you suggest I solve this problem? basically, I want to use the variables from the "ADD" button in the "START" prcedure.

VeVi Over a year ago

Arrays are not allowed as "public members of object modules"

Vityata Over a year ago

@VeVi - Avoiding global variables is an architectural problem, thus it is a bit offtopic - there is not a simple solution to it. And concerning the Arrays are not allowed as "public members of object modules" - what do you mean?

Mathieu Guindon Over a year ago

@VeVi seems you skipped the part where this answer says "write them in a separate module, not on the form".

VeVi Over a year ago

Sorry, you are right, forgot the separate module part. TX!

|

Collectives™ on Stack Overflow

Declare Global Array Variables in VBA-Excel

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related