How can you improve your VBA skills? Build something! Anything! Even if it’s ridiculous. I needed to practice and ended up creating a robot and a bunch of random style hearts! 🙂

Robot Creates Hearts?

Silly but a great way to practice VBA. Once I started playing around my curiosity took over. I tinkered around for a few hours while listening to music.

Disclaimer

Not recommended as a Valentine’s Day gift unless he/she loves spreadsheets and/or programming.

Excel File!

Download my Excel file and click the robot to add a heart! Click it again!

Macro Recorder

I used the macro recorder to create basic code for:

• moving the robot
• adding a heart
• rotating a heart
• varying the heart color

Macro Recorder to VBA

I modified macro recorder code to create more powerful VBA. Examples:

Adding A Heart

The macro recorder created this:

Sub TESTcreateHeart()
‘ TESTcreateHeart Macro
‘
ActiveSheet.Shapes.AddShape(msoShapeHeart, 847.5, 168.6, 72, 72).Select
End Sub

Changing the numbers inside the brackets revealed the purpose of each one.

• 847.5 lateral position
• 168.6 vertical position
• 72 width
• 72 height

I wanted to randomize the heart location and size. I added some variables (all numbers).

Dim lateral, vertical, heartsize As Integer

Next was assigning the random numbers to each variable. Heartsize is used for width & height.

lateral = (Rnd() * 500) + 70
vertical = (Rnd() * 175) + 45
heartsize = Rnd() * 45

Now the variable names replace the original hard-coded numbers:

ActiveSheet.Shapes.AddShape(msoShapeHeart, lateral, vertical, heartsize, heartsize).Select

Heart Color & Transparency

The macro recorder created this:

Sub TEST_Heart_Color_and_Transparency()
‘ TEST_Heart_Color_and_Transparency Macro

ActiveSheet.Shapes.Range(Array(“Heart 2”)).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0.6999999881
.Solid
End With
End Sub

I created variables redc and transp and assigned values to them.

transp = Rnd()
redc = WorksheetFunction.RandBetween(150, 255)

I also added a random tilt for the heart and code to create 3D hearts.

Move the Robot

The main macro is Create_A_Heart found in module A_CreateHeart. Many actions are inside this macro but I also used the Call procedure to run macros in a separate vba modules. Example: move the robot let and right.

Call E_MoveRobot

This macro gets the variable value from individual cells (named ranges) in sheet Hearts. You can modify the numbers in column D!

I used named ranges instead of hard coded cell references in case I added rows or columns later on.

Sub E_MoveRobot()
Dim movebot, movebotleft, movebotright As Integer

movebotleft = Range(“Move_robot_left_amount”).Value
movebotright = Range(“Move_robot_right_amount”).Value
movebot = WorksheetFunction.RandBetween(movebotleft, movebotright)

ActiveSheet.Shapes.Range(Array(“RedRobot”)).Select
Selection.ShapeRange.IncrementLeft movebot

Range(“MoveRobotValue”).Value = movebot

End Sub

Heart Stats

Finally, I added code to assign each heart’s variable values to cells in column J. Why? Why not.

Range(“HeartCount”).Value = Range(“HeartCount”).Value + 1
Range(“HorizontalPosition”).Value = lateral
Range(“VerticalPosition”).Value = vertical
Range(“HeartSize”).Value = heartsize
Range(“MoveRobotTotal”).Value = Range(“MoveRobotValue”).Value + Range(“MoveRobotTotal”).Value

Syntax Help

I found these sites helpful for my VBA syntax questions:

• docs.microsoft.com/en-us/office/vba/api/excel.shapes.addshape
• docs.microsoft.com/en-us/office/vba/api/excel.shaperange.incrementrotation
• powerspreadsheets.com/excel-vba-range-object/
• excelmacromastery.com/excel-vba-range-cells/
• thespreadsheetguru.com/blog/how-to-keep-track-of-your-shapes-created-with-vba-code

About Me

My name is Kevin Lehrbass. I’m a Data Analyst.

I can still remember when I first discovered VBA in Excel. Life altering!!

These days I don’t use vba regularly but I try to keep my skills alive by building things.