Hi,

I am building a excel add-in (VBA) application for risk analysis;
add-in has a commandbar and userform to allow user to select/enter
input data. User is allowed to select multiple workbooks simultaneously
and run data fetch using add-in.
To my suprise I found that excel allows the variable values in the
addin be shared across workbook. How can I avoid this from happening,
is there any work around.

In the code below (Extracted from addin module), I found that variable
"I" shares same values across different workbook


Option Explicit

Dim CmdItem As CommandBarControl

Dim i As Long
Private Sub Pr_LoadDisplay()

On Error GoTo ERRPART

MsgBox (i)
'MsgBox ThisWorkbook.Name
MsgBox ActiveWorkbook.Name

Exit Sub
ERRPART:
Call ShowErrMessage(Err.Number, Err.Description)
End Sub

Private Sub Pr_LoadAdd()
On Error GoTo ERRPART

i = i + 1

Exit Sub
ERRPART:
Call ShowErrMessage(Err.Number, Err.Description)
End Sub


Thanks,
PKR

An add-in is loaded only once and so has the same "properties" (such as the value of a global variable) independent of where it's
accessed from...

Tim


wrote in message ups.com...
Hi,

I am building a excel add-in (VBA) application for risk analysis;
add-in has a commandbar and userform to allow user to select/enter
input data. User is allowed to select multiple workbooks simultaneously
and run data fetch using add-in.
To my suprise I found that excel allows the variable values in the
addin be shared across workbook. How can I avoid this from happening,
is there any work around.

In the code below (Extracted from addin module), I found that variable
"I" shares same values across different workbook


Option Explicit

Dim CmdItem As CommandBarControl

Dim i As Long
Private Sub Pr_LoadDisplay()

On Error GoTo ERRPART

MsgBox (i)
'MsgBox ThisWorkbook.Name
MsgBox ActiveWorkbook.Name

Exit Sub
ERRPART:
Call ShowErrMessage(Err.Number, Err.Description)
End Sub

Private Sub Pr_LoadAdd()
On Error GoTo ERRPART

i = i + 1

Exit Sub
ERRPART:
Call ShowErrMessage(Err.Number, Err.Description)
End Sub


Thanks,
PKR

Hi Tim,

Thanks for the response, but the variables are not public and their
scope is within the module. I am unable to understand why a variable
defined within a module in an Addin persist across workbook.

Can you please help me understand.

Thanks,
Pkr
wrote:
Hi,

I am building a excel add-in (VBA) application for risk analysis;
add-in has a commandbar and userform to allow user to select/enter
input data. User is allowed to select multiple workbooks simultaneously
and run data fetch using add-in.
To my suprise I found that excel allows the variable values in the
addin be shared across workbook. How can I avoid this from happening,
is there any work around.

In the code below (Extracted from addin module), I found that variable
"I" shares same values across different workbook


Option Explicit

Dim CmdItem As CommandBarControl

Dim i As Long
Private Sub Pr_LoadDisplay()

On Error GoTo ERRPART

MsgBox (i)
'MsgBox ThisWorkbook.Name
MsgBox ActiveWorkbook.Name

Exit Sub
ERRPART:
Call ShowErrMessage(Err.Number, Err.Description)
End Sub

Private Sub Pr_LoadAdd()
On Error GoTo ERRPART

i = i + 1

Exit Sub
ERRPART:
Call ShowErrMessage(Err.Number, Err.Description)
End Sub


Thanks,
PKR