Enter Formulas in Excel with a Macro

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)

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.