I have a routine that needs to sometimes display a form to get user input, then hand the result back to the caller. I use a Global variable to do this but the value is not returned. When I use Option Explicit, I get a run-time error, "Variable Not Defined" in the form.

The code is like this:

In the calling module...

Code Snippet

Public PsResult as String
private sub ModuleA()

PsResult = ""
msgbox PsResult
end sub

Then, in the form's code

Code Snippet

PsResult = "derived value"

Note that the form doesn't specify "Public PsResult as String". It shouldn't need to as it should already exist.

What is important to note is that my calling code is within ThisWorkbook (because it's replacing the standard Excel Workbook_BeforeSave function). If I copy a bit of it to a new Module then it works perfectly. So why can't my form see a Public variable defined in ThisWorkbook


Re: Excel: Public variable doesn't work

Andy Pope


Thisworkbook is a special class rather than a code module. So the Public declaration here means the variable is visible to all routines in Thisworkbook rather than all routines in the project.

Either move the declaration of the variable to a standard code module. You can leave the routine ModuleA in thisworkbook if you want.
Or change the way you reference the variable within the userform.

ThisWorkbook.PsResult = "derived value"

Re: Excel: Public variable doesn't work


Thanks Andy

I got around it by writing my data to a Custom Property (it's not a big lot, so this is OK). I just felt that I must have been doing something wrong but if that's the way it works, so be it.

Thanks again,