Worksheet Function in Excel Vba with concatenated values

Question

I have the following formula:

 dim functionString as String

 functionString = "IFERROR(AND(MATCH(" & FirstName.Value & ",C2:C1048576, 0)>0, (MATCH(" & LastName.Value & ",B2:B1048576, 0)>0)), MAX(A2:A1048576)+1)"

What I want to be able to do is call it from the VBA code so it would look like.

 application.WorksheetFunction(functionString)

I know that I can place it on the worksheet at some cell that's never going to be used: IE:

 Activesheet.range("ZZ1000").formula = "="& functionString

and then reference that cell without worrying whether the program would inadvertently crash; but is there a way to do such a formula from VBA directly?

Basically I'm looking to see whether FirstName.Value and LastName.Value (which are defined elsewhere in the code) together are in the worksheet in column B and column C. As I'm writing this, I realized I need to make sure that they are both in the same row as well and not in different rows.

Try Application.Evaluate(functionString)?

David Zemens
– David Zemens

2017-01-20 19:43:00 +00:00
Commented Jan 20, 2017 at 19:43 — David Zemens
– David Zemens, Commented Jan 20, 2017 at 19:43

David Zemens · Accepted Answer · 2017-01-20 19:45:10Z

1

You could try Application.Evaluate(functionString) but depending on complexity it may be better to use VBA functions instead of WorksheetFunctions.

The Application.Match function will return an error type if the value is not found in the range/array, so we Dim first, last as variant type to allow for this (without raising an error).

Dim first, last
' find the row where FirstName.Value appears in column C
first = Application.Match(FirstName.Value, Columns(3), False))
' find the row where LastName.Value appears in column B
last = Application.Match(LastName.Value, Columns(2), False))

If Not IsError(first) And Not IsError(last) Then
    If first = last Then
        ' match was found in both columns and on same row
        ' do something else...

    End If
End If

answered Jan 20, 2017 at 19:45

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

5 Comments

bdpolinsky Over a year ago

the application.match function doesn't appear when application is typed out

Mathieu Guindon Over a year ago

@bdpolinsky that's because it's late-bound. It's the same as WorksheetFunction.Match, except it will return an error value instead of raising a runtime error in case of no-match.

bdpolinsky Over a year ago

Thanks both. Seems like it works. Some more coding to do, but thats for next week.

bdpolinsky Over a year ago

@Mat's Mug can i get a tl/dr of late binding early binding?

Mathieu Guindon Over a year ago

@bdpolinsky late-bound calls resolve at run-time, early-bound resolve at compile-time; that's how IntelliSense only works with early-bound calls. Late-bound calls that can't resolve raise run-time error 438 "object doesn't support this property or method".

Collectives™ on Stack Overflow

Worksheet Function in Excel Vba with concatenated values

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