why cant use Text() function in user defined function?

[dleizer]

folks

Can somebody explain to me why i am getting the following error when i try to construct a simple user defined function

The error is Compile Error: Sub or Function not defined

The user defined function I created is

Function test(dd)
test = Text(dd, "mm/dd/yy")
End Function

The "TEXT" word is highlighted when the compile error is displayed

Any ideas

Thanks

 

[VoG]

Try

test = Format(dd, "mm/dd/yy")

 

[Jonmo1]

Vog's solution with Format instead of Text is definately preferred...
Because Format is a built in VBA function.

But the reason yours didn't work is because TEXT is a worksheet function, not a VBA function.
You have to tell the code that you want to use a worksheet function...

test = WorksheetFunction.Text(dd, "mm/dd/yy")

 

[dleizer]

Jonmo1 & VoG
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for your prompt and useful response (as you expected both solutions worked!)<o></o>
As youcan see i am obviusly relatively inexperienced so off to google to learn all about worksheetFunctions (is there a list of worksheet functions?)<o></o>
<o></o>
Having tested it i now discovered that i cant use the =CONCATENATE("a","b") function in vba (get same error message) but i can’t solve the problem by using the worksheetFunction prefix <o></o>

Is there an similar explanation / solution for this and howe can one find out which functions can be used in VBA?

Thanks

 

[Jonmo1]

Try using

MyVariable = "a" & "b"


The & symbol does the same thing as concatenate.
Also in worksheet formulas as well.