Loading a bitmap into a userform in Excel for Mac at runtime
While converting my own software to work in Excel, it appeared that the best user interaction experience would be to allow the users to modify a chart in a userform (the required interaction made Excel’s chart object just too clunky). However, the only way to get a copy of a ‘live’ chart from a worksheet is to save an image of it in a file and then load it into a userform. It is here that a teeny-weeny bit of a problem appeared: the LoadPicture command was not available for the Mac.
AAAAAARRRRRRGGGGGGHHHHHH!!!!!!
So far, this is the experience of every 'Excel for Mac' programmer - but not any more! I had a brainwave: a userform can be loaded in at runtime (and the user does not need to give permission to use the VBE object on the Mac). So all I had to do was to try to create the ‘.frx’ code file myself. It is here that another little snag appeared: Large pictures are split up after 65536 bytes, and in-between these blocks appear letters of the alphabet in order (it's true - have a look! I used Hex Editor Neo to play around with the bmp and frx files). So I gave up trying to work out the frx file format and instead decided to embed a smaller bitmap into a larger one, and then replace the frx's bitmap with our newer one of the same size. Clunky, far from perfect, but it worked!
The next problem was speed: it could take almost half a second to load in a large chart. This is due to the time taken to load and open a userform from a file at runtime. However, if the bitmap was reduced from 24 bits to 256 colours then it was about twice as fast. Some colour loss seems okay for charts and graphs. (By default, it appears that Office for the Mac saves bitmap images as 32 bit which cannot be loaded into the userform even at design time. The program converts these to 24 bit. If there was a setting to save them as 24 bit bitmaps then it could make a big difference, although the existing routine to embed 24 bit bitmaps into a larger bitmap would need to be speeded up.)
AAAAAARRRRRRGGGGGGHHHHHH!!!!!!
So far, this is the experience of every 'Excel for Mac' programmer - but not any more! I had a brainwave: a userform can be loaded in at runtime (and the user does not need to give permission to use the VBE object on the Mac). So all I had to do was to try to create the ‘.frx’ code file myself. It is here that another little snag appeared: Large pictures are split up after 65536 bytes, and in-between these blocks appear letters of the alphabet in order (it's true - have a look! I used Hex Editor Neo to play around with the bmp and frx files). So I gave up trying to work out the frx file format and instead decided to embed a smaller bitmap into a larger one, and then replace the frx's bitmap with our newer one of the same size. Clunky, far from perfect, but it worked!
The next problem was speed: it could take almost half a second to load in a large chart. This is due to the time taken to load and open a userform from a file at runtime. However, if the bitmap was reduced from 24 bits to 256 colours then it was about twice as fast. Some colour loss seems okay for charts and graphs. (By default, it appears that Office for the Mac saves bitmap images as 32 bit which cannot be loaded into the userform even at design time. The program converts these to 24 bit. If there was a setting to save them as 24 bit bitmaps then it could make a big difference, although the existing routine to embed 24 bit bitmaps into a larger bitmap would need to be speeded up.)
Files and documentation
The 'LoadPicture for Mac' Excel add-in is gladly made available to you as freeware and open source under the GNU General Public License, version 3 (GPL-3.0). I have been greatly confused by such licences but this seemed the best; I gave up trying to have a licence for the documentation so the documentation files are hereby made public domain. (If you need a different licence then please do contact me.) The add-in was uploaded on 17 June 2016. Load AALoadPictureMac.xlam before opening TestLoadPicMac.xlsm. The add-in password is 'qw'; it is passworded so that it is not opened accidentally.
loadpicformac.zip | |
File Size: | 971 kb |
File Type: | zip |
This add-in took a lot of time to produce. If you appreciate it then please support me financially by clicking on one of the buttons below.
Help with API code - advanced users only
This will crash Excel for Mac 2016
After writing the above code I had an idea: surely Microsoft would not 're-invent the wheel' when getting Office to work on a Mac but would wrap the Windows source code around some translation thingy. I then looked for and found the Windows APIs. I was able to get a 32/24-bit bitmap file loaded into a userform at runtime in only 0.05s in Excel for the Mac 2011. However, when I tried it about a month ago in Excel for Mac 2016 it crashed. Any ideas to get it working? Password 'qw' again.
loadpicmacapinotwork2016.zip | |
File Size: | 218 kb |
File Type: | zip |