invalid vba function name... R2_ac

[electricpete]

I created a vba function called R2_ac in excel 2000.

It returns a double. It works fine when called by vba but creates an error the moment I enter it into excel (excel thinks I have an error in my formula as soon as I press enter).

Even if I simplify the function to:
Function R2_ac(ab As Double) As Double
R2_ac = 1
End Function

I still get an error typing in spreadsheet = R2_ac(1).

When I change the name of the function to Rrot, everything works fine.

I have seen before names starting with Rn or Cn where N is an integer cause problems. Can anyone explain why that is?

 

[mintjulep]

Excel probably gets confused and thinks you're trying to do an R1C1 range reference.

Maybe it's a reserved syntax.

 

[electricpete]

I can understand it gets confused with R2... but R2_ac ?

I guess it's just a bug. Sort of like -5^2 (just kidding).

 

[combo]

What error do you get? Is there a name R2_ac in the worksheet?

combo

 

[electricpete]

The error that I get is the same one if you made a typographical error and spelled a name that doesn't exist.

"The formula you typed contains an error.
For information about fixing common formula problems, click Help.
To get assistance in entering a function, click OK, then click Function on the insert menu [btw I did that... still gives error]
If your are not trying to enter a formula, avoid using an equal sign or minus sign, or precedie it with a single quotation mark"

Very helpful, eh? They forgot option 4.... You (the user) wrote something perfectly logical in compliance with all stated rules, but I (the program) am not going to follow my rules ;-)

 

[electricpete]

And no there is no R2_ac named range in the worksheet. Try it on a brand new spreadsheet...

 

[electricpete]

Is this something that is fixed after excel 2000?

 

[SkipVought]

Apparently any range name beginning with Rn or Cn is illegal,

I tried using your name via Inert > Name > Create - Create names in TOP row, and the resulting name ...
[tt]
_R2_ac
[/tt]
which is typical for names using cell references or starting with a number.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

It's strange, I did a test in excel 2000 and the function named R2_ac works perfectly.
The help file limits range names to:
- starts with underscore or letter,
- contains digits, letters, underscores or dots,
- not a valid address,
- no spaces,
- up to 255 characters.

I changed function name to A1, when applied in the worksheet, only #REF! is displayed in the cell, no other messages.

combo

 

[electricpete]

It's strange, I did a test in excel 2000 and the function named R2_ac works perfectly.

For me it works perfectly when called from vba, but gives an error when you enter it into a spreadsheet cell. Did you put it in a spreadsheet cell?

 

[electricpete]

Try my spreadsheet. Follow instructions on sheet 1 and see if you get an error. (I thought this was universal, but I'd be interested to know if it's just me)
home.comcast.net/~electricpete/tek-tips/R2_acBook1.xls

 

[electricpete]

Let me make that link a little more user friendly:
http:www.home.comcast.net/~electricpete/tek-tips/R2_acBook1.xls

 

[electricpete]

I guess I must be TGML challenged because it still didn't work. I guess you guys can figure out how to cut/paste the address into your browser.

 

[combo]

I've got your file, the function works. Concerning my previous post, the function worked in the spreadsheet.

I did some extra tests, the reason it worked is that I had non-english excel version with other than RC row/column notation. After changing function name according to local settings I have the same error. It occured that UDF name can't start with any valid address in RC notation (but possible for A1 addresses, for instance A1_UDF).
Funny thing happens in higher excel versions. With RC reference style set, in edit mode, your function points to the second row, it is possible to drag reference and the function name (first part, i.e. R2 equivalent) changes.

So the problem is in other excel versions too and is linked with local RC abbreviations.

combo