Insert a custom function in a cell by runnig a sub or through amacro.

[Subodh]

I have a function named mysub and with parameters that has to be
supplied to the function.
I want that when the cell where the function is to be inserted, the
user can type the arguments of the
function sub after they have run the sub. Lets say, that after the sub
is run, they get =mysub(..) with the
cursor in between the parenthesis and so that they can move with
typing of the arguments/parameter of the
function.
So, in summary, the advantage is that, the user should not type =mysub
through the keyboard
or they donot even need to remember that provied that there are large
number of such functions.
Thanks for your response.

 

[Per Jessen]

I am not sure if it can be done. Anyway I would use an inputbox in the macro
to prompt for the parameter, or if there are several paramteres maybe use an
userform. Then insert the complete formula into the cell.

Regards,
Per

 

[Gary''s Student]

An interesting request!

This little macro enters the function in the active cell. It then uses
SendKeys to enter Edit Mode and move the cursor betweeen the parens:

Sub FormulaStarter()
Dim r As Range
Set r = ActiveCell
r.Formula = "=mysub()"
Application.SendKeys "{F2}"
Application.SendKeys "{LEFT}"
DoEvents
End Sub

 

[Gary''s Student]

Sub FormulaStarter()
Dim r As Range
Set r = ActiveCell
r.Formula = "=mysub()"
Application.SendKeys "{F2}"
Application.SendKeys "{LEFT}"
DoEvents
End Sub

 

[Subodh]

An interesting request!

This little macro enters the function in the active cell.  It then uses
SendKeys to enter Edit Mode and move the cursor betweeen the parens:

Sub FormulaStarter()
Dim r As Range
Set r = ActiveCell
r.Formula = "=mysub()"
Application.SendKeys "{F2}"
Application.SendKeys "{LEFT}"
DoEvents
End Sub

--
Gary''s Student - gsnu201003





- Show quoted text -

Thanks Gary's
It worked as i had expected.
Thanks a lot.

 

[Gary''s Student]

You are very welcome.
Thanks for the feedback!