MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Addin data persistence problem

  Asked By: Hope    Date: Nov 12    Category: MS Office    Views: 1223

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()


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

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

Private Sub Pr_LoadAdd()

i = i + 1

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



1 Answer Found

Answer #1    Answered By: Ryan Evans     Answered On: Nov 12

I suppose I'm less surprised at this. The variable  belongs to the add-in,
not the spreadsheet, and there's only one add-in. Any persistent data  there
could be expected to be about the state of the add-in  itself.

There would be lots of ways to keep information specific to each workbook,
so you need to think about what you need to hold before you look at a
solution. I'm assuming that your I is simply a test, so step back and
define the problem  before you define the solution.

Specifically, what persistent data should an add-in hold for a spreadsheet
anyway? The normal answer should be: "none". So, think about everything
you're looking at holding and where you should hold it.

Seeing it's Excel, most persistent data should be held in the sheet itself.
This will sometimes be as real information in cells, sometimes as positional
information (e.g. selected cell, active worksheet, etc), and sometimes as
derived information (e.g. first free cell in a column).

If your sheet interacts directly with your add-in (for instance through a
toolbar), then the sheet should be supplying everything the add-in needs.

If your sheet interacts with your add-in via VBA code  in the workbook  (for
instance as service functions) then any persistent data that the sheet
doesn't have should be in the local VBA, not the add-in.

That's the theory. Practice might be different. Have a look at the data
you need to hold. Eliminate anything that can be held as described above,
then by all means come back with questions about specific types of data that
the add-in needs to hold, and we'll throw some suggestions your way.

Didn't find what you were looking for? Find more on Addin data persistence problem Or get search suggestion and latest updates.