Watch Window Value Different from Worksheet Function Value - Excel VBA

[smercker]

When I run this code, the watch value displays the correct values, but when I plug the formula into my worksheet, it displays different values.

This is what I used in the Immediate window
?TOP(Worksheets("Exp").Range("C24"),Worksheets("Exp").Range("PT_Exp_Total"),1)
It returns:
Hair Cut
Pedicure
test
hi
new

This is what I used in the worksheet in cells F24:F28
{=TOP(C24,PT_Exp_Total,1)}
It returns:
Hair Cut
Hair Cut
Hair Cut
hi
new

I'm very new to both Excel and VBA. Any help on why these would be giving different results?

Function TOP(Lu_Val, Lu_Rng, Optional Rtn_Col) As Variant
'Looks up Lu_Val in the top row of Lu_Rng, sorts the values in that column (LuColNum) in descending order, and returns corresponding values from Rtn_Col in Lu_Rng. If Rtn_Col is missing, returns values from LuColNum.
    
    Dim LuColNum As Long
    'Looks up Lu_Val in Lu_Rng and returns the column number
    LuColNum = WorksheetFunction.Match(Lu_Val, WorksheetFunction.Index(Lu_Rng, 1, 0), 0)
    Dim i As Long
    Dim NumCnt As Long
    Dim n
    Dim RowRank() As Long
    Dim LuCol As Range
    Set LuCol = WorksheetFunction.Index(Lu_Rng, 0, LuColNum)
    Dim firstAddress
    Dim RtnVal()
    Dim j
    Dim Rank
        
    'Set default value for Rtn_Col
    If IsMissing(Rtn_Col) = True Then
        Rtn_Col = LuColNum
    End If
    
    'Returns the number of numeric values in the LuCol
    NumCnt = WorksheetFunction.Count(LuCol)
        
    'Resizing RowRank and RtnVal to the number of numeric values in LuCol
    ReDim RowRank(1 To NumCnt) As Long
    ReDim RtnVal(1 To NumCnt)
    
    'Sorts the LuCol in descending order and returns the row numbers in RowRank
    For i = 1 To NumCnt
    Set n = LuCol.Find(WorksheetFunction.Large(LuCol, i))


    If Not n Is Nothing Then
        firstAddress = n.Address
        Do
            RowRank(i) = n.Row - Lu_Rng(1, 1).Row + 1
            Set n = LuCol.FindNext(n)
            If n Is Nothing Then
                Exit Do
            ElseIf firstAddress <> n.Address Then
                i = i + 1
            End If
        Loop While firstAddress <> n.Address
    End If
    Next i
    
    'Returns the sorted values from the Rtn_Col
    j = 1
    For Each Rank In RowRank
        RtnVal(j) = WorksheetFunction.Index(Lu_Rng, Rank, Rtn_Col)
        j = j + 1
    Next Rank
    
    TOP = WorksheetFunction.Transpose(RtnVal)
    
End Function

	A	B	C	D	E	F	G	H	I	J	K
5	Row Labels	Debt	Emergency	Entertainment	Food	Health Care	Housing	Personal Care	Savings	Transportation	Utilities
6	Bowling			15.5
7	Car Payment									350
8	CC	150
9	Deposit								60
10	Doctor					28
11	Electric										250
12	Gas									44.13
13	Hair Cut							25
14	Movies			12.25
15	Pedicure							25
16	Rent						900
17	Taco Bell				8.46
18	Wendys				11.16
19	Car Repairs		100
20	test							25
21	new							15
22	hi							20
23
24			Personal Care			Hair Cut
25						Hair Cut
26						Hair Cut
27						hi
28						new

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Exp​

