Multicoloured Databars Excel addin

I’d been happily using my wonderful little form for a few months now to colourify my data and thinking about how to improve it. Then a couple of weeks ago I discovered some major bugs with the whole routine when I switched language in my trilingual Office2010 installation (Englishman living in Italy working for Swiss Germans). Basically unless you run Office in English but with European list separators, the previous version just didn’t work.

The essential element in the code is the formula hidden behind each condition that sets the value range where a specific colour will be used.
.Formula = “=IF(” & temp & “<” & Controls(“C” & n & “to”).Value + 1 & “;True;False)”
This code took into account the variable range and numerical values BUT ignored the language.

Firstly, I neglected the fact that the formula used “IF”, “True” and “False”. This meant that in any language other than English the formula didn’t work so the multi-colouration didn’t work either.
Secondly, my local setting has “;” as the list separator so in any country where this is not the case (even if the language setting is English), again the formula just wouldn’t work.
I don’t know how many people who downloaded the file ever managed to use the form. Nobody mentioned anything here…

So I’ve spent 2 weekends and some evenings fixing the code. The solution was actually very simple.
Even if you’re working in Excel in French or Chinese, Visual Basic is still English-speaking. You get a friendly, localised UI but VBA is from Redmond (well, Albuquerque originally). The clever part is that Excel and VBA generally do a pretty good job of simultaneously translating*. The VBA code in English
ActiveSheet.Range(“A1”).Formula = “=IF(A2=””””,1 ,2)”
puts an IF formula in the sheet in your local language. All I had to do was read it back in to VBA as text and put that real-time translation into a string called LocalIF. I got LocalTRUE and LocalFALSE in the same way.
The list separator was even easier as you can get that with Application.International(xlListSeparator) which I saved as ILS.
So the formula became
.Formula = “=” & LocalIF & “(” & temp & “<” & Controls(“C” & N & “to”).Value + 1 & ILS & LocalTRUE & ILS & LocalFALSE & “)”
By the way, to be sure that the code doesn’t mess up any data in the user’s sheet I add a new sheet first, get the translations, then delete it (all with ScreenUpdating and DisplayAlerts temporarily turned off).
I’ve tested it on various combinations of Windows and Excel in English, Italian, and German so I’m fairly confident that this time it will work in any country and in any language.

Now to the additions. I thought that special formatting for the maximum value in the range would be good (in the end minimum too or both with separately formatting). I had actually half-solved the way to do this already. All I needed to do was get LocalMIN and LocalMAX using the technique I’d learned above and construct different formulas.

I also stuck in a CheckBox to invert the direction of all the bars. With two adjacent columns of data you can do this:

I’m wondering if an “Invert” CheckBox for each individual range would be useful. At the moment I think not but if someone can suggest a possible use for a chart like this I could implement it later…

The other big change is that I’ve made it into a proper add-in rather than code that has to be stuck somewhere.

Download the Excel AddIn “Multicoloured DataBars“. 9th April 2017: updated Dropbox link

Please try it out and please let me know in the comments if it doesn’t work or you have suggestions.

Playing around, this was an interesting result:

All the data has positive values but I set the conditional formatting minimum to -10. Format text allignment hard left and your data bars start to really look like a serious chart.

 

 

*Let’s not mention date formats.