Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with function / Formula | Excel Worksheet Functions | |||
Help with OR function in IF function formula | Excel Worksheet Functions | |||
Help with function/formula please!!! | Excel Worksheet Functions | |||
Function or formula | Excel Worksheet Functions | |||
A formula/function to return a formula/function | Excel Worksheet Functions |