As noted in the comments here, the main bottleneck in the Python matrix solver functions presented recently was not in the data transfer from Excel, but rather in the creation of the Numpy arrays for very long lists of short lists (see this Stackoverflow thread for more details of the background). It seems there is a substantial time saving in converting the long array into 1D vectors, which can be converted into Numpy arrays very much more quickly. The VBA code below converts a 3D array of any length (up to the maximum allowed in Excel) to 3 vectors.

Function Vectorize(x As Variant, x_1 As Variant, x_2 As Variant, x_3 As Variant) As Long
Dim nr As Long
nr = x.Rows.Count
x_1 = x.Resize(nr, 1).Value2
x_2 = x.Offset(0, 1).Resize(nr, 1).Value2
x_3 = x.Offset(0, 2).Resize(nr, 1).Value2
Vectorize = nr
End Function

To transfer these vectors to Python, via ExcelPython, the PyObj function must be used:

Set x = Range("SSA") ' Excel range with 500500 rows and 3 columns
n = Vectorize(x, x_1, x_2, x_3) 'Convert range values to 3 vectors
' Create ExcelPython objects for transfer to Python
Set x_1 = PyObj(x_1, 1)
Set x_2 = PyObj(x_2, 1)
Set x_3 = PyObj(x_3, 1)

In Python the three vectors are converted to Numpy arrays:

def xl_getnpvect(x_1, x_2, x_3):
timea = np.zeros(4)
timea[0] = time.clock()
x_1 = np.array(x_1)
timea[1] = time.clock()
x_2 = np.array(x_2)
timea[2] = time.clock()
x_3 = np.array(x_3)
timea[3] = time.clock()
return timea.tolist()

The table below compares the data transfer and conversion times using this method on an Excel range of 500500 rows x 3 columns, with the same operation using a 2D variant array.

The times for solution of a large sparse matrix system (10945×10945 matrix), using the new vector transfer routine, are shown below:

The data transfer and array creation times are now a relatively small proportion of the total solution time, even for the iterative solver with a solve time of only 0.28 seconds.

### Like this:

Like Loading...

*Related*

Your code looks good. Thanks for sharing.

LikeLike

maybe it might be of help to force vba to pass data a ONE-dimensional array (as a list), and then

make the reshape to right rows/columns in numpy (maybe also specifying the type and the

column order).

With a tweak, it is possible to do it WITHOUT copying the range, but simply forcing the array to think

(temporarily) that it is one-dimensional. The following code does just this, and then restores the

array:

Option Explicit

Option Base 1

Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” (pvDest As Any, lpvSource As Any, ByVal cbCopy As Long)

Declare Function VarPtrArray Lib “msvbvm60.dll” Alias “VarPtr” (Ptr() As Any) As Long

Private Const VT_BYREF = &H4000&

Public Type SAFEARRAYBOUND

cElements As Long

lLbound As Long

End Type

Public Type SAFEARRAY

cDims As Integer

fFeatures As Integer

cbElements As Long

cLocks As Long

pvData As Long

rgsabound(3) As SAFEARRAYBOUND

End Type

Function Rank(A) As Long

Dim lp As Long, VType As Integer, sa As SAFEARRAY, n As Long

Rank = 0

If Not IsArray(A) Then Exit Function

With sa

CopyMemory VType, A, 2

CopyMemory lp, ByVal VarPtr(A) + 8, 4

If (VType And VT_BYREF) 0 Then

CopyMemory lp, ByVal lp, 4

End If

CopyMemory .cDims, ByVal lp, 2

Rank = .cDims

End With

End Function

Function Make1Dim(A, m As Long)

Dim lp As Long, VType As Integer, sa As SAFEARRAY, n As Long

If Not IsArray(A) Then Exit Function

With sa

CopyMemory VType, A, 2

CopyMemory lp, ByVal VarPtr(A) + 8, 4

If (VType And VT_BYREF) 0 Then

CopyMemory lp, ByVal lp, 4

End If

CopyMemory .cDims, ByVal lp, 16

