pyOpenVBA

https://github.com/williamsmithedward/pyopenvba
Admin
pyOpenVBA is a pure Python library to read and write VBA macros inside Office 365 files without...
Tokens:231,679
Snippets:1,432
Trust Score:7
License:MIT
Update:1 month ago
Show doc for...
Tokens:
Raw
### Install pyOpenVBA from source for development

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/README.md

Clone the repository and install in editable mode with development dependencies.

```bash
git clone https://github.com/WilliamSmithEdward/pyOpenVBA
cd pyOpenVBA
pip install -e ".[dev]"
```

--------------------------------

### Command-line declaration rewrite example

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/research/access_write/README.md

Shows a sample command-line invocation of the rewrite tool, demonstrating a Dim statement with arithmetic and a result. The output '-> 21' is shown as part of the example.

```text
Dim aa As Long / Dim bb As Long / Dim cc As Double
aa = 20 / bb = 22 / cc = 0.5 / Go = (aa + bb) * cc     -> 21
```

--------------------------------

### Install pyOpenVBA with pip

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/demo/demo.md

Install pyOpenVBA using pip. The second command installs in editable mode from a clone of the repository.

```powershell
pip install pyOpenVBA
```

```powershell
pip install -e .   # from a clone of this repo
```

--------------------------------

### AddShape and AddTextbox Name Examples

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/tests/fixtures/excel_model/probes.txt

Examples of adding shapes and textboxes, then retrieving their Name property.

```VBA
;; ActiveSheet.Shapes.AddShape(1, 10, 20, 100, 50).Name
```

```VBA
;; ActiveSheet.Shapes.AddShape(5, 10, 20, 100, 50).Name
```

```VBA
;; ActiveSheet.Shapes.AddShape(9, 10, 20, 100, 50).Name
```

```VBA
;; ActiveSheet.Shapes.AddTextbox(1, 10, 20, 100, 50).Name
```

--------------------------------

### Compressing a Token pseudocode (partial)

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/MS-OVBA_official_specification.pdf

Determines the token type using the Matching algorithm and manufactures the token. If the token would exceed the compressed chunk boundary, it is not inserted and CompressedCurrent is set to signal the chunk is full. The pseudocode shown is incomplete in the source, covering only the initial setup and the start of the conditional logic.

```plaintext
SET Offset TO zero
CALL Matching (section 2.4.1.3.19.4) with DecompressedEnd returning Offset and Length
IF Offset is not zero THEN
IF (CompressedCurrent PLUS 1) is LESS THAN CompressedEnd THEN
CALL Pack CopyToken (section 2.4.1.3.19.3) with Offset and Length returning Token
APPEND the bytes of the CopyToken (section 2.4.1.1.8) Token TO CompressedCurrent in little-endian order
CALL Set FlagBit (section 2.4.1.3.18) with index, 1, and Flags
INCREMENT CompressedCurrent BY 2
INCREMENT DecompressedCurrent BY Length
ELSE
SET CompressedCurrent TO CompressedEnd
ENDIF
ELSE IF CompressedCurrent is LESS THAN CompressedEnd THEN
APPEND the byte of the LiteralToken at DecompressedCurrent
TO CompressedCurrent
INCREMENT CompressedCurrent
INCREMENT DecompressedCurrent
ELSE
SET CompressedCurrent TO CompressedEnd
ENDIF
ENDIF
```

--------------------------------

### Metadata XML item example

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/power_query.md

Shows an example Item element in the metadata XML, with an ItemLocation and StableEntries. The first item is AllFormulas with an empty path, and each query is an item with at least one entry.

```xml
<Item>
  <ItemLocation><ItemType>Formula</ItemType>
    <ItemPath>Section1/Order%20Lines</ItemPath></ItemLocation>
  <StableEntries><Entry Type="IsPrivate" Value="l0" /></StableEntries>
</Item>
```

--------------------------------

### AddLine and AddFormControl Name Examples

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/tests/fixtures/excel_model/probes.txt

Examples of adding a line and a form control, then retrieving their Name property.

