Multicoloured Databars Excel addin

image

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.

image

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

image

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…

image

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:

image

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.

Annunci

Informazioni su DiGiMac

I'm an ex-pat Londoner. I've been living near Lake Garda in North Italy for more than 30 years.
Questa voce è stata pubblicata in English, Excel, Multicoloured DataBars. Contrassegna il permalink.

16 risposte a Multicoloured Databars Excel addin

  1. Pingback: Multicoloured Data Bars in Excel | a curious game

  2. William Ripley ha detto:

    Thanks for the new update! Anniversary weekend so will be out of town, but will try it this week at work and will let you know how it goes. Thank’s again.

  3. kdavis ha detto:

    I get a runtime error when I try to use the addin any suggestions?

  4. Michael ha detto:

    I love the add-in. I was searching for the VB code I’de added to the end of a macro which created the data bars and set the condition to on or off based off of a condition. I couldn’t find that anywhere in my macro files or on the internet but I did find this and it’s very nicely constructed. My only suggestion would be to add more functionality in to allow for “>”, “=”, etc.

  5. Nate Fowkes ha detto:

    It works perfectly if I’m using integers, but when I’m working with a percentage range (i.e., spread from 0 to 1), it uses only the first color range. Am I using the add-in incorrectly, or will the current build simply not work with percentages?

  6. Aless ha detto:

    What’s the VBA password?

  7. Aless ha detto:

    I would like to make it work for Mac as well. It would be great to get the password for the VBA in order to adjust it a bit!

    Thanks a lot

  8. Aless ha detto:

    I tried it on a Windows machine, on Excel 2013 – the AddIn opens as expected and also the color picker works, but when applying it only takes the color of the first value and doesn’t consider the rest

  9. Koen ha detto:

    This add-on looks great, thanks for sharing it! Unfortunately I get a 404 error when trying to download it, could you maybe reupload the .zip?

  10. GLayne ha detto:

    Hi,

    I would love to get the addon as well but the download link is dead. Could the file be re-hosted?

    Thank you very much!

  11. BobM ha detto:

    Hi,
    Is there any chance you could also this somewhere corporate IT doesn’t block please (ie not on major filesharing sites)?
    Or email me a copy directly?
    Thanks.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...