Enter Formulas in Excel with a Macro


Why it is not possible to enter the formulas like iferror, left to excel vba?




Hi Esader and welcome to the Forum.

You can use IFERROR (or other worksheet functions) in VBA by using the Application.WorksheetFunction method. For example, if cell A2 has the formula =1/0 (which produces a #DIV/0! error) then this line: 

Debug.Print Application.WorksheetFunction.IfError(Range("A2"), "Divide by zero error!")

would produce the custom error output "Divide by zero error!" in the VB Explorer Intermediate window (- you can paste that there and press return, once you've set A2 to the error condition). This is as if you put this in a cell:

=IFERROR(Range("A2"), "Divide by zero error!")

Take a look at Don's tutorial Worksheet Functions for VBA - VLOOKUP() and More for other examples.

Note however that the LEFT function is available natively in VBA so the line 

r= Left("Esader",3)
would set the string variable r to "Esa" (i.e. the first three characters), as would the worksheet function in the cell. 

Hope this makes sense.


IFERROR() is a container function, meaning, it evaluates another function that is supplied to it as an argument. VBA doesn't support the use of worksheet container functions because it has its own.

You can use a worksheet function in VBA to obtain a value and then use VBA to examine that value. Take this example:-

    Dim R As Long

    On Error Resume Next
    R = WorksheetFunction.Match(123, Range("A:A"), 1)
    If Err Then R = Cells(Rows.Count, "A").End(xlUp).Row + 1

The code looks for "123" in column A and returns the number of the row where it was found. But the MATCH function errors out if no match is found and VBA's On Error Resume Next captures that exception and returns the next empty cell instead.

Now consider this alternative.

    Dim R As Variant

    R = Application.Match(123, Range("A:A"), 1)
    If IsError(R) Then R = Cells(Rows.Count, "A").End(xlUp).Row + 1

Instead of using the Worksheet function this snippet uses the Excel application's equivalent. Instead of assigning the return value to a Long integer it assigns it to a Variant. This is because Application.Match will return an error if no match is found. That error is an object (Error object). If we had tried to assign it to a Long the code would crash. But assigning the error object to a Variant is possible, and now all we have to do is check if R contains an error. VBA's own IsError() function can do that.

So, why is it not possible to use container functions in VBA? It's because VBA offers so many more and much better alternatives. Worksheet functions (or Excel application functions) must be deployed to increase VBA's capabilities, not to replace them.


Answer the Question

You must create an account to use the forum. Create an Account or Login