```VBA
;; ActiveSheet.Shapes.AddLine(10, 20, 100, 50).Name
```

```VBA
;; ActiveSheet.Shapes.AddFormControl(0, 10, 20, 100, 50).Name
```

--------------------------------

### MsgBox action record example

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/research/access_macros/README.md

Worked example of an action record for MsgBox "Hi", "No", 1, "Title" as the second action. Shows the byte offsets into the string area for each argument.

```hex
16 00 02 00                     action 22, row 2
ff ff ff ff ff ff ff ff         slots 0-3, unused
00 00 06 00 0c 00 10 00         slots 4-7: offsets 0, 6, 12, 16
ff ff ... ff ff                 slots 8-13
1c 00                           28 bytes of strings
48 00 69 00 00 00               "Hi"
4e 00 6f 00 00 00               "No"
31 00 00 00                     "1"
54 00 69 00 74 00 6c 00 65 00 00 00   "Title"
00 00
```

--------------------------------

### PROJECT Stream Example

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/MS-OVBA_official_specification.pdf

Example of a PROJECT stream in the VBA file format, showing the structure and properties of a VBA project. It includes the project ID, document modules, package CLSID, help file path, project name, help context ID, description, version compatibility, and protection state.

```text
ID="{917DED54-440B-4FD1-A5C1-74ACF261E600}"
Document=ThisWorkbook/&H00000000
Document=Sheet1/&H00000000
Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F}
BaseClass=UserForm1
HelpFile="c:\example path\example.hlp"
Name="VBAProject"
HelpContextID="1"
Description="Example VBA Project Description"
VersionCompatible32="393222000"
CMG="0705D8E3D8EDDBF1DBF1DBF1DBF1"
DPB="0E0CD1ECDFF4E7F5E7F5E7"
GC="1517CAF1D6F9D7F9D706"
[Host Extender Info]
&H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000
[Workspace]
ThisWorkbook=23, 23, 911, 280,
Sheet1=69, 69, 724, 317, C
UserForm1=0, 0, 0, 0, C, 46, 46, 701, 294, Z
```

--------------------------------

### VBA Project Stream

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/MS-OVBA_official_specification.pdf

Example of the VBA project stream containing project properties and workspace information.

```text
ID="{917DED54-440B-4FD1-A5C1-74ACF261E600}"
Document=ThisWorkbook/&H00000000
Document=Sheet1/&H00000000
Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F}
BaseClass=UserForm1
HelpFile="c:\example\path\example.hlp"
Name="VBAProject"
HelpContextID="1"
Description="Example Description"
VersionCompatible32="393222000"
CMG="0705D8E3D8EDDBF1DBF1DBF1DBF1"
DPB="0E0CD1ECDFF4E7F5E7F5E7"
GC="1517CAF1D6F9D7F9D706"

[ Host Extender Info]
&H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000

[Workspace]
ThisWorkbook=23, 23, 911, 280, 
Sheet1=69, 69, 724, 317, C
UserForm1=0, 0, 0, 0, C, 46, 46, 701, 294, Z
```

--------------------------------

### Criteria Functions: COUNTIF, SUMIF, SUMIFS, COUNTIFS, AVERAGEIF, COUNTBLANK

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/tests/fixtures/formula/probes.txt

Examples of criteria-based functions with VBA setup code to populate cells. Each line shows the VBA assignment followed by the formula and its expected result, separated by ';;'. These demonstrate how criteria work with numbers, text, wildcards, and blank cells.

```vba
Range("A1").Value = 1: Range("A2").Value = 5: Range("A3").Value = 10 ;; =COUNTIF(A1:A3,">4")
```

```vba
Range("A1").Value = 1: Range("A2").Value = 5: Range("A3").Value = 10 ;; =SUMIF(A1:A3,">4")
```

```vba
Range("A1").Value = "a": Range("A2").Value = "b": Range("B1").Value = 1: Range("B2").Value = 2 ;; =SUMIF(A1:A2,"a",B1:B2)
```

