use vba function in formula
 

Hi !

Is ther any way to use some vba function i wrote in my cells formulas ??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
.....
end Function

Thank you all for your answers

Sylvain Caillet

use vba function in formula
 

Hi
if you don't declare the function as private and put it in a standard
module you can simply use your function as you would use a worksheet
function.
Also see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"news.wanadoo.fr" schrieb im Newsbeitrag
...
Hi !

Is ther any way to use some vba function i wrote in my cells formulas
??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
....
end Function

Thank you all for your answers

Sylvain Caillet

use vba function in formula
 

Your function can be called as any other worksheet function, exactly as you
show. AN example function is

In your function you should only look to manipulate the input data, or some
fixed worksheet data, and return a result. You should not try to amend any
worksheet/cell attributes such as the cell colour, font, it will not work.

So your function might look like as an example

Public Function AnyReds(rng As Range) As Boolean
Dim cell As Range

For Each cell In rng
If cell.Interior.ColorIndex = 3 Then
AnyReds = True
Exit Function
End If
Next
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"news.wanadoo.fr" wrote in message
...
Hi !

Is ther any way to use some vba function i wrote in my cells formulas ??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
....
end Function

Thank you all for your answers

Sylvain Caillet

use vba function in formula
 

Thank you for your quick reply but i need one more information, please.

What should be the return type of my function to be useable as argument for
other standard functions in formulas, such as Sum, Correlation coefficient
calculus, ... ?

I have tried the following function, but it doesn't work :

Public Function myRange(chaine As String) As Range
plage = Sheets("Référence").Range(chaine)
End Function

If i write a formula such as
=SUM(myRange("B29:B31"))
It doesn't work.

Thank you for your help !

Sylvain Caillet



"Frank Kabel" a écrit dans le message de news:
...
Hi
if you don't declare the function as private and put it in a standard
module you can simply use your function as you would use a worksheet
function.
Also see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"news.wanadoo.fr" schrieb im Newsbeitrag
...
Hi !

Is ther any way to use some vba function i wrote in my cells formulas
??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
....
end Function

Thank you all for your answers

Sylvain Caillet

use vba function in formula
 

Thank your for your help but i need one more information. Please, you can
see the question in the first answer of this thread.

Sylvain

"Bob Phillips" a écrit dans le message
de news: ...
Your function can be called as any other worksheet function, exactly as
you
show. AN example function is

In your function you should only look to manipulate the input data, or
some
fixed worksheet data, and return a result. You should not try to amend any
worksheet/cell attributes such as the cell colour, font, it will not work.

So your function might look like as an example

Public Function AnyReds(rng As Range) As Boolean
Dim cell As Range

For Each cell In rng
If cell.Interior.ColorIndex = 3 Then
AnyReds = True
Exit Function
End If
Next
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"news.wanadoo.fr" wrote in message
...
Hi !

Is ther any way to use some vba function i wrote in my cells formulas ??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
....
end Function

Thank you all for your answers

Sylvain Caillet

use vba function in formula
 

Sylvain,

You need to return the Function name to the caller. and range is an object
so you need to Set it

Public Function myRange(chaine As String) As Range
Set myRange = Sheets("Référence").Range(chaine)
End Function
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Sylvain" wrote in message
...
Thank you for your quick reply but i need one more information, please.

What should be the return type of my function to be useable as argument
for
other standard functions in formulas, such as Sum, Correlation coefficient
calculus, ... ?

I have tried the following function, but it doesn't work :

Public Function myRange(chaine As String) As Range
plage = Sheets("Référence").Range(chaine)
End Function

If i write a formula such as
=SUM(myRange("B29:B31"))
It doesn't work.

Thank you for your help !

Sylvain Caillet



"Frank Kabel" a écrit dans le message de news:
...
Hi
if you don't declare the function as private and put it in a standard
module you can simply use your function as you would use a worksheet
function.
Also see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"news.wanadoo.fr" schrieb im Newsbeitrag
...
Hi !

Is ther any way to use some vba function i wrote in my cells formulas
??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
....
end Function

Thank you all for your answers

Sylvain Caillet

use vba function in formula
 

Hi
try
Public Function myRange(chaine As String) As Range
set myRange = Sheets("Référence").Range(chaine)
End Function


--
Regards
Frank Kabel
Frankfurt, Germany

"Sylvain" schrieb im Newsbeitrag
...
Thank you for your quick reply but i need one more information,
please.

What should be the return type of my function to be useable as
argument for
other standard functions in formulas, such as Sum, Correlation
coefficient
calculus, ... ?

I have tried the following function, but it doesn't work :

Public Function myRange(chaine As String) As Range
plage = Sheets("Référence").Range(chaine)
End Function

If i write a formula such as
=SUM(myRange("B29:B31"))
It doesn't work.

Thank you for your help !

Sylvain Caillet



"Frank Kabel" a écrit dans le message de
news:
...
Hi
if you don't declare the function as private and put it in a
standard
module you can simply use your function as you would use a
worksheet
function.
Also see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"news.wanadoo.fr" schrieb im Newsbeitrag
...
Hi !

Is ther any way to use some vba function i wrote in my cells
formulas
??

for example : =myfunction(A1:A10)

with

function myfunction (myRange as range)
....
end Function

Thank you all for your answers

Sylvain Caillet