CODEWITHSUNDEEP
arraysvbaexcelglobal-variables
VeVi
VeVi

Reputation: 281

Declare Global Array Variables in VBA-Excel

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

enter image description here

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)

Upvotes: 1

Views: 8029

Answers (1)

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 2

Related Questions