My_Head_Hurts


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 = ""
formB.show
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

Jeff.




Re: Excel: Public variable doesn't work

Andy Pope


Hi,

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

My_Head_Hurts

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,
Jeff.