Python XlsxWriter - VBA Macro

In Excel, a macro is a recorded series of steps that can be repeated any number of times with a shortcut key. The steps performed while recording the macro are translated into programming instructions VBA which stands for Visual Basic for Applications. VBA is a subset of Visual basic language, especially written to automate the tasks in MS Office apps such as Word, Excel, PowerPoint etc.

The option to record a macro is available in the Developer menu of MS Excel. If this menu is not seen, it has to be activated by going to the "File→Options→Customize" ribbon screen.

As shown in the following figure, click the Record Macro button by going to "View→Macros→Record Macro", and give a suitable name to the macro and perform desired actions to be recorded. After the steps are over stop the recording. Assign a desired shortcut so that the recorded action can be repeated as and it is pressed.

To view the VBA code, edit the macro by going View→ZMacros→View Macros. Select the Macro from Macro name and click on Edit.

The VBA editor will be shown. Delete all the steps generated by Excel and add the statement to pop-up a message box.

Confirm that the macro works perfectly. Press CTL+Shift+M and the message box pops up. Save this file with the .xlsm extension. It internally contains vbaproject.bin, a binary OLE COM container. To extract it from the Excel macro file, use the vba_extract.py utility.

(xlsxenv) E:\xlsxenv>vba_extract.py test.xlsm
Extracted: vbaProject.bin

Example

This vbaProject.bin file can now be added to the XlsxWriter workbook using the add_vba_project() method. On this worksheet, place a button object at B3 cell, and link it to the macro that we had already created (i.e., macro1)

import xlsxwriter

workbook = xlsxwriter.Workbook('testvba.xlsm')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 30)
workbook.add_vba_project('./vbaProject.bin')
worksheet.write('A3', 'Press the button to say Welcome.')
worksheet.insert_button(
   'B3',
   {
      'macro': 'macro1',
      'caption': 'Press Me',
      'width': 80, 'height': 30
   }
)
workbook.close()

Output

When the above code is executed, the macro enabled workbook named testvba.xlsm will be created. Open it and click on the button. It will cause the message box to pop up as shown.