When converting existing VBA code to Python there are a number of obvious changes to the syntax that need to be made for the code to run at all, but there are also a number of not so obvious traps that may result in the code running, but giving incorrect results. Three that regularly catch me unawares are illustrated in the screen shots below:
The first is the value of the counter at the completion of a for loop. In VBA the counter runs from the specified start to stop values inclusive, then after the final loop the counter is incremented again. In Python the counter runs until the last value lower than the specified stop value, and is not incremented after the last step. The result is that the examples below generate the same number of steps (n), and the same value in the calculated quantity (j), but the final counter value (i) is one higher in the VBA code than in the Python:
Differences in application of the = operator when applied to arrays have been discussed previously (The meaning of = in Python). Similar differences occur when variables are passed to other functions, with results that can be confusing and unintuitive (at least to me). In the examples below:
- A variable, x, is passes to a function.
- A local variable y is assigned the value of x, then multiplied by n (sometimes in one operation).
- x is multiplied by n.
- y is returned to the calling function, which then returns x and y to the spreadsheet.
The returned results depend on the variable type, how the multiplication is carried out, and the language. In the examples below the variable x is passed in Python first as a float then as a Numpy array, and in both cases the multiplication is carried out with x = x*n for x and y = x*n for y. In VBA the multiplication was carried out with a for loop.
- In the Python code y returns the multiplied values, but the operations on x are treated as local, and the values in the calling function remain unchanged, both for the single value x and the Numpy array.
- in the VBA code in both cases the x variable is passed by reference, so any operations on x are reflected in the calling routine:
In the next 3 examples x is passed as a Numpy array in Python, and y is created with y = x*n.
- The modified value of y is always returned to the calling routine.
- When x is multiplied by directly modifying the value of the array elements the modified values are reflected in the calling routine, but using for a in x: creates multiplied values of a, but does not modify the values in the x array, so the values in the calling routine are also unchanged.
- In VBA, options 3 and 5 are essentially the same as Python option 5, and return the same results. VBA Option 4 uses a for each loop for both x and y, which does not modify either array, locally or in the calling routine.
In the final 3 Python examples x is passed as a list and y is set equal to x.
- Where the multiplications are applied to the individual elements of either x or y, both arrays are modified, and the modified values are transferred to the calling array.
- Where a for a in y loop is used neither array is modified, either locally or in the calling routine.
in the VBA examples:
- In Option 6 y is created as a Range object and x is then converted to a variant array. The multiplication of x is reflected in the calling routine, but y is unchanged.
- Use of “for each a in y” in Option 7 does not change the values in either x or y.
- In Option 8 y is set equal to x, then converted to a variant array and multiplied by n. The changes in y are returned to the calling array, but x is unchanged.
The code for all the routines is listed below:
Python Code:
@xl_func
@xl_arg('start', 'int')
@xl_arg('stop', 'int')
def CountLoop(start, stop):
n = 0
j = start
for i in range(start, stop):
n = n+1
j = j + 1
return [[i, j, n]]
@xl_func
@xl_arg('val1')
@xl_arg('val2')
@xl_arg('rng1', 'numpy_array', ndim=1)
@xl_arg('rng2', 'float[]')
@xl_arg('out', 'int')
#@xl_return('numpy_array')
def PassArgs(val1, val2, rng1, rng2, out):
if out == 1:
rtn = mult1(val1, val2)
return [rtn, val1]
elif out == 2:
rtn = mult1(rng1, val2)
return [rtn, rng1]
elif out == 3:
rtn = mult2(rng1, val2)
return [rtn, rng1]
elif out == 4:
rtn = mult3(rng1, val2)
return [rtn, rng1]
elif out == 5:
rtn = mult4(rng1, val2)
return [rtn, rng1]
elif out == 6:
rtn = mult5(rng2, val2)
return [rtn, rng2]
elif out == 7:
rtn = mult6(rng2, val2)
return [rtn, rng2]
else:
rtn = mult7(rng2, val2)
return [rtn, rng2]
def mult1(x, n):
y = x*n
x = x*n
return y
def mult2(x, n):
y = x*n
x[:] = x[:]*n
return y
def mult3(x, n):
y = x*n
for a in x:
a = a*n
return y
def mult4(x, n):
y = x*n
for i in range(0, 2):
x[i] = x[i]*n
return y
def mult5(x, n):
y = x
for i in range(0, 2):
x[i] = x[i]*n
return y
def mult6(x, n):
y = x
for a in y:
a = a*n
return y
def mult7(x, n):
y = x
for i in range(0, 2):
y[i] = y[i]*n
return y
VBA Code:
Function vbCountLoop(start, last)
n = 0
j = start
For i = start To last
n = n + 1
j = j + 1
Next i
vbCountLoop = Array(i, j, n)
End Function
Function vbPassArgs(val1, val2, rng1, rng2, out)
If out = 1 Then
rtn = mult1(val1, val2)
vbPassArgs = WorksheetFunction.Transpose(Array(rtn, val1))
ElseIf out = 2 Then
rtn = mult1a(rng1, val2)
vbPassArgs = (Array(rtn, rng1))
ElseIf out = 3 Then
rtn = mult2(rng1, val2)
vbPassArgs = (Array(rtn, rng1))
ElseIf out = 4 Then
rtn = mult3(rng1, val2)
vbPassArgs = (Array(rtn, rng1))
ElseIf out = 5 Then
rtn = mult4(rng1, val2)
vbPassArgs = (Array(rtn, rng1))
ElseIf out = 6 Then
rtn = mult5(rng2, val2)
vbPassArgs = (Array(rtn, rng2))
ElseIf out = 7 Then
rtn = mult6(rng2, val2)
vbPassArgs = (Array(rtn, rng2))
Else
rtn = mult7(rng2, val2)
vbPassArgs = (Array(rtn, rng2))
End If
End Function
Function mult1(x, n)
y = x * n
x = x * n
mult1 = y
End Function
Function mult1a(x, n)
x = x.Value2
y = x
For i = 1 To 2
y(1, i) = x(1, i) * n
x(1, i) = x(1, i) * n
Next i
mult1a = y
End Function
Function mult2(x, n)
x = x.Value2
y = x
For i = 1 To 2
y(1, i) = x(1, i) * n
x(1, i) = x(1, i) * n
Next i
mult2 = y
End Function
Function mult3(x, n)
x = x.Value2
y = x
For Each a In y
a = a * n
Next a
For Each a In x
a = a * n
Next a
mult3 = y
End Function
Function mult4(x, n)
x = x.Value2
y = x
For i = 1 To 2
y(1, i) = y(1, i) * n
x(1, i) = x(1, i) * n
Next i
mult4 = y
End Function
Function mult5(x, n)
Set y = x
x = x.Value2
For i = 1 To 2
x(1, i) = x(1, i) * n
Next i
mult5 = y
End Function
Function mult6(x, n)
Set y = x
For Each a In y
a = a * n
Next a
mult6 = y.Value2
End Function
Function mult7(x, n)
Set y = x
y = y.Value2
For i = 1 To 2
y(1, i) = y(1, i) * n
Next i
mult7 = y
End Function
Newton Excel Bach, not (just) an Excel Blog 