Trouble understanding the IsMissing Dunction (1 Viewer)

[JDubya]

Hi Folks, I'm working through an old manual that provides an introduction to VBA programming in Access and I'm stumped at their use of the IsMissing funtion. The code they provide is thus:-

Function MakeIceCream (Optional CocoaToBeAdded As Variant) As String

If IsMissing(CocoaToBeAdded) Then
CocoaToBeAdded = True
Else
CocoaToBeAdded = False
End If

rest of code.....
End Function

Depending on the value of the CocoaToBeAdded parameter (True or False) that is passed into the MakeIceCream function, the output will either state that you have made Chocolate or Plain ice cream and when testing this I get the results that the book promised . I understand that the IsMissing function tests for whether the parameter has been supplied and if not it sets CocoaToBeAdded = True, and true to it's word when the code runs without a parameter being passed in, I end up with Chocolate Ice Cream. What I don't understand is why the code behaves the way it does when a parameter value of True is passed in. From how I read the code the IsMissing function tests to see whether a parameter has been supplied, finds that it has, the If IsMissing part is therefore False and so proceeds to the Else part of the statement, where it then sets CocoaToBeAdded = False. I would expect that the end result would be Plain Ice Cream. However, that's not the result that I get when I run the code and I don't understand why. It makes sense if I take it that the If..Then..Else statement is just handling the test of the IsMissing function in that if the parameter is missing then the Ismissing value =True Else the parameter is present therefore Ismissing =False, but from how I understand it the code is saying that if the IsMissing function returns a Value of False(so a value has been passed) in, then set CocoaToBeAdded to False, irrespective of whether you passed in a value of True(or any other value). It's tooooo early! Can somebody explain this please. Cheers, John

 

[Grumm]

Is it possible to show us all the code ?
My guess is that it in the code that call's the 'MakeIceCream' the parameter can be null.
Since CocoaToBeAdded is a variant it can be anything (a string, or number or date).

 

[JDubya]

Hi Grumm, sure although in the original code there are two optional arguments, but here here goes:-
Function MakeIceCream1(Optional CocoaToBeAdded As Variant, Optional VanillaToBeAdded As Variant) As String

If IsMissing(CocoaToBeAdded) Then
CocoaToBeAdded = True
Else: ccoatobeadded = False

End If

If IsMissing(VanillaToBeAdded) Then
VanillaToBeAdded = True
Else: vaniallatobeadded = False

End If

Debug.Print "Beat egg yolks lightly"
Debug.Print "Beat in sugar"
Debug.Print "Heat the cream/milk on the stove"

If CocoaToBeAdded = True Then
Debug.Print "Beat in cocoa powder"
End If

Debug.Print "Heat cream/milk/cocoa mix until steaming"
Debug.Print "Stir into egg/sugar mix"

If VanillaToBeAdded = True Then
Debug.Print "Add vanilla extract(If required)"
End If

Debug.Print "Cool"
Debug.Print "Freeze in the ice cream maker"

If CocoaToBeAdded = True And VanillaToBeAdded = True Then
MakeIceCream1 = "The above ingredients and steps make Chocolate Vanilla Ice Cream"
ElseIf CocoaToBeAdded = True And VanillaToBeAdded = False Then
MakeIceCream1 = "The above ingredients and steps make Chocolate Ice Cream"
ElseIf CocoaToBeAdded = False And VanillaToBeAdded = True Then
MakeIceCream1 = "The above ingredients and steps make Vanilla Ice Cream"
ElseIf CocoaToBeAdded = False And VanillaToBeAdded = False Then
MakeIceCream1 = "The above ingredients and steps make Plain Ice Cream"
Else

MakeIceCream1 = "Summat's gone wrong"

Debug.Print MakeIceCream1

End If

End Function

I guess what I'm trying to ascertain is when the CocoaToBeAdded parameter is being set to False in the Else part of the Ismissing statement (after I passed in a value of True), is VBA just setting the default value for CocoaToBeadded to False, but as a value of True was supplied then this default will be superseded? Cheers, John

 

[CJ_London]

 Depending on the value of the CocoaToBeAdded parameter (True or False)

because CocoaToBeAdded is a variant you can pass anything - could be 'dark', 'milk', '100gm', '50gm' etc

Seems to me you have to coding wrong, but perhaps the function is supposed to work this way.

if there is no value then you debug.print "Beat in cocoa powder"

and if there is a value, you don't

I would have thought it should be the other way round


However your function is doing nothing with the value other than seeing if a value has been passed so you could just use

Function MakeIceCream1(Optional CocoaToBeAdded As Variant=false, Optional VanillaToBeAdded As Variant=false) As String

if there is no value passed it will return false and the code would then be

If CocoaToBeAdded <> False Then Debug.Print "Beat in " & CocoaToBeAdded & " cocoa powder"

which would then read something like

Beat in dark cocoa powder

or perhaps

Beat in 100gm cocoa powder

 

[JDubya]

Hi CJ, I understand what you're saying and get that I can set the default value of an optional argument when declaring it, negating the need to use the IsMissing function in this example.

However, this isn't a question of whether I need to use IsMissing but of what happens when i do, especially as outlined in the example code above. My tenuous understanding of VBA is that throughout the execution of a procedure, a named variable can have different values depending on what has been enacted on that variable by the code. However, i thought that if on line 1 of the code a variable is given a value of 10, it keeps that value until the code tells it otherwise. Then something may occur on line 9 that changes the value to 20 and as and before, the variable retains the new value until told to do something else.

