CODEWITHSUNDEEP
excelvba
Andrew Lem
Andrew Lem

Reputation: 21

Custom Excel VBA Function (Modified VLOOKUP) from Cell referring to a range in different file gives an error

First time I'm writing a stackOverflow question so please let me know if I do anything wrong.

I've searched for a few hours now and haven't been able to find a solution to my problem (usually I find the answer hence why this is my first question after using stackoverflow for a few years as a lurker).

Basically I'm trying to write a modified VLOOKUP function that functions similar to VLOOKUP except it return the "next smallest larger" value instead of the default "previous largest smaller" value. I'm aware of the index/match method unfortunately I would need to carefully replace literally thousands of VLOOKUPs manually that already exist in the workbook I'm currently cleaning up at work. Therefore I resorted to writing a VLOOKUPnew function so I could just "find/replace" all the VLOOKUP with VLOOKUPnew.

Function VLOOKUPnew(lookup_value As Variant, table_array As Range, _
        col_index_num As Integer, Optional exactMatch As Boolean) As Variant
    Dim row As Integer

    Debug.Print table_array.Address

    With Application
        On Error GoTo NO_ROW
        row = .Match(lookup_value, table_array.Columns(1), 0)
        On Error GoTo 0

        If row = -1 And Not exactMatch Then
            row = .Match(lookup_value, table_array.Columns(1), 1)
            row = row + 1
        End If

        VLOOKUPnew = .index(table_array.Columns(col_index_num), row, 0)
    End With

Exit Function

NO_ROW:
    row = -1
    Resume Next
End Function

And I succeeding in writing the function but hit a snag. Because I declared "table_array" as a Range, vba fails to identify range references to other workbooks

e.g. "=VLOOKUPnew($A432,'reallyLongFilepath/[filename.xlsx]tablename'!$B$6:$N$35,columnNumber,0),FALSE)" resolves to a #VALUE error

The really weird thing is that if I open the file, then the filepath drops out of the formula (becoming just "=VLOOKUPnew($A432,'[filename.xlsx]tablename'!$B$6:$N$35,columnNumber,0),FALSE)") and then my custom function works just fine returning the correct value.

So my problem is how do I resolve not having to open the other file to use this workbook. I'm not even sure how Excel is passing the address or range to the custom formula so I'm suspecting it's breaking when the filepath is included in the range reference. Is there a way to split the filepath, filename, sheet and address (after it has been passed in)? Or possibly pass it in as a string then easily split it? Or pass it in as something that will correctly identify the range in the different workbook?

Keep in mind that I'm trying to avoid changing the arguments of the function because I want to do the find/replace trick and that this is for work so there's a restraint on too much change in data layout. Also that the workbook is for other employees to use and I'm just setting it up for use.

Thanks in advance!

Andrew

Upvotes: 2

Views: 6371

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

You face quite a dilemma here!

The root problem is that while VLOOKUP can look into closed workbooks, a Range parameter in a UDF cannot. The range reference resolves to an error, so the function call fails with a type mismatch. If you change the table_array parameter type to Variant and put a break on the function header, you will see the parameter value as Error 2036.

While there are ways to look into closed workbooks, all of them (AFAIK) are quite slow. Since you mention ... I would need to carefully replace literally thousands of VLOOKUPs ... I suspect any solution along these lines would be unacceptably slow.

My reccomendation would be to go the INDEX/MATCH route, and write a VBA macro to do the formula updates for you.

Upvotes: 3

Related Questions