Everything you can write in VBA can be done in Python. This page contains information that will help you translate your VBA code into Python.
Please note that the Excel Object Model is part of Excel and documented by Microsoft. The classes and methods from that API used in this documentation are not part of PyXLL, and so please refer to the Excel Object Model documentation for more details about their use.
See also Macro Functions.
When programming in VBA you interact with the Excel Object Model. For example, when writing
Sub Macro1()
Range("B11:K11").Select
EndSub
what you are doing is constructing a Range object and calling the Select method on it. The Range object is part of the Excel Object Model.
Most of what people talk about in reference to VBA in Excel is actually the Excel Object Model, rather than the VBA language itself. Once you understand how to interact with the Excel Object Model from Python then replacing your VBA code with Python code becomes straightforward.
The Excel Object Model is well documented by Microsoft as part of the Office VBA Reference.
The first hurdle people often face when starting to write Excel macros in Python is finding documentation for the Excel Python classes. Once you realise that the Object Model is the same across Python and VBA you will see that the classes documented in the Office VBA Reference are the exact same classes that you use from Python, and so you can use the same documentation even though the example code may be written in VBA.
The Excel Object Model is made available to all languages using COM. Python has a couple of packages that make calling COM interfaces very easy. If you know nothing about COM then there’s no need to worry as you don’t need to in order to call the Excel COM API from Python.
The top-level object in the Excel Object Model is the Application object. This represents the Excel application, and all other objects are accessed via this object.
PyXLL provides a helper function, xl_app, for retrieving the Excel Application object. By default,
it uses the Python package win32com, which is part of the pywin32 package [1].
If you don’t already have the pywin32 package installed you can do so using pip:
pip install pywin32
Or if you are using Anaconda you can use conda:
conda install pywin32
You can use xl_app to access the Excel
Application object from an Excel macro.
The following example shows how to re-write the Macro1 VBA code sample from the section above.
Note that in VBA there is an implicit object, which related to where the VBA Sub (macro) was written.
Commonly, VBA code is written directly on a sheet, and the sheet is implied in various calls.
In the Macro1 example above, the Range is actually a method
on the sheet that macro was written on. In Python, we need to explicitly get the current active sheet instead.
from pyxll import xl_macro, xl_app
@xl_macro
def macro1():
xl = xl_app()
# 'xl' is an instance of the Excel.Application object
# Get the current ActiveSheet (same as in VBA)
sheet = xl.ActiveSheet
# Call the 'Range' method on the Sheet
xl_range = sheet.Range('B11:K11')
# Call the 'Select' method on the Range.
# Note the parentheses which are not required in VBA but are in Python.
xl_range.Select()
You can call into Excel using the Excel Object Model from macros and menu functions, and use a sub-set of the Excel functionality from worksheet functions, where more care must be taken because the functions are called during Excel’s calculation process.
You can remove these restrictions by calling the PyXLL schedule_call
function to schedule a Python function to be called in a way that lets you
use the Excel Object Model safely. For example, it’s not possible to update
worksheet cell values from a worksheet function, but it is possible to
schedule a call using schedule_call and have that call update the
worksheet after Excel has finished calculating.
For testing, it can also be helpful to call into Excel from a Python prompt
(or a Jupyter notebook). This can also be done using xl_app, and
in that case the first open Excel instance found will be returned.
You might try this using win32com directly rather than
xl_app. We do not advise this when calling your Python code from
Excel however, as it may return an Excel instance other than the one you
expect.
from win32com.client.gencache import EnsureDispatch
# Get the first open Excel.Application found, or launch a new one
xl = EnsureDispatch('Excel.Application')
Python is case sensitive. This means that code fragments like r.Value and r.value are different (note the
capital V in the first case. In VBA they would be treated the same, but in Python you have to pay attention
to the case you use in your code.
If something is not working as expected, check the PyXLL log file. Any uncaught exceptions will be logged
there, and if you have attempted to access a property using the wrong case then you will probably see
an AttributeError exception.
In Python, parentheses (()) are always used when calling a method. In
VBA, they may be omitted. Neglecting to add parentheses in Python will result
in the method not being called, so it’s important to be aware of which class
attributes are methods (and must therefore be called) and which are
properties (whose values are available by reference).
For example, the method Select on the Range type is a method and so must be called with parentheses
in Python, but in VBA they can be, and usually are, omitted.
' Select is a method and is called without parentheses in VBA
Range("B11:K11").Select
from pyxll import xl_app
xl = xl_app()
# In Python, the parentheses are necessary to call the method
xl.Range('B11:K11').Select()
Keyword arguments may be passed in both VBA and Python, but in Python keyword arguments use = instead
of the := used in VBA.
Accessing properties does not require parentheses, and doing so will give unexpected results! For example,
the range.Value property will return the value of the range. Adding () to it will attempt to
call that value, and as the value will not be callable it will result in an error.
from pyxll import xl_app
xl = xl_app()
# Value is a property and so no parentheses are used
value = xl.Range('B11:K11').Value
In VBA, named arguments are passed using Name := Value. In Python, the syntax is slightly different and only
the equals sign is used. One other important difference is that VBA is not case-sensitive but Python is. This
applies to argument names as well as method and property names.
In VBA, you might write
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
If you look at the documentation for Application.InputBox you will see that the argument names are cased different from this, and are actually ‘Prompt’ and ‘Type’. In Python, you can’t get away with getting the case wrong like you can in VBA.
In Python, this same method would be called as
from pyxll import xl_app
xl = xl_app()
my_range = xl.InputBox(Prompt='Sample', Type=8)
Both VBA and Python support properties. Accessing a property from an object is similar in both languages. For example, to fetch ActiveSheet property from the Application object you would do the following in VBA:
Set mySheet = Application.ActiveSheet
In Python, the syntax used is identical:
from pyxll import xl_app
xl = xl_app()
my_sheet = xl.ActiveSheet
In VBA, the distinction between methods and properties is somewhat blurred as properties in VBA can take
arguments. In Python, a property never takes arguments. To get around this difference, the win32com
Excel classes have Get and Set methods for properties that take arguments, in addition to the
property.
The Range.Offset property is an example of a property that takes optional arguments. If called with no arguments it simply returns the same Range object. To call it with arguments in Python, the GetOffset method must be used instead of the Offset property.
The following code activates the cell three columns to the right of and three rows down from the active cell on Sheet1:
Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
To convert this to Python we must make the following changes:
from pyxll import xl_app
xl = xl_app()
xl.Worksheets('Sheet1').Activate()
xl.ActiveCell.GetOffset(RowOffset=3, ColumnOffset=3).Activate()
Note
You may wonder, what would happen if you were to use the Offset property in Python? As you may by now expect, it would fail - but not perhaps in the way you might think.
If you were to call xl.ActiveCell.Offset(RowOffset=3, ColumnOffset=3) the the result would be
that the parameter RowOffset is invalid. What’s actually happening is that when
xl.ActiveCell.Offset is evaluated, the Offset property returns a Range equivalent to
ActiveCell, and that Range is then called.
Range has a default method. In Python this translates to the Range class being callable, and calling it calls the default method.
The default method for Range is Item, and so this bit of code is actually equivalent to
xl.ActiveCell.Offset.Item(RowOffset=3, ColumnOffset=3). The Item method doesn’t expect
a RowOffset argument, and so that’s why it fails in this way.
When writing VBA code, the code is usually written ‘on’ an object like a WorkBook or a Sheet. That object is used implicitly when writing VBA code.
If using a ‘With..End’ statement in VBA, the target of the ‘With’ statement becomes the implicit object.
If a property is not found on the current implicit object (e.g. the one specified in a ‘With..End’ statement) then the next one is tried (e.g. the Worksheet the Sub routine is associated with). Finally, the Excel Application object is implicitly used.
In Python there is no implicit object and the object you want to reference must be specified explicitly.
For example, the following VBA code selects a range and alters the column width.
Sub Macro2()
' ActiveSheet is a property of the Application
Set ws = ActiveSheet
With ws
' Range is a method of the Sheet
Set r = Range("A1:B10")
' Call Select on the Range
r.Select
End With
' Selection is a property of the Application
Selection.ColumnWidth = 4
End Sub
To write the same code in Python each object has to be referenced explicitly.
from pyxll import xl_macro, xl_app
@xl_macro
def macro2():
# Get the Excel.Application instance
xl = xl_app()
# Get the active sheet
ws = xl.ActiveSheet
# Get the range from the sheet
r = ws.Range('A1:B10')
# Call Select on the Range
r.Select()
# Change the ColumnWidth property on the selection
xl.Selection.ColumnWidth = 4
VBA uses parentheses (()) for calling methods and for indexing into collections.
In Python, square braces ([]) are used for indexing into collections.
Care should be taken when indexing into Excel collections, as Excel uses an index offset of 1 whereas Python uses 0. This means that to get the first item in a normal Python collection you would use index 0, but when accessing collections from the Excel Object Model you would use 1.
When writing VBA enum values are directly accessible in the global scope. For example, you can write
Set cell = Range("A1")
Set cell2 = cell.End(Direction:=xlDown)
In Python, these enum values are available as constants in the win32com.client.constants package.
The code above would be re-written in Python as follows
from pyxll import xl_app
from win32com.client import constants
xl = xl_app()
cell = xl.Range('A1')
cell2 = cell.End(Direction=constants.xlDown)
In VBA everything always runs on Excel’s main thread. In Python we have multi-threading support and sometimes to perform a long running task you may want to run code on a background thread.
The standard Python threading module is a convenient way to run code on a background thread
in Python. However, we have to be careful about how we call back into Excel from a background thread.
As VBA has no ability to use threads the Excel objects are not written in a such a way that they
can be used across different threads. Attempting to do so may result in serious problems and even cause Excel
to crash!
In order to be able to work with multiple threads and still call back into Excel PyXLL has the
schedule_call function. This is used to schedule a Python function to run on Excel’s
main thread in such a way that the Excel objects can be used safely. Whenever you are working with
threads and need to use the Excel API you should use schedule_call.
For example, you might use an Excel macro to start a long running task and when that task is complete
write the result back to Excel. Instead of writing the result back to Excel from the background
thread, use schedule_call instead.
from pyxll import xl_macro, xl_app, schedule_call
import threading
@xl_macro
def start_task():
# Here we're being called from a macro on the main thread
# so it's safe to use pyxll.xl_app.
xl = xl_app()
value = float(xl.Selection.Value)
# Use a background thread for a long running task.
# Be careful not to pass any Excel objects to the background thread!
thread = threading.Thread(target=long_running_task, args=(value,))
thread.start()
# This runs on a background thread
def long_running_task(value):
# Do some work that takes some time
result = ...
# We shouldn't write the result back to Excel here as we are on
# a background thread. Instead use pyxll.schedule_call to write
# the result back to Excel.
schedule_call(write_result, result, "A1")
# This is called via pyxll.schedule_call
def write_result(result, address):
# Now we're back on the main thread and it's safe to use pyxll.xl_app
xl = xl_app()
cell = xl.Range(address)
cell.Value = result
The Excel VBA editor has integrating debugging so you can step through the code and see what’s happening at each stage.
When writing Python code it is sometimes easier to write the code outside of Excel in your Python IDE before adapting it to be called from Excel as a macro or menu function etc.
When calling your code from Excel, remember that any uncaught exceptions will be printed to the PyXLL log file and so that should always be the first place you look to find what’s going wrong.
If you find that you need to be able to step through your Python code as it is being executed in Excel you will need a Python IDE that supports remote debugging. Remote debugging is how debuggers connect to an external process that they didn’t start themselves.
You can find instructions for debugging Python code running in Excel in this blog post Debugging Your Python Excel Add-In.
| [1] | If you prefer to use comtypes instead of win32com you can still use xl_app by passing
com_package='comtypes'. |