```vba
Range("A1").Value = "a": Range("A2").Value = "A" ;; =COUNTIF(A1:A2,"a")
```

```vba
Range("A1").Value = "apple": Range("A2").Value = "apricot" ;; =COUNTIF(A1:A2,"ap*")
```

```vba
Range("A1").Value = "apple": Range("A2").Value = "apricot" ;; =COUNTIF(A1:A2,"a?p*")
```

```vba
Range("A1").Value = "a": Range("A2").Value = "b" ;; =COUNTIF(A1:A2,"<>a")
```

```vba
Range("A1").Value = 1: Range("A2").Value = 2: Range("B1").Value = "x": Range("B2").Value = "y" ;; =SUMIFS(A1:A2,B1:B2,"y")
```

```vba
Range("A1").Value = 1: Range("A2").Value = 2: Range("B1").Value = "x": Range("B2").Value = "x" ;; =COUNTIFS(A1:A2,">0",B1:B2,"x")
```

```vba
Range("A1").Value = 1: Range("A2").Value = 2 ;; =AVERAGEIF(A1:A2,">2")
```

```vba
Range("A1").Value = 1 ;; =COUNTIF(A1:A3,"")
```

```vba
Range("A1").Value = 1 ;; =COUNTBLANK(A1:A3)
```

--------------------------------

### Show pyOpenVBA CLI help

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/README.md

After installation, the CLI is available as a module or as a script. Both commands display the help text.

```bash
python -m pyopenvba --help
```

```bash
pyopenvba --help
```

--------------------------------

### Lookup Functions: VLOOKUP, MATCH, HLOOKUP, INDEX, CHOOSE, ROW, COLUMN, ROWS, COLUMNS, OFFSET, INDIRECT

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/tests/fixtures/formula/probes.txt

Examples of lookup and reference functions with VBA setup code. Each line shows the VBA assignment followed by the formula and its expected result, separated by ';;'. These demonstrate exact and approximate matches, wildcards, array handling, and error cases.

```vba
Range("A1").Value = "a": Range("B1").Value = 1: Range("A2").Value = "b": Range("B2").Value = 2 ;; =VLOOKUP("b",A1:B2,2,FALSE)
```

```vba
Range("A1").Value = "a": Range("B1").Value = 1: Range("A2").Value = "b": Range("B2").Value = 2 ;; =VLOOKUP("B",A1:B2,2,FALSE)
```

```vba
Range("A1").Value = "a": Range("B1").Value = 1 ;; =VLOOKUP("z",A1:B2,2,FALSE)
```

```vba
Range("A1").Value = 1: Range("B1").Value = "x": Range("A2").Value = 5: Range("B2").Value = "y" ;; =VLOOKUP(3,A1:B2,2)
```

```vba
Range("A1").Value = 1: Range("B1").Value = "x": Range("A2").Value = 5: Range("B2").Value = "y" ;; =VLOOKUP(3,A1:B2,2,TRUE)
```

```vba
Range("A1").Value = "a": Range("A2").Value = "b" ;; =MATCH("b",A1:A2,0)
```

```vba
Range("A1").Value = 1: Range("A2").Value = 5 ;; =MATCH(3,A1:A2)
```

```vba
Range("A1").Value = 1: Range("A2").Value = 5 ;; =MATCH(3,A1:A2,0)
```

```vba
Range("A1").Value = "a": Range("B1").Value = "b" ;; =HLOOKUP("b",A1:B1,1,FALSE)
```

```vba
Range("A1:B2").Value = 7 ;; =INDEX(A1:B2,2,2)
```

```vba
Range("A1").Value = 1: Range("A2").Value = 2 ;; =INDEX(A1:A2,2)
```

```vba
Range("A1").Value = 1: Range("A2").Value = 2 ;; =INDEX(A1:A2,3)
```

```vba
Range("A1").Value = 1: Range("A2").Value = 2 ;; =SUM(INDEX(A1:A2,0))
```

```vba
=CHOOSE(2,"a","b","c")
```

```vba
=CHOOSE(0,"a")
```

```vba
;; =ROW()
```

