Help with SendKeys

[Yukio]

Hello everyone,

I'm having trouble with SendKeys... I'm an amateur VBArtist but I'm trying to learn.
I've got these two sorted out:


Sub ConvertCalc()
Range("A1").Value = "=" & Range("A1").Value
ActiveSheet.Calculate
End Sub

SendKeys "^+{A}"
DoEvents

How do I "combine" them so that COUNT, in the A1 cell becomes =COUNT(value1,value2,...).
The (value1,value2,...) part should come from the shortcut CTRL+SHIFT+A after =COUNT has been recognized.

I would also add after all that a '-symbol in front of the whole =COUNT(value1,value2,...) part so that everyone can see the "naked" function.

If you need an Excel example, please say so.

Thank you in advance,
Robert

[enrand22]

hi!
for formula arrays you can use in vba something like this


Range("a1").FormulaArray = "=count(b1:b2)"

this is the same to key ctrl shift A

[mdmackillop]

I'm not clear so an example would help, or do you just need

Range("A1").Value = "'=" & Range("A1").Value

[Paul_Hossler]

Learning good

1. What are you trying to do? SendKeys is IMHO rarely ever needed.

2. The way I show the naked formula


Option Explicit
Function ShowTheFormula(r As Range) As String
ShowTheFormula = "'" & r.Cells(1, 1).Formula
End Function


Paul

[Yukio]

I not only want to show the naked formula, but to also show the result of the CTRL+SHIFT+A after the formula, hence the SendKeys.

I've attached an example of what I'd like it to do.

Thanks in advance!

[Yukio]

Anyone care to help? :)

[sassora]

Anyone care to help? :)

Are you trying to create a list of functions with their general parameters?

Is this for instructional purposes?

[Yukio]

Are you trying to create a list of functions with their general parameters?

Is this for instructional purposes?

Yes.

[sassora]

This seems to do the trick the first time. After that it breaks - any text typed into the cell has an apostrophe


Sheets("Sheet1").Range("A1").Select
SendKeys "{F2}"
SendKeys "{(}"
SendKeys "{HOME}"
SendKeys "{=}"
SendKeys "{END}"
SendKeys "^+A"
SendKeys "{HOME}"
SendKeys "{'}"
SendKeys "{ENTER}"

[sassora]

This is a way around the issue. I should note that SendKeys are known not to be reliable.

To execute the code you'll need to do so from the Excel side rather than the VBA Editor side.
Either use the macros dialog or create your own button.


Dim Val As String
Sheets("Sheet1").Range("A1").Select
Val = Selection.Value
Selection.Clear
Selection = Val
SendKeys "{F2}"
SendKeys "{(}"
SendKeys "{HOME}"
SendKeys "{=}"
SendKeys "{END}"
SendKeys "^+A"
SendKeys "{HOME}"
SendKeys "{'}"
SendKeys "{ENTER}"