CopyMemory .rgsabound(1), ByVal lp + 16, 2 * Len(.rgsabound(1))

m = .rgsabound(2).cElements

n = .rgsabound(1).cElements

.rgsabound(1).cElements = m * n

.rgsabound(2).cElements = m * n

.cDims = 1

CopyMemory ByVal lp + 16, .rgsabound(1), 2 * Len(.rgsabound(1))

CopyMemory ByVal lp, .cDims, 16

End With

Make1Dim = A

End Function

Sub reset2Dim(A, m As Long)

Dim lp As Long, n As Long, VType As Integer, sa As SAFEARRAY

If Not IsArray(A) Then Exit Sub

With sa

CopyMemory VType, A, 2

CopyMemory lp, ByVal VarPtr(A) + 8, 4

If (VType And VT_BYREF) 0 Then

CopyMemory lp, ByVal lp, 4

End If

CopyMemory .cDims, ByVal lp, 16

CopyMemory .rgsabound(1), ByVal lp + 16, 2 * Len(.rgsabound(1))

.rgsabound(2).cElements = m

.rgsabound(1).cElements = .rgsabound(1).cElements \ m

.cDims = 2

CopyMemory ByVal lp + 16, .rgsabound(1), 2 * Len(.rgsabound(1))

CopyMemory ByVal lp, .cDims, 16

End With

End Sub

Sub sho(r, A)

Dim n As Integer

n = Rank(A)

Select Case n

Case 0

r = A

Case 1

r.Resize(1, UBound(A, 1)).Value2 = A

Case 2

r.Resize(UBound(A, 1), UBound(A, 2)).Value2 = A

End Select

End Sub

Sub Test()

Dim A, m As Long ‘ m saves the right no. of rows

A = [B3:D6]

sho [B10], Make1Dim(A, m)

reset2Dim A, m

sho [B13], A

End Sub

just put numbers in the range B3:D6, and run Test.

LikeLike

Hi Maurizio, thanks for the suggestion and the code.

I had some problems with the code:

After pasting into the VBE, and replacing the WordPress ” and ‘ with the standard versions, I still had three lines marked as errors:

If (VType And VT_BYREF) 0 Then

I have deleted the 0, and the code now seems to work. Does that make sense?

I’m still not sure that I see the advantage of this approach though. With my code I get three 1D lists transferred to Python, which is quite convenient. I guess with a large rectangular array your way would be better though. Are there other advantages that I’m missing?

LikeLike

the original line had “If (VType And VT_BYREF) 0 Then” (i.e., not equal to zero), so your

change is equivalent. In reality, I think that in the present context it’s even safe to delete those

three lines. Regarding your question:

1) as you guess, for a large dense matrix definitively there should be a speed advantage. In python it is quite easy to restore the dense matrix, something like (on the premise that i’m NOT a python expert)

x = [1.0, 2.0, 3.0, 4.0, 5.0, 6.0]

A=np.reshape(asfortranarray(x,dtype=’f8′),(3,-1),order=’F’)

(it makes a 3 x 2 matrix).

2) Similarly, if you need three vectors, i think you can get them in the python side, and possibly the whole process might be faster than the three calls;

3) This approach can give some speed advantage in any vba code, in cases where it is appropriate to process a matrix in vba with a single loop and a single index (add a scalar to the whole matrix, etc.) Something that it was already possible in the old vb6, just blanking the check on the array indexes and using just one index, but that is impossible in vba.

Is it worth? difficult to answer, maybe it is an option to have just in case.

A final comment on your interface to ExcelPython: it is perfect for spreadsheets formulas, as it works on the sequence

range -> variant -> py object -> python processing -> py object -> variant

but this is overkill for vba programmnig, where i’d like to have also internal routines like

py object -> python processing -> py object

in vba, just use

dim A

set A = ….. (py call)

and then the object A is alive and it can be used as such in other vba/ExcelPython processing. I think this “double” interface might increase the appeal of the whole thing.

Best regards, maurizio

LikeLike