It looks to me as though you are using string  variables to hold numbers.
Remember that a number held in a string variable  is checked by alpha characters,
from the left. For instance, in string variables, "2" will be greater than
"1000000".

Use numeric variables for numbers (Integer is fine if the numbers are integral
and small enough). Just give your initial value as something much larger than
the biggest number you're likely to encounter.

But why not use the Min function  and save all the hassle? If your version of
Excel doesn't have one in VBA (e.g. Office 95 or 97) then you can use the one
from Excel itself.

Thank you for your reply and explanation of how String checks
numbers. Being self taught from reading books and help  files I have
never come across that before. I knew the String data type was for
text but had never seen how it handles numbers explaned. Can you
point me to a link that will explain that in more detail?

I took your suggesttion and tried two different data types ( Integer
and Single)and i can still not get it to work.

Dim PrevLowVal As Integer

Function CheckLowVal(Val As Integer) As Integer
If val  <> "" Then
If PrevLowVal = "" Then
PrevLowVal = 1000
MsgBox PrevLowVal & " PrevLowVal 1"
End If
MsgBox PrevLowVal & " PrevLowVal 2"
MsgBox Val & " Val A"

If Val < PrevLowVal Then
PrevLowVal = Val
MsgBox PrevLowVal & " PrevLowVal 3"
End If
MsgBox Val & " Val B"
MsgBox PrevLowVal & " PrevLowVal 4"

End If
End Function




Dim PrevLowVal As Single

Function CheckLowVal(Val As Single) As Integer
If Val <> "" Then
If PrevLowVal = "" Then
PrevLowVal = 1000
MsgBox PrevLowVal & " PrevLowVal 1"
End If
MsgBox PrevLowVal & " PrevLowVal 2"
MsgBox Val & " Val A"

If Val < PrevLowVal Then
PrevLowVal = Val
MsgBox PrevLowVal & " PrevLowVal 3"
End If
MsgBox Val & " Val B"
MsgBox PrevLowVal & " PrevLowVal 4"

End If
End Function


Also regarding your suggestion to use the Min Function. i would be
interested in trying that but am not sure how to do it. i checked
the VBA built in functions and could not locate a Min function. So
either I am not looking in the right place or you are referring to
using the Excel worksheet function  with the WorkSheetFunction
Object. I would use that but I don't know how to get it to do what i
want.

The variable  Val in my CheckLowVal Function will be changing  very
rapidly and I need to determine which value was the lowest number. I
am not quite sure how to create a set  in this situation and also the
help on the excel Min function say that it handles values between 1-
30. i anticipate having more than 30 value changes. If I am not
looking at this correctly any help would be appreciated.

I did not really follow this thread, but I see that David put you in the right
direction regarding the differences between string  and integer data types.
It is not enough for you to declare the variable  as integer or single.
In your code I read that you wrote twice
Val <> "" or val  = "".
To the best of my knowledge "" is used only for string.
(I checked and I see that when you use "" with integer data types you get a
"type mismatch" error).

If you want to check  for an empty variable use the
val = empty (this is good for integer as well as string types I believe).
Where is the PrevLowVal variable initialized???

With each response i learn more. I
have only read a couple of books and started trying to build things
so i am sure there are many holes in my knowledge. Did not know
about the difference between "" and empty in relation to different
data types. Did not know about the initialize variables either. But
after reading your post I looked around and found some info on it
that said if I declare the variable  as a data type the variable will
be initialized as 0. So I asume they were initialized as 0. Thanks
for your patience of someone trying to learn.

Just wanted to make sure i was correct on a
couple of things. If I set  an integer or single data type variable  =
empty will the value in the variable be set to zero? Also am I
correct in that if I declare a integer or single data type variable
it is initialized and the value is set to zero?

If you declare a variable  -let's say an integer- The variable value will be
automatically set  to 0 until you change that in your code.
if you declare a variable as a string. its value will be ""
As you may see, you will check  the value of the integer with the following
dim i as integer
if i = 0 then .....
and the value of the string
dim str as string
if str = "" then ....

but you may as well use
if i = empty then...
if str = empty then ...

what I meant when I asked where do you initialize your PreLowVal variable is
that I did not see you giving it a value. So as per the above its value will be
0 (and of course not "").
As I already told you I did not follow this thread and I do not really know
what you are trying to do.

Thank you for your help  and your suggetions. I appreciate it.

Well, there are a couple things happening to you. First, you're using
mathematical operators on Strings which is likely to force VB to push the
variables to a different type for evaluation *if* you don't get a type
mismatch error. This is commonly referred to Evil Type Coercion. Kinda ugly
and full of unpredictable results. Second, the expect result of a function
is a result but you didn't get around to assigning a result to your
function.

