I don't store such variables in cells, since it is not part of the data
table. I store these variables in the system registry instead.
Here is an example where I use the registry to increase the counter by
one each time a macro is run. If the registry value is not a numeric
value (or empty), I reset it to 1. This example demonstrates how to read
and write to the registry.
' increase Wolf Tools counter
' ---------------------------
UVar = GetSetting(APPNAME:="WolfTools", Section:="Settings",
Key:="Usage")
If IsNumeric(UVar) Then
SaveSetting APPNAME:="WolfTools", Section:="Settings",
Key:="Usage", setting:=UVar + 1
Else
SaveSetting APPNAME:="WolfTools", Section:="Settings",
Key:="Usage", setting:=1
End If
You can use regedit.exe to see that VBA always writes to the following
registry path:
HKEY_CURRENT_USER\Software\VB and VBA Program Settings
So, in my case you will see my variables in the following registry path:
HKEY_CURRENT_USER\Software\VB and VBA Program
Settings\WolfTools\Settings
Hope this is useful,
sunnywulf
--- In ExcelVBA@yahoogroups.com, "David Smart" wrote:
>
> > can this be done with a static variable - does it keep
> > its value after the sub is run and finished?
>
> Sort of. :-) The static variable will keep its value until Excel
decides
> to reinitialise the code. This will occur when Excel wants to, not
when you
> want it to, and will certainly happen each time you open the workbook.
>
> The way to persist information is to put it in cells, as you're doing.
>
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "Torstein Johnsen" sejohnse@...
> To: ExcelVBA@yahoogroups.com
> Sent: Saturday, June 01, 2013 8:24 PM
> Subject: [ExcelVBA] Prevent macro from being run twice
>
>
>
> I've made a sort of psychological test in excel.
>
> I don't want it to be run more than once.
>
> I have made a range called runbefore, and if the value is greater than
zero
> the macro ends with a msgbox.
>
> At the start of the sub
>
> sub test()
>
> if range("runbefore").value > 0 then
> msgbox("run before")
> end
> endif
> .........
>
> range("runbefore").value =1
>
> end sub
>
> can this be done with a static variable - does it keep its value after
the
> sub is run and finished?
>
> or
>
> in another way?
>
> regards Torstein
>
>
>
>
>
> Can this be done with a static variable - does the staic variable keep
its
> value after the macro has run?
>
> or in another way?
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then
search the
> ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1432 / Virus Database: 3184/5873 - Release Date:
05/31/13
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar