Inconsistencies calling a function from within a sub or from the immediate window.

[JDubya]

I have encountered an apparent inconsistency in the way VBA handles calls to functions from within a subroutine, as well as calls to a function from the Immediate window. In both cases how VBA responded seemed to be dependent on whether there was one or whether there was more than one argument to be passed in to the function and I'm hoping somebody can explain to me the whys and wherefores.

I created the following 3 simple functions FoxTest1( has a single compulsory argument), FoxTest2 (has 2 compulsory arguments) & FoxTest3 (has 2 optional arguments) to highlight this issue and called each of these functions in turn from the CallTest() subroutine.


Option Compare Database
Option Explicit

Function FoxTest1(blnFox As Boolean) As String

If blnFox = True Then
FoxTest1 = "The quick brown fox did indeed jump over the lazy dog"
Else
FoxTest1 = "I think the quick brown fox is still in the pub"

End If

Debug.Print FoxTest1

End Function
Function FoxTest2(blnFox As Boolean, blnDog As Boolean) As String

If blnFox = True And blnDog = True Then
FoxTest2 = "The quick brown fox did indeed jump over the lazy dog"
ElseIf blnFox = False And blnDog = True Then
FoxTest2 = "I think the quick brown fox is still in the pub"
ElseIf blnFox = True And blnDog = False Then
FoxTest2 = "I think fido is still in the pub"
Else
FoxTest2 = "I think the quick brown fox and fido are still in the pub"

End If

Debug.Print FoxTest2

End Function
Function FoxTest3(Optional blnFox As Boolean = True, Optional blnDog As Boolean = True) As String

If blnFox = True And blnDog = True Then
FoxTest3 = "The quick brown fox did indeed jump over the lazy dog"
ElseIf blnFox = False And blnDog = True Then
FoxTest3 = "I think the quick brown fox is still in the pub"
ElseIf blnFox = True And blnDog = False Then
FoxTest3 = "I think fido is still in the pub"
Else
FoxTest3 = "I think the quick brown fox and fido are still in the pub"

End If

Debug.Print FoxTest3

End Function
Sub CallTest()

FoxTest1
'FoxTest2(True,True) Get "Expected =" error
'FoxTest3(,False) Get "Expected =" error

End Sub

FoxTest1 only has one compulsory argument and when I pass in a value of True or False and then run the CallTest() subroutine or call it from the Immediate Window, I get the expected result - a print out of whether the fox did or did not jump over dog.
However, when I change the call within subroutine CallTest() from FoxTest1 to FoxTest2 and pass in a value for both compulsory arguments, I get an "Expected = " error message when I try to move off the line.
The same is true within CallTest() when I change the call to FoxTest3 and pass in two optional arguments. However, if I only enter a value for the first argument and leave the second optional argument empty eg:-(True)so that it passes in it's default value, then there are no errors. But if I leave the first argument empty and only pass in a value for the second optional argument eg:- (,True), I get the error again. (It seems to me as if the comma is the issue??)


The second issue is what happens when I call each of the 3 functions direct from the Immediate Window. I've been told that if you want to call a function from the immediate window you have to prefix the function name with a "?". And true enough, whenever I call these functions from the immediate window with a "?", the function value is printed out to the immediate window....only it's printed TWICE??
However, when I tested to see what would happen if I didn't prefix the function name with a "?", the following happened:- FoxTest1 (the function with only one variable) printed the value to the Immediate Window and only ONCE this time. The same happened when I printed FoxTest3 and only passed in the first optional argument. All other times for FoxTest3 (whenever I passed in both arguments or just the second) and for all possible permutations for FoxTest2 I got the expected error when trying to call a function from the Immediate Window without prefixing with a "?".

Ok folks, I know this isn't the sexiest of conundrums to have ever come your way but for somebody who is starting their VBA voyage of discovery, it is hugely important that I am confident with the fundamentals otherwise I'm dooooomed!

So ,1. Why is VBA only able to handle a call to a function from within a subroutine so long as only one, or the first optional argument, is passed in? 2. Why when I correctly call a Function from the immediate window with the "?" prefix, is the function value being printed twice? 3. Why when I incorrectly call a function from the immediate window without prefixing the call with a "?", is it printing out the function value just once (and not twice as previously mentioned) but will only allow the call as long as there is only one argument to pass in or only the first optional argument is supplied. Cheers John.

 

[stopher]

1. Why is VBA only able to handle a call to a function from within a subroutine so long as only one, or the first optional argument, is passed in?

Remove the brackets and the expressions will run fine e.g.

FoxTest2 True, True

You only need brackets if you are using the CALL statement.

2. Why when I correctly call a Function from the immediate window with the "?" prefix, is the function value being printed twice? 3. Why when I incorrectly call a function from the immediate window without prefixing the call with a "?", is it printing out the function value just once (and not twice as previously mentioned) but will only allow the call as long as there is only one argument to pass in or only the first optional argument is supplied. Cheers John.

The ? in the immediate window is asking a question i.e. tell me what the result of the expression is. So when you run ?FoxTest1, the function will print the value of FoxTest1 (because it is embedded in the function). The function then returns the value and because you used ?, the result of the function is printed again in the window.

If you don't use ? then you are simple running the function and doing nothing with what is returned from the function so the value is printed only once (from inside the function)

hth

 

[static]

FoxTest2 True, True

You only need brackets if you are using the CALL statement.

or storing the return value

result = FoxTest2(True, True)

A function without brackets acts like a sub.

 

[gemma-the-husky]

in passing I tend to have the optional value as the negative not the positive.

eg in your case

option flag as Boolean = FALSE

much safer in my opinion, as the default value for a Boolean field in a table is false.

 

[JDubya]

Thanks hth(stopher), Static and Dave (not Gemma) for your replies, it all makes sense to me now. I guess the confusion comes about from the Auto Quick Info prompts... I'd already learned (the hard way) that the square brackets meant the argument was optional, but I thought that if normal brackets were being proposed, then I was supposed to use them. Out of interest, are there any occasions when Auto Quick Info suggests brackets and I am actually supposed to use them?