How can I call an Excel Function in VBA?

It doesn't work

Hi Scott,
I thank you for your interest about my problem.
I have already tried to do what you say, but it doesn't work very well. Visual Basic reply with message: "SUB OR FUNCTION NOT DEFINED" even if I use the function, as you suggest, "varMonth = Month(Now())".
My function (custom) is defined as well as Excel functions but I cannot call it. Why? Thanks.
Walter

Re: It doesn't work

Scotts answer should have worked ??
sounds like you have not defined your varibles
with a Dim ??
eg Dim VarMonth as double
OR
you have misTyped the name of the function.

What is the function you are trying to call ?

Ivan

Re: It doesn't work

Hi Ivan,
this is an example what I do in my Excel Sub:
------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Text As String
Text = Upper("hello")
End Sub
------
VBA reply with message "SUB OR FUNCTION NON DEFINED" and point to the word "Upper".

Re: It doesn't work

In the above case VBA is saying VBA or your
project has not defined this function !
What is this function Upper ?? is it a function
you have defined OR are you in fact after the
Upper case function ?? which should be Ucaes("hello") ???

Ivan

Re: It doesn't work

The function Upper is an Excel Function and not a VBA Funcion, category Text, that converts a text string to uppercase. It is only an example to understand how is it possible call an Excel Function in VBA. OK?
When, in Excel, I tab or I write in a cell, VBA reply with that message. Probably I wrong my code, but it is very small (only 2 rows). What can I do?

Re: It doesn't work

Walter
Not all excel functions are usable or avail
via VBA as they are used in excel
eg UPPER in excel has the VBA equal of UCase

To use excel functions you have to use the
syntax Application.Worksheetfunction.[function]

for a list of excel functions see online help.
In the Office assistant type
List of Worksheet Functions Available to Visual Basic
This will give you a list of worksheet functions
avail for VBA, your'll note that not all functions
are avial including your UPPER.

Ivan

Thanks - Grazie Ivan

Thank Ivan for your suggestions.

Sorry Ivan, last question

Sorry Ivan, I have read the office online help: Ok.
So a Custom Excel function (my function) cannot be included in VBA?

Re: Sorry Ivan, last question

Yes, you can use a custom fumction in VBA. If you
go back to Scotts answer he tells you how to
call a custum function.
eg.
You have the defined function as;

Function MyFunction(A as double, B as Double)
MyFunction = A * B
End Function

Now to call from VBA;

Sub Test()
Dim A as double
Dim B as Double
Dim Result as Double

A = cells(1,1)
B = Cells(2,1)

Result = MyFunction(A,B)
End sub

HTH

Ivan

Re: Sorry Ivan, last question

If something wasnot clear or you need
further clarifiction then post your code
and I'll have a look.

Ivan

There are a few ways to call functions and other routines. First, simply use the name of the function (with any appropriate values to pass along as well). For example, if I have a function called ReadInValues, I type ReadInValues in my current routine. Alternatively I can use the Call command (Call ReadInValues)

As far as Excel functions go, just use the function like you would inside a cell. I find more often that I usually assign the results of the Excel function to a variable.

For example:

varMonth = Month(Now())

assigns the current month to my variable named Month.

Scott