A question on VBA coding in Excel 10.

I maintain various items of status in a custom data structure (Budget) defined by a Type statement. i.e.
Type Budget
....
End Type
The definition of this data structure is coded in a Module outside the subroutines and functions that define my code. I also set up a Public reference (bref) to this data structure in the same location i.e.
Public bref As Budget

The data structure Budget is populated in my Main macro that is called to create a summary of some data from an external database. However when I enter the event handler Worksheet_BeforeDoubleClick, I find I have no access to the data in Budget.

I expected to have access to Budget everywhere in my code including worksheet event handlers. Is there another technique to pass data into the set up event handlers set up by excel.

Thanks

A question on VBA coding in Excel 10.

I maintain various items of status in a custom data structure
(Budget) defined by a Type statement. i.e. Type Budget
...
End Type
The definition of this data structure is coded in a Module outside
the subroutines and functions that define my code. I also set up a
Public reference (bref) to this data structure in the same location
i.e. Public bref As Budget

The data structure Budget is populated in my Main macro that is
called to create a summary of some data from an external database.
However when I enter the event handler Worksheet_BeforeDoubleClick,
I find I have no access to the data in Budget.

I expected to have access to Budget everywhere in my code including
worksheet event handlers. Is there another technique to pass data
into the set up event handlers set up by excel.

Thanks

Your assumption is correct! As per your explanation you should have
access to 'bref' from anywhere...

var = bref.item

...where 'item' represents what appears in the intellisense popup after
typing 'bref.' in any line of code.

There's probably worth in mentioning that this...

var = bref.item &bref.otheritem

...may inhibit the intellisense feature under some conditions. (I find
this quirky behavior more & more lately!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thank you for your prompt reply that reassured me that Public variables work as I expected. I confirmed this by coding up a simple test case.

Looking at my code more closely, I noticed in the event handler Worksheet_BeforeDoubleClick the statement

Dim bref as Budget

Now, I realize this created a new local variable. Commenting out this statement and using bref gives me the Public variable defined in Main and thus access to the Budget data structure.

If this simple coding error had occurred in another module, I probably would have quickly spotted it. However, having the error only occur in an event handler led to the false assumption that perhaps Excel's event handlers have special rules. Their interface parameters are specified by Excel and must not be changed, but that is all.

Thank you for your prompt reply that reassured me that Public
variables work as I expected. I confirmed this by coding up a simple
test case.

Looking at my code more closely, I noticed in the event handler
Worksheet_BeforeDoubleClick the statement

Dim bref as Budget

Now, I realize this created a new local variable. Commenting out
this statement and using bref gives me the Public variable defined
in Main and thus access to the Budget data structure.

If this simple coding error had occurred in another module, I
probably would have quickly spotted it. However, having the error
only occur in an event handler led to the false assumption that
perhaps Excel's event handlers have special rules. Their interface
parameters are specified by Excel and must not be changed, but that
is all.

Glad you caught the problem! Best wishes...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion