Addin data persistence problem

[[email protected]]

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

[Tim Williams]

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


<[email protected]> wrote in message news:[email protected]...
> 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
>

[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
[email protected] 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