Initializing variables

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I'm writing a Function and need to initialize a variable to say 10,000. I
can't find a way to do this on the Dim statement. Of course I can initialize
it programmatically in the code section, but that seems overly complicated.
Is there a simple way to initialize variables?

Bear in mind I only want to initialize once and not each time the Function
is invoked.

Don
 
All variables are initialized to the minimalist values, such as 0,
Nothing, Empty, or null string depending on the variable type. You
must use code to set a variable to some other value.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
can't find a way to do this on the Dim statement.

Just to mention...if the variable won't change, perhaps use a constant.
The idea being that you dim it and set its value at the same time.

Const Pi As Double = 3.14159

= = =
Dana DeLouis
 
Chip and Dana,

Thanks for the feedback. I'm amazed that you can't set a value on the dim
statement. Oh well, the variable does change so I'll have to add code to
somehow initialize it the first time the function is executed.

I guess I'll have to use another variable to serve as an initialization
switch. I'll set that variable to "one" after the main variable is
initialized and then use that "one" in an If statement to skip the
initialization code whenever the function is subsequently called.

Don
 
You probably don't need another variable to serve as an initialization
switch... you can probably just use the contents of the variable itself. For
example,

Function Foo() As Variant
Static MyVariable As Long
If MyVariable = 0 Then MyVariable = 10000
....
....
End Function

As long as MyVariable is never reset to 0, it will retain the last value
assigned to it whenever the function is called (the Static rather than Dim
statement sees to that).
 
Rick,

Thanks for the suggestion, but I don't think it works in my case. That's
because the normal range of values potentially stored in MyVariable is zero.

However, maybe I could add a constant to any MyVariable values to avoid
zero. I just have to remember to subtract that constant anytime I want the
true value of MyVariable.

On second thought maybe it's easier just to have another variable as an
initialization switch.

Don
 
Well, I didn't promise that my suggestion could always be used... I did say
"probably" and offered the conditions under which it would work. If you
still wanted to try the approach, and didn't mind using a Variant, then you
could do it this way...

Function Foo() As Variant
Static MyVariable As Variant
If IsEmpty(MyVariable) Then MyVariable = 10000
....
....
End Function

This works because Variants start off defaulted to Empty and will never be
Empty again unless specifically set to it. Of course, you could always use
another variable as an initialization switch too, you know.<g>
 
One more complication, if I may.

I need to use this function many times. How can I assign different variable
names to each iteration?

Frank
 
I'm not sure what you are wanting to do here... can you provide more
details? (My initial thought is maybe a Variant array where you pass in the
index number of the array element, but I would need to know more about what
you want to do before locking down a recommendation.)
 
I have vendor supplied Excel spreadsheet software that frequently updates
stock prices, maybe once per second.

I am trying to capture the Hi and low prices and the time of day for each
since the spreadsheet was opened. I have two functions defined called
HiWater and LoWater. Today the only parameter passed to these functions is
the cell containing the stock price.

The problem is that there are several stocks that I want to monitor at the
same time. Here is the code I am using for the hi water function. Your
variant array idea sounds promising.

Dim tempHiWatertimeX As String


Function HiWater(Target As Range)
Static tempHiWater As Variant
If IsEmpty(tempHiWater) Then tempHiWater = -10000
If Target > tempHiWater Then
tempHiWater = Target
tempHiWatertime = Time
End If
HiWater = "hi= " & tempHiWater & " " & tempHiWatertime

End Function
 
Two points about the code you posted... one, you don't appear to be using
the tempHiWatertimeX variable that you are declaring (globally) outside of
the function, so unless you are using it in another procedure, you can
remove its declaration; and two, you are specifying a Target argument to
your function, but you don't seem to be using it either, so you can remove
it from the function's declaration as well.

If your stocks (the ones you want to monitor) are fixed, then we can hard
code them in the function; otherwise we can read them from a range on a
worksheet... which way did you want to do this?
 
Rick,

Thanks for the help.

Sorry about the "X" on the global variable I should have removed it (the X).
All the variables originally had an X suffix which I removed for clarity,
but I forgot about the global variable.

I am not sure why you think the target argument is not used. It is included
in two of the statements in the function. The address of the cell containing
the stock price is passed by this argument to the function.

The stocks are not fixed and will change from time to time. However, there
is no need for the function to know the stock name. I just want the function
to capture the Hi water mark applicable to the price variations. The
function will be called from a cell near to the stock name and it will
obvious that it pertains to that stock. The function will simply return the
value and time of the high water mark. Don't worry about the time, hopefully
I'll be able to learn from the Hi Water code and duplicate that for the time
capture.

Sorry if the following is repetitious,but I'm trying to be clear: I intend
to call this function for each stock and pass the applicable price cell
address in the Target argument. Because this Function will be used many
times in the spreadsheet (for each stock) and obviously the variables within
the function must be unique for each stock.

Perhaps I could create a global variable for each stock outside the function
and also pass that to the function along with the target? That's probably
not very elegant, but it would be simple and simple is good for me.

After I get the Hi Water mark function working I'll duplicate it to capture
the low water mark.

As you probably have already detected I'm not very Excel savvy especially
with the VBA aspects.

Don
 
Back
Top