```vba
;; =COLUMN()
```

```vba
;; =ROWS(A1:A5)&"/"&COLUMNS(A1:C1)
```

```vba
Range("A1").Value = 9 ;; =OFFSET(A1,0,0)
```

```vba
Range("B2").Value = 9 ;; =OFFSET(A1,1,1)
```

```vba
Range("A1:A3").Value = 2 ;; =SUM(OFFSET(A1,0,0,3,1))
```

```vba
Range("A1").Value = 5 ;; =INDIRECT("A1")
```

```vba
Range("A1").Value = 5 ;; =INDIRECT("A"&1)
```

```vba
=INDIRECT("nonsense")
```

--------------------------------

### Run live gates on Windows with environment variables

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/README.md

On Windows with desktop Office installed, set the corresponding environment variable and run the live gate test. These tests are skipped by default and in CI. Each builds a file with pyOpenVBA and has the real application open it, run its code, or perform the same edit for a byte-for-byte comparison.

```powershell
$env:RUN_LIVE_EXCEL = "1"; pytest tests/test_live_excel_gate.py
$env:RUN_LIVE_ACCESS = "1"; pytest tests/test_live_access_engine_gate.py
$env:RUN_LIVE_ACCESS_VBA = "1"; pytest tests/test_live_access_design_gate.py
$env:RUN_LIVE_POWER_QUERY = "1"; pytest tests/test_live_powerquery_gate.py
```

--------------------------------

### Create Excel workbook from scratch and run macro

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/vba_runtime.md

Creates a new ExcelApplication with no file, adds a workbook and a module containing VBA code, then runs the macro and reads a cell value. Use when starting with nothing.

```python
app = ExcelApplication()
app.add_workbook()
app.add_module('Sub Main()\n    Range("A1").Value = 42\nEnd Sub\n', name="Module1")
app.run("Main")
app.sheet(1).value("A1")        # 42
```

--------------------------------

### GUID ABNF Rule

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/MS-OVBA_official_specification.pdf

Specifies a GUID.

```ABNF
GUID = "{" 8HEXDIG "-" 4HEXDIG "-" 4HEXDIG "-" 4HEXDIG "-" 12HEXDIG "}"
```

--------------------------------

### Run push_demo_module.py

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/demo/demo.md

Run the demo script from the project folder. It adds a DemoShowcase module with three macros to test_macro_workbook.xlsm.

```powershell
python push_demo_module.py
```

--------------------------------

### Run roundtrip.py with scratch path

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/research/pcode/README.md

Runs the acceptance gate that compiles a 37-entry corpus with Excel, decompiles it, and diffs against the original source. Requires Windows and desktop Excel, and writes workbooks to PYOPENVBA_PCODE_SCRATCH or a temp directory.

```bash
python docs/research/pcode/roundtrip.py /path/to/scratch
```

--------------------------------

### Shapes Count and TypeName Examples

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/tests/fixtures/excel_model/probes.txt

Examples of using ActiveSheet.Shapes.Count and TypeName to inspect the Shapes collection.

```VBA
;; ActiveSheet.Shapes.Count
```

```VBA
;; TypeName(ActiveSheet.Shapes)
```

--------------------------------

### Run verify_compiler.py on sample.accdb

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/docs/research/access_write/README.md

Runs the compiler verification gate on a sample Access database. Recompiles every module in an Access-built project and requires byte-identical output.

```bash
python docs/research/access_write/verify_compiler.py sample.accdb
```

--------------------------------

### Command-line interface for Power Query export/import

Source: https://github.com/williamsmithedward/pyopenvba/blob/main/README.md

Shows the CLI commands to list, pull, and push Power Query queries. Each query is saved as a .m file with a queries.json manifest for metadata. The commands are: pq-ls, pq-pull, and pq-push.

```bash
python -m pyopenvba pq-ls   workbook.xlsx        # name, where it loads, its group
python -m pyopenvba pq-pull workbook.xlsx ./queries
python -m pyopenvba pq-push ./queries workbook.xlsx
```
Tokens:
Raw