use vba function in formula

  • Thread starter news.wanadoo.fr
  • Start date
N

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
 
B

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)
 
S

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
 
S

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
 
B

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)
 
F

Frank Kabel

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