Array Formulas Cell Formula F24:F28 {=TOP(C24,PT_Exp_Total,1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To PT_Exp_Total =OFFSET(Exp!$A$5,0,0,COUNTA(Exp!$A:$A)-2,11) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Welcome to the Board.

I've done some poking around, and I'm a bit confused. Your function returns an array, so when I try

?TOP(Worksheets("Exp").Range("C24"),Worksheets("Exp").Range("PT_Exp_Total"),1)

in the Immediate window, I get an error. If I add subscripts like this:

?TOP(Worksheets("Exp").Range("C24"),Worksheets("Exp").Range("PT_Exp_Total"),1)(2,1)

I can see the individual values. When I do that, I get different results from the Immediate window and the spreadsheet, like you, but different from yours. Possibly because I didn't copy the data exactly the same. To figure out what's going on, I'd have to start debugging your function. But it's fairly long. Based on the description, I suspect I could write a shorter version. Would that be acceptable?

 

[smercker]

Thanks Eric. That would be awesome! This is my first attempt at any kind of programming, so I have a lot to learn.

 

[Eric W]

Right, learning to program requires a lot of trial and error, just to see what works. Here's my take on your function:

Function TOP(Lu_Val As String, Lu_Rng As Range, Optional Rtn_Col As Long = 0) As Variant
Dim MyData As Variant, LuColNum As Long, i As Long, MyResults() As String

    MyData = Lu_Rng.Value
    On Error GoTo NoMatch:
    LuColNum = WorksheetFunction.Match(Lu_Val, WorksheetFunction.Index(MyData, 1, 0), 0)
    
    If Rtn_Col = 0 Then Rtn_Col = LuColNum
    
    With CreateObject("System.Collections.SortedList")
        For i = 2 To UBound(MyData, 1)
            If MyData(i, LuColNum) <> "" Then .Add MyData(i, LuColNum) + i / 100000, MyData(i, Rtn_Col)
        Next i
    
        ReDim MyResults(1 To IIf(.Count > 100, .Count, 100), 1 To 1)
        For i = 0 To .Count - 1
            MyResults(i + 1, 1) = .getbyindex(.Count - i - 1)
        Next i
    End With
    
    TOP = MyResults
    Exit Function
    
NoMatch:
    ReDim MyResults(1 To 100, 1 To 1)
    MyResults(1, 1) = "Header not found"
    TOP = MyResults

End Function

The SortedList is an advanced feature that makes it so you don't have to manually write a sort.

Also, it appears that your Defined Name for PT_Exp_Total is cutting off the last 2 rows. Either remove the -2 from it, or try something like:


=OFFSET(Exp!$A$5,0,0,LOOKUP("zzzz",Exp!$A:$A,ROW(Exp!$A:$A))-ROW(Exp!$A$5)+1,11)

which doesn't care if there are values in A1:A4.

Let me know how this works.

 

[smercker]

That works perfectly! Thank you so much. Now I just need to go through it to figure out what it's doing exactly.

The Defined Name is for a Pivot Table that has two more items in the first column, I just didn't include that in the screen shot. The LOOKUP to get the last row is clever, I'll have to keep that one in mind.

Also, from your first post, you had mentioned getting an error when you didn't include the subscripts. I realize now that I had a stop at the end of the function so I could take a look at the Watch values, so I never fully ran the function.

I really appreciate you taking the time to do that for me. It's a big help. Thanks again!

 

[Eric W]

I'm very glad this helped! Keep plugging away at programming. There's a lot to learn, but it's very rewarding.
Let me know if you have any questions about it.

 

[smercker]

I do have one question. I'm wondering why you set the array to 100:

ReDim MyResults(1 To IIf(.Count > 100, .Count, 100) , 1 To 1)

Rather than just the .Count:

ReDim MyResults(1 To .Count , 1 To 1)

 

[Eric W]

Ah! Excellent question! When you put the formula on the worksheet, you select a range F24:F28, enter the formula and press Control+Shift+Enter. If you just set the Results array to .Count, and the .count is less than the size of the range, then the last few cells in the range will have an error in them. If you set the size of the array to 100 (or something bigger than the maximum range you expect to use on the sheet), and make sure that the trailing values in the array are null (which I did by defining MyResults as String), then you just get empty cells. It's up to you how you want to see it, but I find it a bit cleaner with no error messages. Try it both ways and see.

And as an FYI, this section of code is a bit neater than what I had before:

    For i = 1 To .Count 
        MyResults(i, 1) = .getbyindex(.Count - i)
    Next i

 

[smercker]

I see. I do want to format the numbers as currency though, so I'll have to set MyResults as a Variant. I was going to handle the errors with conditional formatting. Thanks again for all your help.

 

[Eric W]

That sounds like a viable approach. I'm glad to see you adapting the macro for your particular needs. One other thing that might be useful to point out is how I used "+ i / 100000" on the 9th line down. I used this to prevent duplicate keys in the SortedList (which is a no-no), but the amount was so small it did not affect the sort order.

Glad to help.