PDA

View Full Version : Calling Function with 2 args results in "Expected: =" compile error



rubinho
09-07-2010, 08:08 AM
Background; I'm a C++/C# Windows programmer who has delved into an Excel VBA project. I've played with it a bit before but I find the environment a little unfriendly and the language a bit scary! I used to write BBC Basic 20+ years ago and MS QuickBASIC about 15 years ago!

I have a function declared:


Function FindX(ByVal First as Integer, ByVal Second as Integer) As Range

Set FindX = Cells(1, 1)

End Function
If I reference:
FindX(1, 2) from a Proc I get a compile error "Expected: =". I've done some digging and I have tried the following:


FindX 1, 2 ' get rid of the parentheses
Call FindX(1, 2) ' use the Call keyword
Neither of these do what I want. Ultimately, the function will do something useful and I would like to assign and do something with the result.

Strangely I have a function with just one parameter which is declared and referenced in exactly the same way which works just fine.

I'm very confused!

Bob Phillips
09-07-2010, 08:12 AM
It is returning a range object, so you need to assign it to a range object



Set rng = FindX(1, 2)

rubinho
09-07-2010, 08:19 AM
It is returning a range object, so you need to assign it to a range object



Set rng = FindX(1, 2)

I was just about to add, if I do assign it to a range object e.g.

Dim thing As Range

Set thing = FindX(1, 2)
I get an error saying "Object variable or With block variable not set".

My other identical-except-for-having-just-one-argument Function I can reference simply FindY(1) without any assignment.

Bob Phillips
09-07-2010, 08:26 AM
I was just about to add, if I do assign it to a range object e.g.


Dim thing As Range

Set thing = FindX(1, 2)
I get an error saying "Object variable or With block variable not set".

It works fine for me, which suggests that there is more that we are not (yet) aware of.


My other identical-except-for-having-just-one-argument Function I can reference simply FindY(1) without any assignment.

You can, and you can call the two argument function as well, but there is no point in assigning it a return type of range if you aren't capturing it as a range, and no point in setting a value in the function as you are just throwing it away with a call.

rubinho
09-07-2010, 08:36 AM
It works fine for me, which suggests that there is more that we are not (yet) aware of.
Yes, the same thought was dawning on me too. I'm thinking typo but I've been staring at it trying to figure it out to no avail. Is there a length at which a function name no longer becomes unique, i.e. does it only check the first 13 letters or less? Do arguments need to have unique names between different functions?

If I cut and paste the declaration of this function before the other one which does work the error does not change.


You can, and you can call the two argument function as well, but there is no point in assigning it a return type of range if you aren't capturing it as a range, and no point in setting a value in the function as you are just throwing it away with a call.
Sure but I'm taking baby steps here and I want to call without assigning the return value just to test the logic as I go. Production code will use the returned value.

rubinho
09-07-2010, 08:49 AM
Ok, I've tried adding a second parameter to the function which before only had one. When I do this, it stops working and gives me the same error. if I rename the original function and its two parameters it makes no difference.

Bob Phillips
09-07-2010, 08:49 AM
None of those restrictions apply. Why don't you post the workbook so that we can look at it.

rubinho
09-07-2010, 08:57 AM
Ok, it's attached. I'm running the code by selecting a cell and calling the sub with CTRL+L. It's barely a skeleton right now...

Bob Phillips
09-07-2010, 09:12 AM
If I put Cal in front of FindConnectorPin, it works fine (although as I said it is pointless as you do nothing with the function results).

rubinho
09-07-2010, 09:42 AM
If I put Cal in front of FindConnectorPin, it works fine (although as I said it is pointless as you do nothing with the function results).

Yes that's true, but if you use Call I can't then get the return value later on when I will need it making the whole thing useless. Is it just not possible to have a function which returns an object which has more than one argument? Seems a pretty steep limitation if it is.

Bob Phillips
09-07-2010, 09:53 AM
You need to set it to a variable if you want to use it later, as I told you at the start.

It's a function returns a result, <quote>A function is a block of code that performs a calculation and returns a value. </quote>

rubinho
09-08-2010, 01:08 AM
You need to set it to a variable if you want to use it later, as I told you at the start.

It's a function returns a result, <quote>A function is a block of code that performs a calculation and returns a value. </quote>

Sure I get that but if you use Call the return gets discarded so you can't assign it to a variable.


If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded.

I need my function to work without using Call so I can get the return value.

Bob Phillips
09-08-2010, 01:35 AM
So I repeat



Set rng = FindConnectorPin(1, 2)