Open Excel
Blank Workbook
File > Options
Customise the Ribbon
Main Tab > Developer > OK
Developer
Visual Basic
Insert
Module
Sub sbHelloWorld()
MsgBox "HelloWorld"
End Sub
Run > Reset
File > Save
Filename: xl_HW
Save as type: Excel Macro-Enabled Workbook (*.xlsm)
Save
Run > Run Sub
As at 10:16 30th December 2022 Microsoft 365 Excel Version 2211 (Build 15831.20208 Click-to-Run)
=====
To add a button which calls the subroutine
Developer
Insert
Button (not Active X)
-> cross hairs
locate in the chosen cell
Assign macro
sbHelloWorld
Press the new button
Hello World message is displayed
OK
=====
Add a shortcut key sequence to execute the subroutine
Developer
Macros
Macro name
sbHelloWorld()
Macro Options
Shortcut key
"K"
OK
Cancel
Ctrl-Shift-K
The macro runs
OK
=====
Add a button on the Quick Access Toolbar
Show the ribbon
ctrl-F1
Show the Quick Access Toolbar
Ribbon Display Options (located in the lower-right below the ribbon)
Show Quick Access Toolbar
File
Options
Quick Access Toolbar
Choose commands from:
Macros
sbHelloWorld()
Add
OK
Press the QAT button
The macro runs
Message displayed - "Hello World"
OK
=====
Simple using the Macro Recorder
Blank Workbook
Developer > Record Macro > OK
Enter "Hello" in A1
Enter "World" in A2
Developer > Stop Recording
Developer > Visual Basic > Expand + Modules
double click Module1
macro generated
Sub Macro1()
ActiveCell.FormulaR1C1 = "Hello"
Range("A2").Select
ActiveCell.FormulaR1C1 = "World"
Range("A3").Select
End Sub
=====
Turn on option explicit by default for all VBA Code modules
Developer
Visual Basic
Insert
Module
Tools
Options
Require variable declaration
Tick the box
OK
=====
Call Excel from Python
import time
import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Open(Filename=r"C:\Users\david\Desktop\xl_HW.xlsm",ReadOnly=1)
xl.Application.Run("sbHelloWorld")
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0
time.sleep(10)
Windows 22H2 Build 22621.1194 64 bit - Python 3.11.2 - Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20186) 64-bit
I needed python -m pip install pywin32 for the win32com.client