macro to insert function without parameters

[freddy007]

My goal is to write a macro that inserts this into the active cell:

=stdev( or =stdev()

and then have it act just like if the user had typed the same thing,
that is, be ready to accept a range of cells.

If I type: =stdev() into a cell and hit enter, I get a message that
says "The formula you typed contains an error". If I say ok, then the
message goes away and I get the behavior I want. So what I want to do
is automate that (including the clicking ok part.)

I have tried:
activecell.value = "=stdev()"
activecell.text = "=stdev()"
sendkeys "=stdev("

I get errors with all of those. Putting in "on error resume next"
hasn't helped either, because the error happens before it inserts the
formula.

I know I can write a macro to calculate the StDev of a previously
selected range and put the answer below, and I have done that, but now
this is the behavior I want. It is for students, and I want them to see
the formula and how it works.

Any help would be greatly appreciated.

 

[acampbell012]

Try activecell.value = "'=stdev()"

 

[freddy007]

Thanks. I tried that too, but it just makes the cell be text, so it
doesn't end up in "Ready to accept selected range mode."
Is there some method I can use that puts it in that mode?

 

[NickHK]

Range("A1").Select
SendKeys "{F2}"
SendKeys "=stdev()"

NickHK

 

[John Welch]

Thanks Nick, but it doesn't work for me. The parentheses don't get typed by
the sendkeys command or some reason, so I just end up with =sendkeys in the
cell, and when I go to select a range, I get a #name in A1.

I also tried the shortcut keys ctl-a and ctl+shift+a. I got close with
ctl+shift+a, but it brings up a dialog box that gets in the way. So I'm
still trying. Any more ideas anyone?

 

[NickHK]

John,
Try this. But bear in mind Excel is not designed to work this way ; running
code when in edit mode. So you are limited in what can be achieved.

Range("A1").Select
SendKeys "{F2}"
SendKeys "=stdev+9+0"
SendKeys "{LEFT}"

NickHK

 

[John Welch]

Nick, thanks for trying again-
this time it works great. I left out the first line and just did this:

SendKeys "{F2}"
SendKeys "=stdev+9+0{LEFT}"

and it does exactly what I want it to do. I wanted a macro that will save
typing but at the same time teach students how to use excel when they don't
have this macro available.
I really appreciate the help.
-John