Hello, I’ve been trying to use chat gpt to craft some excel specific macros without much luck.
i want to hit fn2 to pull up my temporary fn2 layer, and then hit what is in the position of the 4/$ key, and I want it to execute the following command:
Ctrl + 1
N
tab
CUS
Alt + T
$#,##0_);($#,##0);-;@
Enter
Can’t seem to get this to work with chat gpt’s help. Any recommendations?
What is your system keyboard layout? enUs?
If so, something like
tapKeySeq LC-1 N tab <what the hell is a CUS key?> LALS-t
write '$#,##0_);($#,##0);-;@'
tapKey enter
I don’t know my way around Excel, but I think they’re trying to set up a macro to navigate formatting presets. So I think the N is meant to be typed text to bring up the “Number” tab, and CUS text to bring up the “Custom” preset from the drop-down menu.
I took this screenshot in the free online Excel app:
The shortcut keys seem to be different in the online version, so Idk exactly what sequence works properly in the desktop version. Using online version, I need to use Tab or arrow keys to navigate, because typing to select “Number” section and “Custom” preset doesn’t work.
I see…
Pcooke9 was right on the money. I was ultimately able to get it to work by just iterating at each step to see how I had to modify the macro vs. what chatgpt was telling me would work. Pasting the below code brick to illustrate in case anybody else every runs into the same issue.
BlockquotetapKeySeq C-1
delayUntil 140
tapKey n
delayUntil 120
tapKey tab
delayUntil 120
tapKeySeq A-c
delayUntil 100
tapKey c
delayUntil 70
tapKey u
delayUntil 70
tapKey s
delayUntil 120
tapKeySeq A-t
delayUntil 140
tapKeySeq S-4
delayUntil 80
write “#,##0_);($#,##0);-;@”
delayUntil 120
tapKey enter
Blockquote
Well, that’s scary.
haha it saves a bunch of time. I’ve setup this, as well as a bunch of other formatting macros. There’s excel add ons to do this, but this method allows you to keep the macros on the keyboard and that way you can plug in to any computer regardless of whether or not macros are installed and just get straight to it
Yeah, that’s one of the wildest macros I’ve seen so far🤪.
You might be able to clean that up and hopefully eliminate all those delayUntil lines by setting your keystroke delay to 10ms or more. (Agent’s left-panel—> Typing behavior—> Keystroke delay = 10ms). In Windows, I have to keep mine set to at least 10ms because some apps can’t keep up with the UHK’s output speed, and it can produce jumbled/missing characters etc..
If you haven’t already increased the keystroke delay, try doing so, and then replace that unwieldy macro with the following:
tapKeySeq LC-1 n tab LA-c c u s LA-t
write '$#,##0_);($#,##0);-;@'
tapKey enter
I’m a little unsure about the LA-c c u s part. (compare to yours):
tapKeySeq A-c
delayUntil 100
tapKey c
delayUntil 70
tapKey u
delayUntil 70
tapKey s
and the
tapKeySeq S-4
delayUntil 80
write “#,##0_);($#,##0);-;@”
I’m not sure why ChatGPT structured it that way, but if it worked in the macro you posted, then my shortened macro should work similarly. I don’t have a full version of Excel to test it, but I think it’s correct.
If the output is still too fast to open/navigate the menus in time, then I guess your Franken-macro will have to do🙃. Cleaning everything up should save you some on-board storage, and of course will be a lot easier/faster to write, especially if you have a lot of them.
I had to break it out like that wrt to the CUS because it wasn’t registering, but it could have been a key stroke delay thing. I appreciate the input! Let me give that a try and will report back if that approach works.
The thing about this and a lot of other excel shortcuts is that they use the “ribbon” up top, which has a delay, so you often have to bake the delay in, but a global key delay seems like a more elegant solution to your point.
Does the delay add to all keys, or just macro keys?
Edit: your approach worked!! Thank you!
The keystroke delay makes sure that between any two usb report changes, there is at least x ms delay.
I.e., it affects all keys, not just macro ones, but in practice keys register immediately. Only if you hit two keys within x ms of each other, then the second one will be delayed.
Thanks, all this was very helpful. With the more efficient code, I was actually able to get it to work even without the keystroke delay. I went back and redid all my macros with the one shared above as a precedent.