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

S

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.
 
P

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
 
G

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
 
G

Gary''s Student

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top