Re: Initializing variables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23BHeblvXJHA.6064@xxxxxxxxxxxxxxxxxxxxxxx
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.)

--
Rick (MVP - Excel)


"Don" <don87109@xxxxxxxxxxx> wrote in message
news:Ot17VavXJHA.5336@xxxxxxxxxxxxxxxxxxxxxxx
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

"Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23QzDCXlXJHA.1328@xxxxxxxxxxxxxxxxxxxxxxx
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>

--
Rick (MVP - Excel)


"Don" <don87109@xxxxxxxxxxx> wrote in message
news:eboODOlXJHA.1328@xxxxxxxxxxxxxxxxxxxxxxx
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

"Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message
news:ORsY35ZXJHA.5272@xxxxxxxxxxxxxxxxxxxxxxx
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 (MVP - Excel)


"Don" <don87109@xxxxxxxxxxx> wrote in message
news:%23RRIMmWXJHA.2444@xxxxxxxxxxxxxxxxxxxxxxx
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

"Dana DeLouis" <ddelouis@xxxxxxxxxxxxx> wrote in message
news:uhfaVaWXJHA.760@xxxxxxxxxxxxxxxxxxxxxxx
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


Don wrote:
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











.

Relevant Pages

  • Re: Initializing variables
    ... Rick ... Your variant array idea sounds promising. ... Static tempHiWater As Variant ... On second thought maybe it's easier just to have another variable as an initialization switch. ...
    (microsoft.public.excel)
  • Re: Initializing variables
    ... the stock price is passed by this argument to the function. ... Your variant array idea sounds promising. ... Static tempHiWater As Variant ... could always use another variable as an initialization switch too, ...
    (microsoft.public.excel)
  • Re: Initializing variables
    ... Rick ... If you still wanted to try the approach, and didn't mind using a Variant, then you could do it this way... ... On second thought maybe it's easier just to have another variable as an initialization switch. ... 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). ...
    (microsoft.public.excel)
  • Re: Delphi.NET
    ... Since arrays need initialization, .NET will not allow this in a variant type record. ... the following code will not compile because the array type needs to be initialized. ... MyActType: TTestRec; ...
    (comp.lang.pascal.delphi.misc)
  • Re: Initializing variables
    ... If you still wanted to try the approach, and didn't mind using a Variant, then you could do it this way... ... On second thought maybe it's easier just to have another variable as an initialization switch. ... 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). ...
    (microsoft.public.excel)