macro to insert function without parameters
 

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.

macro to insert function without parameters
 

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


freddy007 wrote:
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.

macro to insert function without parameters
 

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?

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


freddy007 wrote:
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.

macro to insert function without parameters
 

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

NickHK

"freddy007" wrote in message
ps.com...
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.

macro to insert function without parameters
 

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" wrote in message
...
Range("A1").Select
SendKeys "{F2}"
SendKeys "=stdev()"

NickHK

"freddy007" wrote in message
ps.com...
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.

macro to insert function without parameters
 

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" wrote in message
...
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" wrote in message
...
Range("A1").Select
SendKeys "{F2}"
SendKeys "=stdev()"

NickHK

"freddy007" wrote in message
ps.com...
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.

macro to insert function without parameters
 

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



NickHK

"John Welch" wrote in message
...
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" wrote in message
...
Range("A1").Select
SendKeys "{F2}"
SendKeys "=stdev()"

NickHK

"freddy007" wrote in message
ps.com...
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.