[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 D4

I'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 D4

It 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!