use vba function in formula

[news.wanadoo.fr]

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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[Sylvain]

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

 

[Sylvain]

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]

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)

 

[Frank Kabel]

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