Combination of user defined fucntion and row() function, doesn't seem to work?

  • Thread starter Thread starter Sally
  • Start date Start date
S

Sally

There seems to be something weird here.

I have one user defined function, all it does is return the value sent
Function rowTestInteger(r As Integer) As Integer
rowTestInteger = r
End Function

I have 6 cells a1::b3
all with the formula =rowTestInteger(ROW())

results
1 1
2 2
3 3

So far fine

In column a, the conditional format is the same for all three
=rowTestInteger(ROW())=2
=rowTestInteger(ROW())=2
=rowTestInteger(ROW())=2

in Columns b, I change it a bit to
=rowTestInteger(b1)=2
=rowTestInteger(b2)=2
=rowTestInteger(b3)=2

I set the formats to background of red

a2 and b2 BOTH should be red, but only b2 is.
a2=2=ROW(), so it should be red too, but for some reason it is not!!!

-Sal
 
I don't know for sure, but here's my guess:

Try putting =row() in a cell (a1). Hit F2, F9, but watch the formula bar.
You'll see this:
={1}

A one element array.

If I changed your UDF to:
Option Explicit
Function rowTestInteger(r As Variant) As Integer
rowTestInteger = Evaluate(r)
End Function

it worked ok.

I'm guessing that even though you see a number in the cell, excel/vba sees it as
an array.
 
Back
Top