In VBA, How Do I Edit a Global Variable in One Module and Reference it in Another?

Question

I'm having a difficult time trying to use global variables across modules in VBA.

I have a two-dimensional array that I am using to store values from a sheet in Excel. I want to create an undo button, where I retrieve the last stored values, what I'm looking at is using a global variable, that is edited in one module and referenced in another.

The issue is, I don't think that the program is allowing me to carry over the changes from one module into another. Is there anything I can do to facilitate this? Below is a summary of what I'd like to accomplish:

Public empsTot As Variant

Sub update()
    ReDim Preserve empsTot(5, numOfEmp, numOfFields)
    'emps(,,) would be range extracted from excel
    For i = 0 To numOfFields
        For j = 0 To numOfEmp
            empsTot(0, j, i) = emps(j, i)
        Next
    Next
    'repeat the above process for each new entry, changing empsTot(++,j,i)
End sub

Sub retrieveValues()
    For i = 0 To 6
        For j = 0 To 3
            'add values to sheet using something like
            Range = empsTot([last value],j,i)
        Next
    Next
End Sub

Public week As Integer
Public empsTot As Variant

Sub update()
week = week + 1
If week > 5 Then
    week = 5
End If

Worksheets("Data").Activate
Dim numOfEmp
Let numOfEmp = ThisWorkbook.Sheets("Accessory Sheet").Range("I2")
numOfEmp = numOfEmp
ReDim emps(numOfEmp, 6)
Dim numOfFields
Let numOfFields = 5
ReDim Preserve empsTot(5, numOfEmp, numOfFields)
For i = 0 To numOfEmp
    Dim offset As Integer
    offset = i + 3
    emps(i, 0) = Range("E" & (offset))
    emps(i, 1) = Range("F" & (offset))
    emps(i, 2) = Range("I" & (offset))
    emps(i, 3) = Range("J" & (offset))
    emps(i, 4) = Range("K" & (offset))
    emps(i, 5) = Range("B" & (offset))
    Debug.Print ("^" & emps(i, 5))
Next
Debug.Print ("Week is " & week)
For i = 0 To numOfFields
    For j = 0 To numOfEmp
        empsTot(1, j, i) = emps(j, i)
        Debug.Print (">" & empsTot(1, j, i))
        Debug.Print ("i" & CStr(i))
        Debug.Print ("j" & CStr(j))
    Next
Next
Worksheets("Accessory Sheet").Activate
Dim ColNo
Dim empIndA
empIndA = 0
Dim empIndB
empIndB = 0

For a = 2 To numOfFields + 1
    For b = 2 To (numOfEmp + 2)
        ColNo = a
        CurCol = Split(Cells(, ColNo).Address, "$")(1)
        ThisWorkbook.Sheets("Accessory Sheet").Range((CurCol) & CStr(b + 10)).Value = emps(empIndB, empIndA) - ThisWorkbook.Sheets("Accessory Sheet").Range((CurCol) & CStr(b)).Value
        empIndB = empIndB + 1
    Next
    empIndB = 0
    empIndA = empIndA + 1
Next
ColNo = 0
For a = 0 To numOfFields
    For i = 0 To numOfEmp
        ColNo = a + 2
        CurCol = Split(Cells(, ColNo).Address, "$")(1)
        CurCol = CStr(CurCol)
        ThisWorkbook.Sheets("Accessory Sheet").Range((CurCol) & CStr(i + 2)).Value = emps(i, a)
    Next
Next
End Sub
Sub retrieveValues()
For i = 0 To 2
    For j = 0 To 2
        Debug.Print (empsTot(1, 1, 1))
    Next
Next
End Sub

Public variables should be accessible in all modules; I expect there is more going on; can you post your actual code instead of your summarized version? — Adam Martin, Jul 01 '16 at 19:04
If you declare a global variable it should be accessible in any module. http://stackoverflow.com/a/27578082/2867756 — Mátray Márk, Jul 01 '16 at 19:05
Thanks for replying guys. OK, well, that's good news. Could you take a look at my full code? Posted it below the original sample. — RWA4ARC, Jul 01 '16 at 19:21
`Public empsTot As Variant` should be in one place only - do not declare it in the other modules. Also `Preserve` only lets you resize the *last dimension* of a multi-dimensional array: the other dimensions cannot be changed. — Tim Williams, Jul 01 '16 at 21:01
Thanks, that seems to have been the issue. Everything is working fine now. — RWA4ARC, Jul 05 '16 at 12:01

score 0 · Answer 1 · answered Jul 01 '16 at 20:24

0

I believe the issue was caused by Preserve. Removing it and resetting the module seems to have fixed the issue, but I'm not sure why it was causing the issue. I never resized anything, but I did try referencing it.

answered Jul 01 '16 at 20:24

RWA4ARC

109
3
14

`but I'm not sure why it was causing the issue` Tim WIlliams has addressed that in the comments above... – SierraOscar Jul 01 '16 at 23:07
Thanks, that explains it. – RWA4ARC Jul 05 '16 at 12:01

In VBA, How Do I Edit a Global Variable in One Module and Reference it in Another?

1 Answers1