Calling Excel's worksheet function from vba code

Question

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?

Answer 1

Here is a tiny example:

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

Example

#	A	B	C	value in C
	Ref	Value	Formula	Output
1	B1	Gold	=INDIRECT(A1)	Gold
2	B2	Silver	=INDIRECT(A2)	Silver

Running this macro:

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

will produce:

Answer 2

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.