0

Is there a way to run a macro while you are editing a cell in the formula bar? I didn't find anything online, unfortunately.

Example:
Current formula: =4×5+4 returns 24
I want to put brackets around 5+4, so the formula changes to: =4×(5+4) returns 36.
Instead of running the combo:
SELECT 5+4 ►► CTRL+X ►► Keystroke ( ►► CTRL+V ►► Keystroke )
I want to just click a key combo like ALT+Q+E (pure example off the top of my head).

Is there a way to do this?

Spurious
  • 1,903
  • 5
  • 27
  • 53
  • 1
    That seems like an XY problem. Why would you want to do this instead of while selecting a cell? I can't possible think of a use-case for this, but I've had some weird stuff myself. – pgSystemTester May 26 '18 at 22:54
  • @Spurious - This is an odd question. Why don't you change the formula directly in the cell instead of messing around with the formula bar? ie., `Range("D1").Formula = "=4*(5+4)"` ... But besides that, **how often** are you putting parentheses around `5x4`'s, that it justifies a shortcut? What are you *actually* trying to accomplish? There's probably an easier way. – ashleedawg May 27 '18 at 01:26
  • So, one specific use case for example: I have an Excel sheet where I track my daily credit card charges and clear them off once they're properly added to my monthly credit card balance. I separate them by days because it makes clearing off easier (most charges are all swept t+1). I get the Excel sheet from my bank and have all the values stored beneath each other. I have a separate shortcut to concat the cell values by "+" (or any other character). I then want to separate the days by parentheses. But I have a ton of use cases where I want to do this. Parentheses are underrated - it seems. – Spurious May 27 '18 at 13:57

1 Answers1

2

You can't run the macro in Edit mode, but you can achieve the same result:

Before:

enter image description here

The macro:

Sub EditFormula()
    With ActiveCell
        .Formula = Mid(.Formula, 1, 3) & "(" & Mid(.Formula, 4) & ")"
    End With
End Sub

The result:

enter image description here

(you may also be able to do some simple editing with SendKeys)

Sub Try_SendKeys()
    With Application
        .SendKeys "{F2}"
        .SendKeys "{LEFT}"
        .SendKeys "{LEFT}"
        .SendKeys "{LEFT}"
        .SendKeys "+9"
        .SendKeys "{RIGHT}"
        .SendKeys "{RIGHT}"
        .SendKeys "{RIGHT}"
        .SendKeys "+0"
        .SendKeys "{ENTER}"
        DoEvents
    End With
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    The `SendKeys` aspect is helpful. The formula via macro is not a useful way of going about the world. Writing macros for every single use case is not good programming, but you are probably aware and were just helping out for that specific aspect. – Spurious May 27 '18 at 13:56
  • @Spurious I agree with you. – Gary's Student May 27 '18 at 13:59