Prev: Convert date (m/dd/yyyy) in text format into an excel recognised date
Next: colorscales, icon sets, and databars referring to remote cells
From: Goody on 11 Mar 2010 16:19
My macro is triggered on Worksheet_Activate, and stores the contents of
several cells in an array. Later, when a button is clicked, another macro is
a standard module uses the variables in the array in a dialog box. It worked
for a while, but now, for some reason, as soon as the Worksheet_Activate
macro ends, the array values revert to Empty. I've tried declaring the array
in the worksheet code, the module code, and as Public, but nothing seems to
restore its earlier functionality. Where should the declaration be placed?

Goody
From: Dave Peterson on 11 Mar 2010 17:06
If you stop the macro manually (usually while testing) by clicking the Reset
button (or Run|Reset from the menubar), then those public/static variables are
lost/reset to defaults.

If you use "End" to quit the macro (not "End Sub", "End If", "End Function",
....), then you'll reset those variables.

I'm gonna guess that you tried to quickly quit your code by using End and lost
the values your variables held.

Goody wrote:
>
> My macro is triggered on Worksheet_Activate, and stores the contents of
> several cells in an array. Later, when a button is clicked, another macro is
> a standard module uses the variables in the array in a dialog box. It worked
> for a while, but now, for some reason, as soon as the Worksheet_Activate
> macro ends, the array values revert to Empty. I've tried declaring the array
> in the worksheet code, the module code, and as Public, but nothing seems to
> restore its earlier functionality. Where should the declaration be placed?
>
> Goody

--

Dave Peterson
From: Rich Locus on 11 Mar 2010 19:36
Goody:

One process I use frequently to initialize and PRESERVE all the variables I
want to be GLOBAL is to initialize them at Excel worksheet startup time.

From your VBA area, Insert a Module, then Insert a Procedure, and name it
Auto_Open.

Once you define Public variables in that manner, they persists and can be
viewed/modified by forms and worksheets.

Here's an example:

Option Explicit
Option Base 1
Public dteDateSelected As Date
Public intNumberOfTransactionRows As Long
Public intNumberOfTemplateRows As Long
Public strStyleAnalysisFilter As String
Public strColorAnalysisFilter As String
Public strFactoryFilter As String
Public strMailToEmailAddress As String
Public strCCEmail As String
Public strToggleFilter As Boolean

Public Sub Auto_Open()
Dim intFullArrayEntries As Long
Dim intAllFactoryEntries As Long
Dim intBuildAllArray As Long
Dim intNumberOfRowsInTransWorksheet As Long
Dim intNumberOfRowsInTemplateWorksheet As Long
Dim intBuildEmailAddress As Long

dteDateSelected = Date
intNumberOfTransactionRows = 2
strStyleAnalysisFilter = ""
strColorAnalysisFilter = ""
strFactoryFilter = ""
strTemplateWorkbookName = ActiveWorkbook.Name
strMailToEmailAddress = ""
strCCEmail = ""
strToggleFilter = True

.... More Code

--
Rich Locus
Logicwurks, LLC


"Goody" wrote:

> My macro is triggered on Worksheet_Activate, and stores the contents of
> several cells in an array. Later, when a button is clicked, another macro is
> a standard module uses the variables in the array in a dialog box. It worked
> for a while, but now, for some reason, as soon as the Worksheet_Activate
> macro ends, the array values revert to Empty. I've tried declaring the array
> in the worksheet code, the module code, and as Public, but nothing seems to
> restore its earlier functionality. Where should the declaration be placed?
>
> Goody
From: Dennis Tucker on 12 Mar 2010 18:52
If you are using VBA/Excel, use cells on a worksheet instead of MyArray().



"Goody" <Goody(a)discussions.microsoft.com> wrote in message
news:A6D56281-24E3-4F99-B60A-46440C4D5745(a)microsoft.com...
> My macro is triggered on Worksheet_Activate, and stores the contents of
> several cells in an array. Later, when a button is clicked, another macro
> is
> a standard module uses the variables in the array in a dialog box. It
> worked
> for a while, but now, for some reason, as soon as the Worksheet_Activate
> macro ends, the array values revert to Empty. I've tried declaring the
> array
> in the worksheet code, the module code, and as Public, but nothing seems
> to
> restore its earlier functionality. Where should the declaration be placed?
>
> Goody

 | 
Pages: 1
Prev: Convert date (m/dd/yyyy) in text format into an excel recognised date
Next: colorscales, icon sets, and databars referring to remote cells