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?

IF() is also not available, because it has a direct vba equivalent. — Scott Craner
– Scott Craner, Commented Dec 14, 2017 at 19:33

S Meaden · Accepted Answer · 2017-12-14 19:27:04Z

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.

answered Dec 14, 2017 at 19:27

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

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

Comments

surfmuggle · Accepted Answer · 2023-03-17 18:42:29Z

0

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:

edited Mar 17, 2023 at 18:42

surfmuggle

6,0408 gold badges58 silver badges96 bronze badges

answered Dec 14, 2017 at 19:57

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Calling Excel's worksheet function from vba code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related