VBA_REFERENCE.md

VBA Language Reference — Extraction Targets and Python Equivalents

This document lists VBA language constructs the extraction script should identify, with direct Python equivalents for iterative migration. Use it as a translation guide when moving code from extractions/*_extraction.json (vba_macros) to src/.

See also: MIGRATION_GUIDE.md · README.md

---

Data Types

VBA Type	Description	Python Equivalent
Boolean	True/False	bool
Byte	Integer 0–255	int
Integer	Integer −32,768 to 32,767	int
Long	Integer −2,147,483,648 to 2,147,483,647	int
Single	Single-precision floating point	float
Double	Double-precision floating point	float
Currency	Decimal number for financial calculations	decimal.Decimal
String	Character string	str
Date	Date and/or time	datetime.datetime
Variant	Dynamic type (accepts anything)	Dynamic Python type (no declaration needed)
Object	Reference to a COM object	Python object
Array	Array (one or more dimensions)	list or numpy.ndarray

---

Statements

VBA Statement	Role	Python Equivalent
Dim x As Type	Declares a variable	No mandatory declaration, or annotation x: int
Const x = value	Declares a constant	X = value in uppercase (convention)
Set obj = ...	Assigns an object	obj = ...
If...Then...ElseIf...Else...End If	Condition	if ... elif ... else:
Select Case x	Multiple condition	match x: (Python 3.10+) or if/elif
For i = 1 To N	Counted loop	for i in range(1, N + 1):
For Each item In collection	Loop over collection	for item in collection:
While condition ... Wend	While loop	while condition:
Do While/Until ... Loop	Loop with entry or exit condition	while condition: with break
Exit For / Exit Do	Exit loop	break
Sub ProcedureName()	Procedure with no return value	def procedure_name():
Function FunctionName()	Function with return value	def function_name(): return value
Call ProcedureName	Call a procedure	procedure_name()
With object ... End With	Simplified access to object members	No direct equivalent — use object.attribute
On Error GoTo label	Error handling	try: ... except Exception as e:
On Error Resume Next	Ignores errors	try: ... except: pass (not recommended)
Resume / Resume Next	Resumes after error	Handled inside except block
GoTo label	Unconditional jump	No equivalent — refactor the logic
ReDim / ReDim Preserve	Resizes an array	list.append() or numpy.resize()
Erase array	Clears an array	list.clear()
Type ... End Type	Custom data structure	@dataclass or class
Enum ... End Enum	Enumeration	from enum import Enum
Option Explicit	Forces variable declaration	No direct equivalent (Python is dynamic)
Option Base 0/1	Array starting index	Python always indexes from 0
Public / Private	Scope of variable or procedure	No keyword — use _ prefix for private
Static variable	Variable persists between calls	Class attribute or module-level variable
Stop	Breakpoint	breakpoint()
End	Stops execution	sys.exit()
MsgBox "message"	Dialog box	print() or UI widget
InputBox "prompt"	User input	Input field in the UI
SendKeys	Simulates keyboard input	pyautogui (only if strictly necessary)

---

Built-in Functions

String Functions

VBA Function	Description	Python Equivalent
Len(s)	Length of a string	len(s)
Left(s, n)	n characters from the left	s[:n]
Right(s, n)	n characters from the right	s[-n:]
Mid(s, start, n)	Substring from a position	s[start - 1 : start - 1 + n]
InStr(s1, s2)	Position of s2 in s1	s1.find(s2) + 1
InStrRev(s1, s2)	Position from the right	s1.rfind(s2) + 1
Replace(s, old, new)	Replaces a substring	s.replace(old, new)
Trim(s)	Removes surrounding spaces	s.strip()
LTrim(s)	Removes left spaces	s.lstrip()
RTrim(s)	Removes right spaces	s.rstrip()
UCase(s)	Uppercase	s.upper()
LCase(s)	Lowercase	s.lower()
StrReverse(s)	Reverses a string	s[::-1]
Split(s, delim)	Splits into array	s.split(delim)
Join(arr, delim)	Joins array into string	delim.join(arr)
StrComp(s1, s2)	Compares two strings	s1 == s2 or s1 < s2
String(n, c)	Repeats a character n times	c * n
Space(n)	Generates n spaces	' ' * n
Asc(c)	ASCII code of a character	ord(c)
Chr(n)	Character from ASCII code	chr(n)
Format(val, fmt)	Formats a value	format(val, fmt) or f"{val:.2f}"
FormatNumber(n, dec)	Formats a number	f"{n:.{dec}f}"
FormatCurrency(n)	Formats as currency	f"{n:,.2f}"
FormatPercent(n)	Formats as percentage	f"{n:.1%}"

Mathematical Functions

VBA Function	Description	Python Equivalent
Abs(n)	Absolute value	abs(n)
Int(n)	Integer part (rounds down)	math.floor(n)
Fix(n)	Integer part (truncation)	int(n)
Round(n, dec)	Rounding	round(n, dec)
Sqr(n)	Square root	math.sqrt(n)
Exp(n)	Exponential (e^n)	math.exp(n)
Log(n)	Natural logarithm	math.log(n)
Sin(n)	Sine (radians)	math.sin(n)
Cos(n)	Cosine (radians)	math.cos(n)
Tan(n)	Tangent (radians)	math.tan(n)
Atn(n)	Arctangent	math.atan(n)
Rnd()	Random number 0–1	random.random()

Date and Time Functions

VBA Function	Description	Python Equivalent
Now	Current date and time	datetime.now()
Date	Current date	datetime.today().date()
Time	Current time	datetime.now().time()
Year(d)	Year from a date	d.year
Month(d)	Month from a date	d.month
Day(d)	Day from a date	d.day
Hour(d)	Hour	d.hour
Minute(d)	Minutes	d.minute
Second(d)	Seconds	d.second
Weekday(d)	Day of the week number	d.weekday()
DateAdd(interval, n, d)	Adds an interval to a date	d + timedelta(days=n)
DateDiff(interval, d1, d2)	Difference between two dates	(d2 - d1).days
DatePart(interval, d)	Specific part of a date	d.year, d.month, etc.
DateSerial(y, m, d)	Creates a date	datetime(y, m, d)
DateValue(s)	Converts string to date	datetime.strptime(s, fmt)
CDate(val)	Conversion to date	pd.to_datetime(val)
MonthName(n)	Month name	calendar.month_name[n]
WeekdayName(n)	Day of week name	calendar.day_name[n - 1]
Timer	Seconds since midnight	time.time() % 86400

Type Conversion Functions

VBA Function	Description	Python Equivalent
CInt(val)	Converts to integer (with rounding)	int(round(val))
CLng(val)	Converts to Long	int(val)
CDbl(val)	Converts to Double	float(val)
CSng(val)	Converts to Single	float(val)
CStr(val)	Converts to string	str(val)
CBool(val)	Converts to boolean	bool(val)
CDate(val)	Converts to date	pd.to_datetime(val)
CVar(val)	Converts to Variant	Not needed in Python
Val(s)	Extracts numeric part from string	float(s) or int(s)
Str(n)	Converts a number to string	str(n)
Hex(n)	Converts to hexadecimal	hex(n)
Oct(n)	Converts to octal	oct(n)

Information and Test Functions

VBA Function	Description	Python Equivalent
IsNumeric(val)	Checks if val is numeric	isinstance(val, (int, float))
IsDate(val)	Checks if val is a date	isinstance(val, datetime)
IsNull(val)	Checks if val is Null	val is None
IsEmpty(val)	Checks if val is empty	val is None or val == ""
IsArray(val)	Checks if val is an array	isinstance(val, list)
IsError(val)	Checks if val is an error	isinstance(val, Exception)
IsObject(val)	Checks if val is an object	isinstance(val, object)
IsMissing(val)	Checks if a parameter is absent	Use val=None then if val is None
TypeName(val)	Returns the type name	type(val).__name__
VarType(val)	Returns the type code	type(val)
IIf(cond, true, false)	Inline condition	true if cond else false
Choose(n, v1, v2, ...)	Selects by index	[v1, v2, ...][n - 1]
Switch(c1, v1, c2, v2, ...)	First true condition	Chain of if/elif

Financial Functions

VBA Function	Description	Python Equivalent
FV(rate, nper, pmt)	Future value	numpy_financial.fv()
PV(rate, nper, pmt)	Present value	numpy_financial.pv()
NPV(rate, values)	Net present value	numpy_financial.npv()
IRR(values)	Internal rate of return	numpy_financial.irr()
IPmt(rate, per, nper, pv)	Interest portion of a payment	numpy_financial.ipmt()
PPmt(rate, per, nper, pv)	Principal portion of a payment	numpy_financial.ppmt()
SLN(cost, salvage, life)	Straight-line depreciation	Manual calculation
DDB(cost, salvage, life, period)	Double-declining balance depreciation	Manual calculation

File and System Functions

VBA Function	Description	Python Equivalent
Dir(path)	Lists files matching a pattern	glob.glob(path)
FileLen(path)	File size in bytes	os.path.getsize(path)
FileDateTime(path)	Last modified date	os.path.getmtime(path)
Kill(path)	Deletes a file	os.remove(path)
FileCopy(src, dst)	Copies a file	shutil.copy(src, dst)
MkDir(path)	Creates a directory	os.makedirs(path)
RmDir(path)	Removes a directory	os.rmdir(path)
ChDir(path)	Changes the current directory	os.chdir(path)
CurDir()	Current directory	os.getcwd()
Shell(cmd)	Executes a system command	subprocess.run(cmd)
Environ(var)	Environment variable	os.environ[var]
GetAttr(path)	File attributes	os.stat(path)
SetAttr(path, attr)	Modifies file attributes	os.chmod(path, ...)
EOF(n)	End of file	f.read() == ""
Open...For...As	Opens a file	open(path, mode)
Close #n	Closes a file	f.close() or with open(...)
Print #n, ...	Writes to a file	f.write(...)
Line Input #n, var	Reads a line	f.readline()

---

Operators

Arithmetic Operators

VBA Operator	Description	Python Equivalent
+	Addition	+
-	Subtraction	-
*	Multiplication	*
/	Floating-point division	/
\	Integer division	//
Mod	Division remainder	%
^	Exponentiation	**

Comparison Operators

VBA Operator	Description	Python Equivalent
=	Equal to	==
<>	Not equal to	!=
<	Less than	<
<=	Less than or equal	<=
>	Greater than	>
>=	Greater than or equal	>=
Is	Same object reference	is
Like	Pattern matching	re.match()

Logical and Concatenation Operators

VBA Operator	Description	Python Equivalent
And	Logical AND	and
Or	Logical OR	or
Not	Logical NOT	not
Xor	Exclusive OR	^ (on booleans)
Eqv	Logical equivalence	== (on booleans)
Imp	Logical implication	not a or b
&	String concatenation	+ or f-string

---

Excel Object Model

These objects are specific to Excel and must be replaced by openpyxl, pandas, or UI framework calls.

VBA Excel Object	Role	Python Equivalent
Application	Global Excel instance	No equivalent concept
Workbook	Open workbook	openpyxl.load_workbook()
Worksheet	Sheet within a workbook	wb["SheetName"]
Range("A1")	Cell or range	ws["A1"] or ws.cell(row, col)
Range("A1:B5")	Multi-cell range	ws["A1:B5"]
Cells(r, c)	Cell by coordinates	ws.cell(row=r, column=c)
ActiveSheet	Currently active sheet	Python variable for current worksheet
ActiveWorkbook	Currently active workbook	Python variable for current workbook
Selection	Current selection	No concept (UI only)
UserForm	Graphical form	Streamlit page or HTML form
ListBox / ComboBox	Dropdown lists	st.selectbox()
TextBox	Input field	st.text_input()
CommandButton	Action button	st.button()
Chart	Embedded chart	plotly.express or altair
PivotTable	Pivot table	pd.pivot_table()
Worksheets.Add	Adds a sheet	wb.create_sheet()
Range.Sort	Sorts a range	df.sort_values()
Range.AutoFilter	Auto filter	df[condition]
Range.Find	Finds in a range	df[df['col'] == val]
MsgBox	Modal dialog box	st.warning() or st.success()
InputBox	Input dialog	st.text_input()

---

VBA Events and Web App Equivalents

VBA Event	Trigger	Web App Equivalent
Workbook_Open	On file open	App initialization on startup
Workbook_BeforeClose	Before closing	Session callback / atexit
Worksheet_Change(Target)	Cell modification	on_change callback on a widget
Worksheet_Activate	Navigate to a sheet	Page navigation in the app
CommandButton_Click	Button click	if st.button("Submit"):
ComboBox_Change	Dropdown change	on_change on st.selectbox()
UserForm_Initialize	Form opened	Streamlit page initialization
BeforeDoubleClick	Double-click on a cell	No direct concept — refactor
Calculate	Sheet recalculation	Automatically triggered by Python

---

What to Verify Manually During Translation

Usually automated well	Check manually
Syntactic VBA → Python translation	Complex nested business logic
Unit test scaffolding	Expected values from specs/*_FUNCTIONAL_SPEC.md
File structure and imports	Rounding and numeric edge cases (VBA ≠ Python)
Documentation drafts	Cross-sheet dependencies not visible in VBA
API endpoint stubs	Security and authentication

Always ask for a line-by-line explanation of generated migration code before merging.