I recently released a macro to help you back up your macros, MacroBackerUpper. This was one of my most-tested macros ever, as between myself and three testers we had put in well over 3,000 uses of the macro.
And yet, on release day, several people complained that it just didn't work. After much digging and with great help from the affected users, I figured out the problem: The routine I use to time tasks within the macro was causing it to fail.
How could that be, though, given we'd done so much testing? Because all of the testers' systems use the "0,000.00" number format. Those having trouble use the "0.000,00" format. But why was this killing the macro?
Because of the "With Format" box on a Calculation action:
Going in, my understanding of the With Format box was that it was just that: An option to format the results of the calculation. The help page for the Calculation action seems to say just that, too:
As can be seen by the blue shade of the action gear menu there is an additional option for the action, specifically you can Format the Result using Unicode Number Format Patterns such as #,##0.## .
Note: Peter has now added a note below this block, explaining the limitation on usage of the With Format box—thanks, Peter!
But what I failed to grasp is that while yes, this formats the result, it also changes the number itself by embedding the applied formatting. So further on in my macro, when I went to use rg_MT_netTimer again, instead of being (for example) the real number 23.22525, it would be 23[decimal separator character as set by the OS]22525.
In the USA and many other countries, that's fine—the decimal separator character is the period, so the number is still a decimal. So we merrily tested and updated and retested, and everything was fine. But everything wasn't really fine…
Quite a few countries use the comma as their decimal separator. So instead of trying to do math with 23.22525, the macro would try to do math with 23,22525 and fail, because commas aren't allowed in values in calculations (but periods/decimals are, obviously, to show non-integer values).
And that's how you release what you think is a well-tested macro and have it fail miserably on day one.
You can try this for yourself with this very simple test macro:
────────────────────────────
'With Format' example.kmmacros (6.1 KB)
────────────────────────────
If you run that macro with your System Settings > Language & Region > Number format set to 1,234,567.89, it will work just fine. If you change the number format to 1.234.567,89 it will fail:
2023-11-09 10:39:29 Set Variable to Calculation failed to evaluate local_task_2_End-local_task_1_time-local_startTimer. Macro “_Calc formatting” cancelled (while executing Set Variable “local_task_2_time” To Calculation “local_task_2_End-local_task_1_time-local_startTime…”).
It fails because the local_task_2_time calculation is trying to use local_task_1_time, which has been formatted as 0,00 based on the number format settings.
So what's the solution to the problem when you need to both format a number for display and also keep using it in calculations? The only one I thought of is this one:
I make a copy of the calculated value and set its format, not that of the original. I can then use the 'display' variable where I need the formatted number, and keep the original 'calculate' value clean for future use.
Hopefully my little exercise in frustration saves someone else some time in the future.
-rob.