[RESOLVED] My Public Function only works in a worksheet cel. Versions conflict!

[Tobian]

When I insert following Public Function ValueOneBack() in a Cell of a Excel worksheet, it works.
Eg. when I insert ValueOneBack() in Cell H7, it returns the value of Cell G8.

Now I want to know if the result of ValueOneBack() is also present in Range B1:B4. So I use the result of ValueOneBack() as LookupValue in the Function MatchOneBack(). But now ValueOneBack() doesn't return a value, which makes MatchOneBack() always false.
Can somebody tell me what I do wrong?

Public Function ValueOneBack() As Variant

ValueOneBack = ""

Application.Volatile

If TypeName(Application.Caller) = "Range" Then
If Application.Caller.Row <> 65536 And Application.Caller.Column <> 1 Then
ValueOneBack = Application.Caller.Offset(1, -1).Value
End If
End If

End Function

Function MatchOneBack() As Integer
Dim LookupValue As Variant
Dim rngMatrix As Range

LookupValue = ValueOneBack

Set rngMatrix = Worksheets(1).Range("B1:B4")

If rngMatrix.Find(What:=LookupValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False) Is Nothing Then
MatchOneBack = 0
Else
MatchOneBack = 2
End If

Set rngMatrix = Nothing

End Function

[Webtest]

I'm really shaky on this one, but my guess is that the problem is in your Find statement:

If rngMatrix.Find(What:=LookupValue, _

I think "What" expects a string(?), and you are giving it a Range Object. Since Function "ValueOneBack" only returns a Range Object, why don't you just declare it as returning Type Range?

Again, I really don't know much about this ... but have you put in debugging statements, such as "MsgBox LookupValue.address" right after you set it in "MatchOneBack"?

[Tobian]

Thanks, I try. I can have some help with debugging statements. What do you mean with that?

[Webtest]

In your function "MatchOneBack": ...
LookupValue = ValueOneBack
'Add Here:
MsgBox LookupValue.Address
...This will pop up a message box with the Address of the Range Object that is returned by the call to the ValueOneBack function.

[Tobian]

I tried MsgBox LookupValue.Address and get Runtime error 424, Object needed.
What could this mean?

[Tobian]

About
my guess is that the problem is in your Find statement:

If rngMatrix.Find(What:=LookupValue, _

I think "What" expects a string(?), and you are giving it a Range Object. Since Function "ValueOneBack" only returns a Range Object, why don't you just declare it as returning Type Range?

When I put a value in Range B1:B4 , eg. "3" in Cell B3 and I give LookupValue = 3 in MatchOneBack() in stead of ValueOneBack, it works. How do I have to declare LookupValue? Don't know how to declare the returning Type Range.

[Webtest]

I sent you a private message ... I apologize, your Function "ValueOneBack" DOES return a value ... not a Range Object. Just change the message box call to "MsgBox LookupValue". I generated the following matrix and put "=ValueOneBack()" in Cell C2. You'll notice that it does update with the value in Cell B3. I put "=MatchOneBack" in Cell C3 and Cell D3. The result in Cell C2 is "2" indicating that the string "I'm B4" IS matched in B1:B4. The result in Cell D3 is "0" indicating that the string "I'm C4" was NOT matched in B1:B4. This indicates to me that the "Find" is working as expected. A B C D
1 0 I'm B1 I'm C1 I'm D1
2 0 I'm B2 I'm XX I'm D2
3 0 I'm XX 2 0
4 0 I'm B4 I'm C4 I'm D4I'm going to add one more column to my matrix and test it, but so far, it seems to work as coded. What is different from what you are trying to do?

[Webtest]

I inserted a new "B" Column. Here are two sets of results:
"ValueOneBack" is now in Cell D2
"MatchOneBack" is now in Cells D3 and E3 A B C D E
1 0 0 I'm B1 I'm C1 I'm D1
2 0 I'm A4 I'm B2 I'm B3 I'm D2
3 0 I'm D4 I'm B3 0 0
4 0 0 I'm B4 I'm C4 I'm D4

A B C D E
1 0 0 I'm B1 I'm C1 I'm D1
2 0 I'm B4 I'm B2 I'm B3 I'm D2
3 0 I'm C4 I'm B3 2 2
4 0 0 I'm B4 I'm C4 I'm D4It looks like it works fine to me???

[Tobian]

no problem, Glad you help me. I tried your code but get "0" in C3..!? It is different from your outcome, how come? What version your working with? My version is 2000.

[Webtest]

What did you get in the popup Message Box?
Select (highlight) your matrix and copy it into NotePad. Then copy the NotePad into your next post, enclosing it in code tags. Of course, I manually added the Column legends across the top and the Row Legends along the left edge. You don't need to do that.

I am using your exact code that you posted, with the addiition of the MsgBox call to display "LookupValue".

[Webtest]

Here are the formulas I am using in my cells: In Cell D2: =ValueOneBack()
In Cell D3: =matchoneback()
In Cell E3: =matchoneback()


I'm using Excel 2003, but I could try it in '97 if I have the time.

[Tobian]

I got a lot of msg boxes I had to click away. But it was containing "I'm B4", that's ok. But it is strange that it works in your version and not in mine.

[Webtest]

Yes, the message boxes will pop up for every instance of "MatchOneBack" every time an update is done. Does the string "I'm B4" exist in B1:B4? If so, you've got to be getting a "2" in Cell C3 ??? Let me try it in '97.

[Webtest]

It appears to be broken in '97 ... I'll try to figure out why.

[Tobian]

I zipped my worksheet with macro, try if it works in your version, please

[Webtest]

Duhhh ... Monday Mornings. "Find" is a WORKSHEET function somewhere prior to version 2003. You have to use "Application.WorksheetFunction.", HOWEVER, not all worksheet functions can be called from VBA!!! I couldn't get it to work with '97. You'll probably have to use a 'native' VBA search function.

Sorry, I can't unzip anything in my present incarnation.

[Webtest]

This works in '97 in place of your "Find" function.
Dim aCell As Range
For Each aCell In rngMatrix
If aCell.Value = LookupValue Then
MatchOneBack = 2
Exit For
Else
MatchOneBack = 0
End If
Next aCell

[Webtest]

Of course you can shorten the search function: Dim aCell As Range
MatchOneBack = 0
For Each aCell In rngMatrix
If aCell.Value = LookupValue Then
MatchOneBack = 2
Exit For
End If
Next aCell

[Tobian]

Thanks, problem is solved. Good to know that Office versions can react differently on same code.

[Webtest]

The lousy part is when Worksheet Functions don't work AND they don't generate any warnings! It's a wonderful life, but nobody said it's easy!