Global public string variable losing value between subs

Question

I have created a sub "importData", which takes as on optional parameter a string, which should the name of a worksheet to be read from. If it is empty, a default value set elsewhere should be used. More precisely, elsewhere is within the "Workbook_Open" sub.

When I open the workbook and run the importData macro (calling the sub without an argument), everything is fine.

Now, if I play a bit with VBA, not touching the above and VBA execution runs in an error elsewhere, the value of the default variable is dropped. If I run the sub after an error occured, my importData sub displays an error message, that the string is now empty, which is not a valid value of course.

A bit of code: Somewhere in the module, outside of any function or sub:

Public wsImporter                       As String               ' Name of import worksheet.

Somewhere in the initialization sub being called by Workbook_Open:

wsImporter = "Import"

Beginning of sub "importData":

Public Sub importData(Optional ByVal importer As Variant)
   (omitting local variable declaration)
    If IsMissing(importer) Then importer = wsImporter
    If Not chkWsExists(importer) Then
        MsgBox "The specified import sheet does not exist: " & importer, vbCritical, "Microsoft Excel:importData"
        Exit Sub
    End If
...
End Sub

chkWsExists is just a boolean function returning true if the workbook contains a worksheet of the given name, false otherwise. It works fine. When I execute "importData" after a VBA error occured (completely ouside this, say a type mismatch while testing something else), then the Message Box appears and the value of importer is empty.

Essentially, it seems that the value of the public variable was somehow lost by the error. Is that normal behavior? Is there a way around it? Have I done something, to cause this?

This is an issue for me, since other sub's errors can lead to this sub not working anymore, e.g. there was an error caused elsewhere and the value of wsImporter got dropped, making it unusable for the normal user. Possible workaround would be close and open of workbook, but that is not very user friendly :)

Any insight in what's going on would be much appreciated.

Best,

YeOldHinnerk

Please explain why this is happening and how to change the behavior. Thank you. — YeOldHinnerk, Nov 17 '20 at 15:52
Don't use `Public` variables like this. This is a great example why not to. [More reading here on lifetime](https://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba). — BigBen, Nov 17 '20 at 15:53
If you're setting the value during `workbook.Open` then you may as well use public Constants instead of variables: then they won't lose their values if your code crashes or is otherwise reset. Or create a function/property which returns a "config" object which has the configuration settings for that particular workbook. — Tim Williams, Nov 17 '20 at 16:21
Good idea. I actually have this config function already, just need to call it again at the beginning of importData! That is such an easy solution, I'm acutally surprised I didn't think of that right away. Thank you. — YeOldHinnerk, Nov 17 '20 at 22:22

score 0 · Answer 1 · answered Nov 17 '20 at 15:36

0

Move the global variable from a module declaration to be the default of the optional parameter. This means you can also delete the IsMIssing line and more correctly define importer as a string parameter.

Public Sub importData(Optional ByVal ipImporter As String="Importer")
   (omitting local variable declaration)
    If Not chkWsExists(ipImporter) Then
        MsgBox "The specified import sheet does not exist: " & ipImporter, vbCritical, "Microsoft Excel:importData"
        Exit Sub
    End If
...
End Sub

answered Nov 17 '20 at 15:36

freeflow

4,129
3
10
18

Well, yes, but it does change something: importData is essentially part of a library, that should not be individualized per workbook. Workbook indiviualization is done in one place only (in a sub called by Workbook_Open), so not hardcoded worksheet name should appear in the sub importData. – YeOldHinnerk Nov 17 '20 at 15:51
I didn't mean to insult you. If I did, my apologies. I admit that information was missing, but that was why I was trying to keep the literal out of the sub. – YeOldHinnerk Nov 17 '20 at 22:14

Global public string variable losing value between subs

1 Answers1