Hi,

I need to execute some code from an add-in within the Workbook_SheetChange
event. As part of this I need a global array that I can update/use from
within an add-in UDF and this workbook event.

When I define a global array in an add-in module, I can update it via the
UDF's contained in the add-in, but I am unable to reference this array from
within the Workbook_SheetChange event. If I attempt to use the array within
the event it shows as empty, even though it was populated in an earlier step.
If I try to call a UDF function from this event instead, I get the compile
error 'Sub or Function not defined'.

Alternatively, I defined the global array within a module in the xls file.
In this case I can reference the array from the workbook event, but cannot do
so from within the add-in UDF's. This gives me the compile error 'Variable
not defined'.

I'm using Excel 2000.

Thanks!

Try DIMming the array in a module before any functions or sub. Also BE SURE
to DIM it as Public.
--
Gary''s Student - gsnu200746


"BigJimmer" wrote:

Hi,

I need to execute some code from an add-in within the Workbook_SheetChange
event. As part of this I need a global array that I can update/use from
within an add-in UDF and this workbook event.

When I define a global array in an add-in module, I can update it via the
UDF's contained in the add-in, but I am unable to reference this array from
within the Workbook_SheetChange event. If I attempt to use the array within
the event it shows as empty, even though it was populated in an earlier step.
If I try to call a UDF function from this event instead, I get the compile
error 'Sub or Function not defined'.

Alternatively, I defined the global array within a module in the xls file.
In this case I can reference the array from the workbook event, but cannot do
so from within the add-in UDF's. This gives me the compile error 'Variable
not defined'.

I'm using Excel 2000.

Thanks!

That's what I did. I've made that mistake before, so I checked it a few
times before I posted.

"Gary''s Student" wrote:

Try DIMming the array in a module before any functions or sub. Also BE SURE
to DIM it as Public.
--
Gary''s Student - gsnu200746


"BigJimmer" wrote:

Hi,

I need to execute some code from an add-in within the Workbook_SheetChange
event. As part of this I need a global array that I can update/use from
within an add-in UDF and this workbook event.

When I define a global array in an add-in module, I can update it via the
UDF's contained in the add-in, but I am unable to reference this array from
within the Workbook_SheetChange event. If I attempt to use the array within
the event it shows as empty, even though it was populated in an earlier step.
If I try to call a UDF function from this event instead, I get the compile
error 'Sub or Function not defined'.

Alternatively, I defined the global array within a module in the xls file.
In this case I can reference the array from the workbook event, but cannot do
so from within the add-in UDF's. This gives me the compile error 'Variable
not defined'.

I'm using Excel 2000.

Thanks!