How to write Excel function array selection/creation formula in Excel VBA

Question

Struggling to figure out how to write the following in Excel VBA:

=LOOKUP(2,1/(A:A=K8),F:F)

My current attempt looks as such:

Application.lookup(2, [1 / (dates = (lookupDate + lookupModifier))], balances))

When I break out to just the array formula I get a #VALUE error, as part of the lookup I get #NAME.

The original formula was taken from a site that didn't particularly explain what the central parameter does, while I understand what it does I am unsure of the correct terminology for it so apologies for that.

The full formula for reference:

Option Explicit
Public Function GetWeekEndBalanceFromStatement(lookupDate As Double, dates As Range, balances As Range) As Variant
    Dim lookupModifier As Integer

    Do While IsError(Application.lookup(2, [1 / (dates = (lookupDate + lookupModifier))], balances)) And lookupModifier > -7
        lookupModifier = lookupModifier - 1
    Loop

    GetWeekEndBalanceFromStatement = Application.lookup(2, [1 / (dates = (lookupDate + lookupModifier))], balances)
End Function

You mention an "array formula" and your formula suggests it is an array formula, but your originally lookup is missing the brackets, so just want to confirm that you are looking to replicate an array formula in VBA — psubsee2003
– psubsee2003, Commented Jan 14, 2018 at 21:17
I'm trying to recreate the original lookup, the inclusion of the square brackets in the formula came later as an attempt to remedy the problem I'm having getting said formula to work. For reference without the square brackets I only ever get the #VALUE error. — Grizzly
– Grizzly, Commented Jan 14, 2018 at 21:31
That is what I am asking - is the original formula an array formula or not?Does it work with or without the square brackets? — psubsee2003
– psubsee2003, Commented Jan 14, 2018 at 22:30
The [...] is a shortcut to Evalute where what's between the brackets is what you would type into the Exel formula bar. In this case dates etc are parameters to the function so are unknown to Excel. Try using dates.Worksheet.Evaluate(...) and build the formula with dates.Address etc — chris neilsen
– chris neilsen, Commented Jan 14, 2018 at 23:49

Grizzly · Accepted Answer · 2018-01-28 17:06:16Z

1

In your VBA code the [...] is a shortcut to Evaluate where what's inside the [ ] is literally what you would type into the Excel formula bar.

In this case dates and lookupDate are parameters to the Function, and `lookupModifier is a local variable, which are unknown to Excel and therefore cause an error.

You can use Worksheet.Evaluate instead, as this takes a string parameter, which you can build from your Functions Parameter.Address's

So, change

Application.Lookup(2, [1 / (dates = (lookupDate + lookupModifier))], balances)

to

Application.Lookup(2, dates.Worksheet.Evaluate("1 / (" & dates.Address & " = (" & lookupDate & " + " & lookupModifier & "))"), balances)

In addition, if you are passing whole columns (as in your formual example) this will be inefficient in VBA. You can reduce those ranges to only those cells containing data, like this

If IsEmpty(dates.Cells(dates.Cells.Count)) Then
    Set dates = dates.Resize(dates.Cells(dates.Cells.Count).End(xlUp).Row, 1)
End If

Similar for balances

To add flexibility, consider this change to lookupDate

Change lookupDate As Double to lookupDate As Variant and add code to check its type

If TypeName(lookupDate) = "Range" Then
    ... (dates.Address = (lookupDate.Address ...
Else
    ... (dates.Address = (lookupDate ...
End IF

edited Jan 28, 2018 at 17:06

Grizzly

5513 silver badges8 bronze badges

answered Jan 15, 2018 at 1:25

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

QHarr Over a year ago

How is this IsEmpty(dates.Cells(dates.Cells.Count) working? Is it testing a range?

chris neilsen Over a year ago

@qharr dates is a range parameter to the function. dates.Cells(dates.Cells.Count) refers to the last cell in range dates. If it's empty do the End Up to get to the last used cell. Conversely if it's not empty then dates already is limited to the data filled range so don't do the End Up

QHarr Over a year ago

Thanks. I thought so but then confused myself.

Grizzly Over a year ago

Thanks very much, solution works bar one small alteration. 'lookupDate' is passed in as a Double. Possible fixes to this are removing '.Address' from lookupDate or treating 'lookupDate' as a Range. Is either preferable in this scenario? Thanks also for the inefficiency solution, once the formula was working it was indeed quite slow until I added those lines in.

chris neilsen Over a year ago

@Grizzly regarding lookupDate, I'd change it to As Variant and test its type. If it's a value drop the .Address. I'll update the answer

Collectives™ on Stack Overflow

How to write Excel function array selection/creation formula in Excel VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related