-------------------------------------------- On Thu, 2/9/17, keisha.fry via MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> wrote:
Subject: Re: $$Excel-Macros$$ keep active in memory variables, array variables To: excel-macros@googlegroups.com Date: Thursday, February 9, 2017, 7:02 PM -------------------------------------------- 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.-9 oct - Vizita tarului Nicolae al II in Franta -- 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.
