Calling Excel's worksheet function from vba code

0
votes

With worksheetfunction's methods I can call from vba code a lot of excel's function without reinvent the wheel. Unfortunately not all function are available there but other simple function can be find under vba library. Now I need to use two functions:

  • address()
  • indirect()

But none of two is available as method of vba or worksheetfunction

(here what is available: https://msdn.microsoft.com/en-us/library/office/ff822194(v=office.14).aspx)

Using the object browser on the editor I can't find those functions... how can I do?

2
vbaexcel
show code please, we can answer given the context. - S Meaden
Range.Address is what you are looking for. - K.Dᴀᴠɪs
IF() is also not available, because it has a direct vba equivalent. - Scott Craner
in place of INDIRECT(): Range("youraddressstring") - Scott Craner

2 Answers

2
votes

INDIRECT is a way of resolving a string, this can be done in VBA easily. ADDRESS can also be found as a member of a Range object. That's why they are not available.

0
votes

Here is a tiny example:

A1 contains the text B1
B1 contains the text Gold
C1 contains the formula:=INDIRECT(A1):

enter image description here

Running this macro:

Sub UsingEvaluate()
    MsgBox Evaluate("INDIRECT(A1)")
End Sub

will produce:

enter image description here