VBA – Drawing objects with VBA in Excel

WelcomeToVitoshAcademy

How much time do you need to build something like this in Excel?

At least 20 minutes + a lot of lost nerves for the formatting. Unless you use VBA and you build it within some seconds 🙂

Pretty much the code is quite easy to be understood and modified – what is interesting is the name of the shape and the randomizing function. The name of the shape is taken from the Excel Macro Recorder and in my case I use two forms – msoShapeDownRibbon and msoShapeUpRibbon. The randomizing function is pretty obvious and can be reused in any project.

Something else, quite useful for working with forms – calculating their x and y position. In my case the y is standard, as all the shapes are in line and the x is calculated with the formula x = n * i / k, where n is a constant for the size of the shape, i is the position of the character in the string and k is the total characters of the string. To convert this numbers to points from inches (as far as Excel uses this metric system), we use the build-in function InchesToPoints – press F2 in VBE and write “InchesToPoints” for more information:

inchesToPoints

 

Last but not least, we use module calculation in order to select which shape to use – if i mod 2  returns 1, we use the msoShapeUpRibbon, and if it returns 0 we use the msoShapeDownRibbon. And it works! 🙂

Enjoy it!

About

VBA Developer

Tagged with: ,