After my presentation of Excel macros at BarCamp Kuwait a friend sent me this link (http://imgclean.com/?p=2149) on Twitter and said that this was much better than my excel macro. I told him challenge accepted and spent a week and a half trying to figure out how I’m going to to what I had in mind. The Excel macro I had in mind would let you choose an image, it will import it, read pixels and use the colours to colour excel cells & make an image out of them.
At the end, the program works as follows:
- Open a UserForm to enable user to import an image.
- Image Control in the UserForm will zoom the image so it all shows inside the Image Control.
- Handle the UserForm and use GetPixel function to get the pixel colours as “Long” integers.
- A function will extract individual RGB colours.
- A new WorkBook is opened, rows/cols will be re-sized and Sheet is zoomed out.
- Cells will be coloured with the previously extracted RGB values.
Problems encountered:
- You cannot draw an image twice in one Excel sheet. It seems that Excel runs in an issue with large number of formatted cells. That’s why I’m using a new Work Book each time, it’s actually good too so you can save it for the image only.
- VB has a Picture Box Control which is not available in VBA, Excel has only an Image Control which doesn’t have all the functions of a Picture Box. I’m not an expert on this since it’s the first time I use Forms and Controls so I cannot tell the difference. All what I can say is after hundreds of searches online many people were looking for Picture Box Control in Excel but it’s simply not there. I had to use Image Control and create a handle for the UserForm, declare GetPixel function, import libraries and then use the GetPixel to get all the pixel colours for the image.
- RGB colour conversion used to over-flow so the negative numbers were set to 0 and numbers above 255 are set to 255. Now one might say when it goes negative it should be set to 255 because it over-flows. I’m not sure I just got bored of all the searches I’ve done so I don’t really care how they work. Yes not ALL pixels are going to be perfect but hey its giving great results!
- When using the GetPixel function with loop I used to get part of the image while all other pixels give “-1” value. After long debugging I discovered that when Excel goes into a loop and for long time it gives a “Not Responding” message then starts being responsive after it finishes. This causes the GetPixel to get bad pixels from handle of the UserForm. A work around would be a small 10 ms delay in the loop or a progress bar box which was done using UserForm.
This program is not written to be perfect it has bugs and it definitely could be better.
Images:
- Excel Art Loading Image
Excel Art Result
Excel Art Result Zoomed
