--------------------------------------------
On Thu, 2/9/17, Paul Schreiner <schreiner_p...@att.net> wrote:
Subject: Re: $$Excel-Macros$$ keep active in memory variables, array variables
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Date: Thursday, February 9, 2017, 6:56 PM
The
answer is:Yes.
The
long version of the answer involves describing the
"scope" of variables.
First
of all,in
each VBA module, I recommend using
Option
Explicit
as
the first line of the module.This
forces the compiler to check that all variables are
EXPLICITLY declared.without
it, the first time you use a variable, the compiler will
define it as type "variant".although
I've had it auto-define a variable as an
integer.
As
for declaring variables: placement is important!You declare a variable with
the Dimstatement.
Dim nrow as
integer
declares
the variable "nrow" as an integer.
If
you put this WITHIN a subroutine, like
Sub
Count_Rows()
Dim nRow as Integer...End
Sub
then
the variable is ONLY recognized for use WITHIN the
subroutine.That
means that if you have a second sub and you want to use the
value that you got from the Count_Rows sub, you
cannot.
If
you declare the variable OUTSIDE of a subroutine, it is
available for all subroutines in the module.
---------Option
ExplicitDim
nRowSub
Count_Rows()
For nRow = 1 to 100
... Next
nRowEnd
Sub
sub
Work_Backwards()
Dim inx
Count_Rows
for inx = nrow to 1 step -1 ... next
inxend
sub
---------------
But,
if you have multiple modules, or write your code within the
sheet module, then those variables are only available to
that module, not others.
variables
defined with the Dim function are not
"persistent".That
is: they are cleared when the macro stops running.
to
make a variable "persistent", you need to use the
"Global" (or "Public") declaration
keyword.
Option
ExplicitPublic
nRowSub
Count_Rows...End
Sub
This MUST be done outside of a macro, and in a
"Standard" module (not a Sheet module)
It is
perfectly legal to declare a variable as a
"Public" variable and also within a subroutine as
a "local" variable.
When the macros run, they
will treat these as two separate variables.
As for array
variables:There are many ways of
declaring them, depending on how you plan to use
them.
If, for instance, you don't know what size
they will be until run-time,you can
declare the variable like:
Dim sArray
then, once
you decide on a size, you can re-dimension it:
ReDim
sArray(nRow) Preserve
Use
the VBA help to read up on declaring arrays.
hope this
helps.
Paul-----------------------------------------
“Do
all the good you can,
By all the means you
can,
In all the ways you can,
In all the places you can,
At
all the times you can,
To all the people you
can,
As long as ever you can.” - John
Wesley
-----------------------------------------
On Thursday, February
9, 2017 10:01 AM, "wltrp...@gmail.com"
<wltrp...@gmail.com> wrote:
Is it possible in an Excel
workbook as long as it is open,
to keep active in memory variables, array variables? And
those, when running a
program / script vba or macro is finished.
How to declare
variables, array variables?
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official
Facebook page of this forum @ https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
like Please Help, Urgent, Need Help, Formula Problem, Code
Problem, and Need Advice will not get quick attention or may
not be answered.
2) Don't post a question in the thread of another
member.
3) Don't post questions regarding breaking or bypassing
any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not
allowed.
NOTE : Don't ever post confidential data in a workbook.
Forum owners and members are not responsible for any
loss.
---
You received this message because you are subscribed to the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official
Facebook page of this forum @ https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
like Please Help, Urgent, Need Help, Formula Problem, Code
Problem, and Need Advice will not get quick attention or may
not be answered.
2) Don't post a question in the thread of another
member.
3) Don't post questions regarding breaking or bypassing
any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not
allowed.
NOTE : Don't ever post confidential data in a workbook.
Forum owners and members are not responsible for any
loss.
---
You received this message because you are subscribed to the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
conscriptie- recensamant efectuat de autoritatile civile militare sau
ecleziastice pe intreg teritoriul
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
