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 aprogram / 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.
