Vba Excel Global Array didn't keep values

Question

I'm new to stackoverflow and currently in real need of help.

I'm fighting since several days with an array of strings, which i declare as public or even private in a module.

Public unitTypes(1 To 3, 1 To 4) As String

In my first Sub in that particulare module I write several Strings into that array.

Public Sub WriteBoxes()
[...]

unitTypes(1, 1) = "Durchschnitt"
unitTypes(2, 1) = "avg"
unitTypes(1, 2) = "Maximum"
unitTypes(2, 2) = "max"
[...]

If I test it via MsgBox at the end of the Sub, all the strings are still in the array.

Public Sub DeleteData()
[...]
MsgBox unitTypes(1, 2)
[...]

But if i want to read any of that strings in another Sub in the same module the strings are lost and I just have an empty ("") array.

Can anybody tell me what am I missing?

Thanks for your help in advance, Daniel

are you sure you triggered first WriteBoxes sub? you are testing (1,2) item- are you written any value there while I can see you set (2,1) item but not (1,2). — Kazimierz Jawor
– Kazimierz Jawor, Commented Dec 22, 2016 at 9:41
Did you place the Public unitTypes at the top of the module? — rohrl77
– rohrl77, Commented Dec 22, 2016 at 9:53
Yes, it is mentioned at the beginning of the module. @rohrl77. — nooby16
– nooby16, Commented Dec 22, 2016 at 10:16

LS_ᴅᴇᴠ · Accepted Answer · 2016-12-22 11:55:02Z

4

From my experience, each time code execution is stopped (clicking Reset, accepting End when error happens, workbook is closed, ...), variable data is lost. To avoid such, I uses constants or properties with cache:

Example:

Public Property Get Var
     Static sVar, inited as Boolean
     If Not inited then
         sVar = "something"
         inited = True
     End If
     Var = sVar
End Property

This way, everytime you try to access unitTypes, property will populate data or return cached data.

In your case, being an 2D array, you may use something like:

Private pUnitTypes(1 To 3, 1 To 4) As String
Public Property Get unitTypes(ByVal i1, ByVal i2) As String
     Static inited as Boolean
     If Not inited then
         pUnitTypes(1, 1) = "Durchschnitt"
         pUnitTypes(2, 1) = "avg"
         pUnitTypes(1, 2) = "Maximum"
         pUnitTypes(2, 2) = "max"
         '...
         inited = True
     End If
     unitTypes = pUnitTypes(i1, i2)
End Property

Public Property Let unitTypes(ByVal i1, ByVal i2, ByVal Value As String)
     'Force call to property Get to ensure array is populated
     Dim dummy as String
     dummy = unitTypes(i1, i2)
     punitTypes(i1,i2) = Value
End Property

Of course, when accessing unitType(1,2) you're acessing a String function, not an array, but it may be transparent in your code usage.

edited Dec 22, 2016 at 11:55

answered Dec 22, 2016 at 9:55

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

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

6 Comments

nooby16 Over a year ago

Thanks, @LS_dev. But I have a problem with understanding/implementing your suggestion. If I put it like that into my module and try to compile it, it'll prompt "Can't assign to read-only property". Can you tell me, what is my mistake?

LS_ᴅᴇᴠ Over a year ago

Yes, you must also specify Property Put... I'll edit.

LS_ᴅᴇᴠ Over a year ago

Property Let, I mean!

nooby16 Over a year ago

While I got another error, I figured out, that properties procedures are class related. Am I right? So I should implement your code into a class object!? Do I have to care about the class name? I'm totaly new to property procedures, so sorry for my ongoing questionnaire.

LS_ᴅᴇᴠ Over a year ago

Yes, it's related to class modules but allowed in code modules. So, you don't need to take care about classes.

|

tretom · Accepted Answer · 2016-12-22 09:47:30Z

1

(did you call your WriteBoxes first?) A small test that works for me:

Public unitTypes(1 To 3, 1 To 4) As String
Sub WriteBoxes()
    unitTypes(1, 1) = "Durchschnitt"
    unitTypes(1, 2) = "avg"
End Sub
Sub test2()
    Call WriteBoxes
    MsgBox (unitTypes(1, 1))
    MsgBox (unitTypes(1, 2))
End Sub

answered Dec 22, 2016 at 9:47

tretom

6451 gold badge9 silver badges18 bronze badges

1 Comment

nooby16 Over a year ago

Thank's @tretom, that test works for me, too. But in my case WriteBoxes() should only run once, as it also does some other things. the reason of the two different procedures is, to give the user time to edit the sheet inbetween.

Collectives™ on Stack Overflow

Vba Excel Global Array didn't keep values

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related