Global Static and dynamic variable possible?

[ShieBoon]

Hi all, i need a variable for a date which is static, global, and the value of this variable can be changed via a userform. Is that possible?? :|

Excel 2003
Shie Boon
Thanks

 

[sijpie]

If you declare a variable Public then it has global reach
Do this at the top of a module ( I often have a module only for constants and global variables declaration, then iti s easy to maintain.)

option explicit
 
Public MyGlobalString as String

Now to have it static is more difficult to guarantee. When the macors finish running, Excel could overwrite variables, and so the next time you press a macro button this varaible will likely contain something else.

The easiest way to create a real static variable is to use a (hidden) cell in your spreadsheet to store the value.

 

[sijpie]

I forgot tomake it clear that the Public variable is 'Static' for the duration of all the macors running.

Normaly you would declare a local variable static if you want it to retain the value then next time the sub is called. A Public varible, once assigned a value will keep this value for all subs to use.

sub test()
   dim i as integer
   for i=1 to 3
     msgbox ShowStatic
   next i
end sub
 
function ShowStatic()
   Static MySt as Long
   MySt = MySt + 1
  ShowStatic = MySt
end function

The above shows how the local static keeps its value. But if you run the macro a second time it will start from 0 again.

Public MyPublicV as string
 
sub Test2()
   MyPublicV = "Feed Me!"
   Showstr1
   Showstr2
   Showstr3
end sub
 
sub Showstr1()
   msgbox MyPublicV
end sub
 
sub Showstr2()
   msgbox MyPublicV
   MyPublicV = MyPublicV & " Now please?"
end sub
 
sub Showstr3()
   msgbox MyPublicV
end sub

This shows how the variable is available to all subs to use.

Note that if the variable is only for use in the one module, don't use Public but Private. Public means it is available in all modules.

 

[ShieBoon]

Hi sijpie, first of all, thank you for the effort put in to give me such a detailed reply.

Thank you for the tips (I often have a module only for constants and global variables declaration, then it is easy to maintain.) too, I might consider it because right now i'm keeping my global variables in different modules which has relations with them.

I am now still trying to achieve my goal, which is to allow a dynamic static variable.

Thanks,
Shie Boon

 

[RoryA]

What exactly do you mean by a 'dynamic static' variable?

 

[ShieBoon]

Hi rorya, okay i should explain what i need in detail.

I have a userform which has a textbox to supposedly store a date. I need this date for formulas in one of the worksheets. Users can type in the date they need for the formulas into the textbox.

But i need a static variable to store the user's input, so that the user does not need to retype this value over and over again. But i do not want to use a cell to retain this value.

Right now, i have placed codes into the beforeupdate event of the textbox, to call a procedure, passing in the user input. (This procedure is used to start up the userform) And in that procedure, i store the user input into a static variable. And then, i place the value of the static variable into the textbox to display it.

Thanks,
Shie Boon

 

[RoryA]

In the sense that you seem to be using Static, you either need a Public variable as was already explained, or a named range.
None of the rest makes any sense to me - how can you use the event of a textbox on a form to call a procedure that loads that form?

 

[ShieBoon]

It's like this, i use a procedure (frm_MainBtns_Initialize) to load the userform and set some settings to display the userform like how i want it to be.

So it's like,
Sub frm_MainBtns_Initialize(Optional ByRef Date as Date)
static DateStorage as Date

DateStorage = Date
load frm_MainBtns
' Set textbox to display the static variable's value
txt_Date = DateStorage
frm_MainBtns.Show

End Sub

This is how i'm trying to create a static variable, and store its value from a user input.

Anyway what do you mean by the following?

In the sense that you seem to be using Static, you either need a Public variable as was already explained, or a named range.

Thank you for the reply
Shie Boon

 

[ShieBoon]

And it's not working, the method i'm trying.

Thanks,
Shie Boon

 

[RoryA]

Assuming that code is not in the form, you haven't specified where the textbox is:

Sub frm_MainBtns_Initialize(Optional ByRef Date as Date)
static DateStorage as Date

DateStorage = Date
load frm_MainBtns
' Set textbox to display the static variable's value
frm_MainBtns.txt_Date = DateStorage
frm_MainBtns.Show

End Sub