What is puzzling to me is in the code example above, if the variable/parameter/argument (or whatever it should be called) CocoaToBeAdded is given an initial value of True to pass into MakeIceCream1, my understanding is that this means before I reach the first If statement CocoaToBeAdded = True.

Then as a result of CocoaToBeAdded failing the test of the IsMissing function it is passed to the Else part of the If...Then...Else statement which then sets CocoaToBeAdded = False. However, when the code is run, the result I get is the one I would expect to see if CocoaToBeAdded =True.

So the only way this makes any sense to me is when in the Else line of the statement whereby CocoaToBeAdded = False, CocoaToBeAdded is not really being set to false but VBA is saying that the default value of CocoaToBeAdded = False and that that value will be superseded if an actual value was passed in. But then this is a bit cloak and dagger and doesn't seem to adhere to the basic rules.....hence why I am baffled!

 

[JDubya]

BTW CJ, in the book example CocoaToBeAdded was originally Boolean, but then I was instructed to change it to Variant as this is the only variable type that works with the IsMissing function. So the only values that will passed in, if at all, will be True or False.

 

[CJ_London]

if you want to use ismissing then yes the type needs to be variant. My point is you don't need to use it in the context you are trying to use it if you modify the parameter definition as I showed you. Is missing is just a way of determining the state of a variable - it either has a value or it might be missing, null or nothing

i thought that if on line 1 of the code a variable is given a value of 10, it keeps that value until the code tells it otherwise. Then something may occur on line 9 that changes the value to 20 and as and before, the variable retains the new value until told to do something else.

correct, providing the variable is declared within the same code block (i.e. a procedure or sub). Once the code finishes, the variable is 'destroyed' and cannot be used in another sub - unless the variable is declared at a higher level - either at the top of the form module or as a public variable in a general module.

when in the Else line of the statement whereby CocoaToBeAdded = False, CocoaToBeAdded is not really being set to false but VBA is saying that the default value of CocoaToBeAdded = False

No, you have that wrong. If you set CocoaToBeAdded = False, it is false, end of. There is no default value. But you can change it to true if you want.

 

[Grumm]

So it is save to say that using the 'IsMissing' in this case was just for education purpose only.

In a real situation you will never do something like this. You can check for optional variables but still I think this option is beter :

Function MakeIceCream1(Optional CocoaToBeAdded As Variant = False, Optional VanillaToBeAdded As Variant = False) As String

The code above has default values for both variables. So if CocoaToBeAdded is missing, it will be 'False'.

 

[moke123]

Function MakeIceCream1(Optional CocoaToBeAdded As Variant, Optional VanillaToBeAdded As Variant) As String

its always been my understanding that ismissing is related to the call of the function.

for instance
MakeIceCream1(10) - the 2nd argument ismissing
MakeIceCream1(,10) - the 1st argument ismissing

 

[Frothingslosh]

Function MakeIceCream1(Optional CocoaToBeAdded As Variant, Optional VanillaToBeAdded As Variant) As String

its always been my understanding that ismissing is related to the call of the function.

for instance
MakeIceCream1(10) - the 2nd argument ismissing
MakeIceCream1(,10) - the 1st argument ismissing

It is, but it effectively duplicates the workings of IsNull. I never use it, as I always provide a default when I include optional parameters (it's been commented on before that I take a very belt-and-suspenders approach to things), but it's possible it will return false if a null is expressly passed; otherwise it's the same return as IsNull.

 

[JDubya]

Hi CJ, I take your point on the real world uses of the IsMissing function and that the example given in the book is probably not the best. And it's good to know that my very basic understanding of how a variable is supposed to behave during the execution of a procedure is sound. (I purposely didn't get into scope and lifespan of a variable because I haven't got to that point of my learning as of yet, apart from a cursory glance).

Turns out the problem was twofold. To begin with I started off with a function called MakeIcecream, which had a lot less of the code. The book instructed me to add the rest of the code, but instead of doing that, I copied the existing code to a new function MakeIcream1, and then added the new code to the new function. (This way I'd have a before an after). The mistake I made, which I accept was pretty idiotic, was that when I went to test the new code in MakeIceCream1, I was actually calling MakeIceCream instead, hence why the code produced the results that the book predicted.

However, after realising my mistake and then running MakeIceCream1, the results back were not as the book described but were as I theorised. It turns out that those Else statements in the IsMissing part of the code provided by the manual should not have been there.

So the code supplied by the manual was wrong, but I would have picked up on this and not caused myself so much confusion if I'd of called the correct function from the immediate window. Really sorry for wasting your time CJ, but as always thanks very much for your help. Cheers, John

 

[JDubya]

Hi FrothingSlosh, Make123 and Grumm as per my reply to CJ turns out the error was in the original example supplied in the manual, but because of my own error in calling the wrong procedure I didn't pick up on the manual's code error until just now. Sorry for wasting your time guys and thanks for the help. Cheers, John

 

[gemma-the-husky]

ismissing is useful in some cases.

an alternative is to set a default
optional cocoatobeadded as Boolean = false

now ismissing will always be false, but if the parameter IS missing, the value of false gets assigned to the parameter.


it depends exactly what you are trying to do.