Here's a linear subroutine which does what you asked for:
Sub Checkval()

Dim FinalVal As Long
Dim FirstVal As Long
Dim SecondVal As Long
Dim ThirdVal As Long
Dim FourthVal As Long

FirstVal = 20000
SecondVal = 9
ThirdVal = -6
FourthVal = 0

'Set the initial value of FinalVal
'to the same as FirstVal
FinalVal = FirstVal

If SecondVal < FinalVal Then
FinalVal = SecondVal
MsgBox _
"SecondVal was less than FinalVal: " & _
FinalVal
End If

If ThirdVal < FinalVal Then
FinalVal = ThirdVal
MsgBox _
"ThirdVal was less than FinalVal: " & _
FinalVal
End If

'This one will be skipped because
'zero is greater than -6
If FourthVal < FinalVal Then
FinalVal = FourthVal
MsgBox _
"FourthVal was less than FinalVal: " & _
FinalVal
End If

MsgBox "FinalVal: " & FinalVal
End Sub

Were you to turn this into a routine to do constant comparisons, you'd need
something to treat like a global variable  and then pass your new values to
the routine and update the global variable only if the condition of 'less
than' is true.
'============================================
Public Finalval as Long
'============================================
Sub CompareVals()
Dim lng1 as Long
Dim lng2 as Long
Dim lng3 as long

FinalVal=40000000
Lng1=20
Lng2=-6
Lng3=0

FinalVal=CheckVal(Lng1)
FinalVal=CheckVal(Lng2)
FinalVal=CheckVal(Lng3)

MsgBox FinalVal
End Sub
'============================================
Function CheckVal(lngVal as Long) As Long

If LngVal < FinalVal Then
CheckVal=LngVal
Else
Checkval=FinalVal
End If
'Debug.Print shows output in the Immediate
'Window in the VBE
Debug.Print CheckVal
End Function

I recognize that I should be using data
type variables instead of strings and will make that switch. Also
thank you for you example.

I don't know of any specific explanation of this. It's just the way strings
work. They compare the characters in the string, starting from the left,
putting everything into "alphabetical" order. The test is based on the ASCII
sequence of characters and the digits are in that sequence too. If you look for
ASCII collating sequence you should find the order of characters in the ASCII
set.

You have made your variables into integers but you are still using a string  -
specifically the "" empty string. An integer cannot hold an empty string or any
other type of string. I don't know whether this will give errors or whether the
empty strings will be converted to zeroes, but either way they aren't doing what
you want.

You need to put an impossibly high number into PrevLowVal before you first call
CheckLowVal.

An alternative would be to define an extra variable  as a Boolean. Call it
Initialised or similar. VBA will initialise it to false.

Then you can ask

If Not Initialised Then

and set  up the big number in PrevLowVal, and also set Initialised to True.

I notice you have a large  number of message box calls. Are you stepping the
code through with the debugger and using break points? Get to know the debugger
- it'll save you lots of time. Put a breakpoint on the first statement inside
your function.

I also note for the first time  that you've defined this as a function, but it
doesn't ever return a value. What is it supposed to return? How do you call
it? How do you eventually use the lowest value?

Whenever I read your replies I realize how much i have to learn.
Thank you for taking the time. My first problem  was with different
type of variables and how empty was/is handles. Have a lot to learn
there. As for all the Message box calls. You are right, i am using
them to check  results as the function  steps through. Good suggestion
to get to know the debugger. What is the easist way to get started?

One of the main reasons I am using a function is to get it to
automatically update whenever there is a value change to the val
variable. val  will be linked to an RTD cell. i am then going to add
the PrevLowVal value (which will hold the lowest value over some
period of time) to a collection. The function will return the number
of items in the collection. i am looking to determine the lowest
value from a realtime data stream over set  periods of time  and add
thoes values as items to a collection. It all seems to work  except I
was not able to set the first PrevLowVal to a high enough value as a
string. I guess I could use 999999 or convert to a data variable  and
use empty instead of "". Probably convert to data variables and give
that a try.

You may want to check  out...

pubs.logicalexpressions.com/.../LPMArticle.asp

That is a great link. I have only read the top two,
Splitting the Code Window, and Activating Intellisense, but if the
rest is like those...what a treasure.

The debugger is very simple, and is the same environment you're using to type
your code now.

Simply put a breakpoint on the first statement inside your function  (it won't
put breakpoints on statements like DIM statements, so it might not be exactly
the first statement). Then whenever your function runs next time, it will break
at that point and display the line of code. You then have options to step
through the code and you can view the contents of local variables. The
short-cut key to put a breakpoint on a line is F9.