VBA Matrix class
 

Has anyone implemented a Matrix class that lets you refer to rows and
columns by their header, extract vectors corresponding to individual
rows or columns, get all of the data in a single variant array, etc?
Any help would be most appreciated.

VBA Matrix class
 

R Avery wrote...
Has anyone implemented a Matrix class that lets you refer to rows and
columns by their header, extract vectors corresponding to individual
rows or columns, get all of the data in a single variant array, etc?

Unclear it'd be all that beneficial, and it's sure to be slower than
the alternatives. If you mean nothing but 2D arrays with top row and
left column containing labels (so the top-left entry would be unused),
you could fetch rows with

n = Application.Match(YourColValHere,
Application.Index(YourArrayRefHere, 1, 0), 0)

ResultingColVector = Application.Index(YourArrayRefHere, 0, n)

and similarly for the other subarrays you mentioned. So the answer is
that Application.Index or Application.WorksheetFunction.Index already
provide the array slicing functionality, and Application.Match (don't
use Application.WorksheetFunction.Match since errors it could throw
don't seem to be trappable by On Error) provides the matching
functionality.

VBA Matrix class
 

I had never realized that I could use those functions with VBA arrays.
Thanks a lot, Harlan!

VBA Matrix class
 

R Avery wrote:

I had never realized that I could use those functions with VBA arrays.
Thanks a lot, Harlan!
One thing to be aware of: those functions don't work on arrays of more
than 5461 elements in versions of Excel through xl2000.

Alan Beban

VBA Matrix class
 

I have conducted my own speed tests using
Application.Worksheetfunction.Index, and it is slower than a custom
function by a factor of 1.4 to 30, depending on the size of the array.

Does the Index function offer any benefits over what my ArraySlice
function (other than the fact that I would need to add code to make it
handle Rows as well)?

Also, are there any builtin functions that can return a subset of
columns from the original array? For example, if I want to get a
variant array containing only columns 2, 3 and 5.




Sub sldkgghlkh()
Dim x As Variant, vnt As Variant
Dim i As Long
Dim myTimer As New clsTimer

x = Selection

Debug.Print "Application.Index"
myTimer.StartTimer
For i = 1 To 10000
vnt = Application.WorksheetFunction.Index(x, 3)
Next i
myTimer.StopTimer
Debug.Print myTimer.PerformanceString


Debug.Print "Custom Function"
myTimer.StartTimer
For i = 1 To 10000
vnt = ArraySlice(x, 3)
Next i
myTimer.StopTimer
Debug.Print myTimer.PerformanceString
End Sub



Private Function ArraySlice(varArray As Variant, ColNum As Long) As Variant
Dim vntCol As Variant
Dim i As Long

ReDim vntCol(LBound(varArray, 1) To UBound(varArray, 1))
For i = LBound(varArray, 1) To UBound(varArray, 1)
vntCol(i) = varArray(i, ColNum)
Next i
ArraySlice = vntCol
End Function

VBA Matrix class
 

R Avery wrote:

I have conducted my own speed tests using
Application.Worksheetfunction.Index, and it is slower than a custom
function by a factor of 1.4 to 30, depending on the size of the array.

I didn't understand your myTimer code and got an error message with
Dim myTimer As New clsTimer, so I ran it with the following and
consistently found the Custom Function 50% slower than the built-in
function.

Sub sldkgghlkh()
Dim x As Variant, vnt As Variant
Dim i As Long
'Dim myTimer As New clsTimer

x = Selection

Debug.Print "Application.Index"
starttime = Now()
For i = 1 To 100000
vnt = Application.WorksheetFunction.Index(x, 3)
Next i
Debug.Print (Now() - starttime) * 86400


Debug.Print "Custom Function"
For i = 1 To 100000
vnt = ArraySlice(x, 3)
Next i
Debug.Print (Now() - starttime) * 86400
End Sub

Alan Beban
Sub sldkgghlkh()
Dim x As Variant, vnt As Variant
Dim i As Long
'Dim myTimer As New clsTimer

x = Selection

Debug.Print "Application.Index"
starttime = Now()
For i = 1 To 100000
vnt = Application.WorksheetFunction.Index(x, 3)
Next i
Debug.Print (Now() - starttime) * 86400


Debug.Print "Custom Function"
For i = 1 To 100000
vnt = ArraySlice(x, 3)
Next i
Debug.Print (Now() - starttime) * 86400
End Sub



Sub sldkgghlkh()
Dim x As Variant, vnt As Variant
Dim i As Long
Dim myTimer As New clsTimer

x = Selection

Debug.Print "Application.Index"
myTimer.StartTimer
For i = 1 To 10000
vnt = Application.WorksheetFunction.Index(x, 3)
Next i
myTimer.StopTimer
Debug.Print myTimer.PerformanceString


Debug.Print "Custom Function"
myTimer.StartTimer
For i = 1 To 10000
vnt = ArraySlice(x, 3)
Next i
myTimer.StopTimer
Debug.Print myTimer.PerformanceString
End Sub



Private Function ArraySlice(varArray As Variant, ColNum As Long) As Variant
Dim vntCol As Variant
Dim i As Long

ReDim vntCol(LBound(varArray, 1) To UBound(varArray, 1))
For i = LBound(varArray, 1) To UBound(varArray, 1)
vntCol(i) = varArray(i, ColNum)
Next i
ArraySlice = vntCol
End Function

VBA Matrix class
 

'THIS IS THE clsTimer class that I use for speed tests.


Private Declare Function timeGetTime Lib "winmm.dll" () As Long


' Properties.
Private lngBeginTime As Long
Private lngEndTime As Long

Private blnRunning As Boolean


'################################################# #########################
' Read properties.
'################################################# #########################

Public Property Get BeginTime() As Long
BeginTime = lngBeginTime
End Property

Public Property Get EndTime() As Long
EndTime = lngEndTime
End Property

Public Property Get Running() As Boolean
Running = blnRunning
End Property

'################################################# #########################
' Write properties.
'################################################# #########################


Public Property Let BeginTime(ByVal Value As Long)
lngBeginTime = Value
End Property

Public Property Let EndTime(ByVal Value As Long)
lngEndTime = Value
End Property

Public Property Let Running(ByVal Value As Boolean)
blnRunning = Value
End Property

'################################################# #########################
' Methods.
'################################################# #########################


Public Sub StartTimer()
blnRunning = True
lngBeginTime = timeGetTime()
End Sub

Public Sub StopTimer()
lngEndTime = timeGetTime()
blnRunning = False
End Sub


Public Function PerformanceString() As String
PerformanceString = Me.SecondsElapsed & " seconds elapsed."
End Function

Public Function TimeElapsed() As Long
TimeElapsed = lngEndTime - lngBeginTime
End Function

Public Function SecondsElapsed() As Double
SecondsElapsed = (lngEndTime - lngBeginTime) / 1000
End Function

VBA Matrix class
 

Whoops!

Although I still haven't got my head around the speed tests that you
use, my tests show the Custom Function as twice as fast as the built-in.
Sorry for the misinformation.

Alan Beban

R Avery wrote:
'THIS IS THE clsTimer class that I use for speed tests.


Private Declare Function timeGetTime Lib "winmm.dll" () As Long


' Properties.
Private lngBeginTime As Long
Private lngEndTime As Long

Private blnRunning As Boolean


'################################################# #########################
' Read properties.
'################################################# #########################

Public Property Get BeginTime() As Long
BeginTime = lngBeginTime
End Property

Public Property Get EndTime() As Long
EndTime = lngEndTime
End Property

Public Property Get Running() As Boolean
Running = blnRunning
End Property

'################################################# #########################
' Write properties.
'################################################# #########################


Public Property Let BeginTime(ByVal Value As Long)
lngBeginTime = Value
End Property

Public Property Let EndTime(ByVal Value As Long)
lngEndTime = Value
End Property

Public Property Let Running(ByVal Value As Boolean)
blnRunning = Value
End Property

'################################################# #########################
' Methods.
'################################################# #########################


Public Sub StartTimer()
blnRunning = True
lngBeginTime = timeGetTime()
End Sub

Public Sub StopTimer()
lngEndTime = timeGetTime()
blnRunning = False
End Sub


Public Function PerformanceString() As String
PerformanceString = Me.SecondsElapsed & " seconds elapsed."
End Function

Public Function TimeElapsed() As Long
TimeElapsed = lngEndTime - lngBeginTime
End Function

Public Function SecondsElapsed() As Double
SecondsElapsed = (lngEndTime - lngBeginTime) / 1000
End Function

VBA Matrix class
 

NOTE: There was a bug in the code i posted. one line should read:
vnt = Application.WorksheetFunction.Index(x, 0, 3)

However, this does not affect the fact that the custom function performs
much faster.

I get results like the following quite frequently, when running this
code. What version of XL are you using?




Application.Index
15.7 seconds elapsed.
Custom Function
0.123 seconds elapsed.

VBA Matrix class
 

If you make a Class Module, and dump all of the clsTimer code in there,
then you can use the clsTimer object. You don't have to know how it
works, but it is pretty simple. Just use .StartTimer, .StopTimer, and
..PerformanceString for results.

VBA Matrix class
 

R Avery wrote:

If you make a Class Module, and dump all of the clsTimer code in there,
then you can use the clsTimer object. You don't have to know how it
works, but it is pretty simple. Just use .StartTimer, .StopTimer, and
.PerformanceString for results.
Thanks; I'm using xl2000. When I ran the speed tests I had a brain
lapse and forgot that I didn't start the timer over again, so when I got
6 seconds for the built-in and 9 seconds for the Custom I attributed the
whole 9 seconds (rather than just 3, of course) to the Custom.

Thanks again,
Alan Beban

VBA Matrix class
 

R Avery wrote:

. . . Also, are there any builtin functions that can return a subset of
columns from the original array? For example, if I want to get a
variant array containing only columns 2, 3 and 5.

Well, though somewhat kludgy you could always dump the array to the
worksheet, extract the three columns on the worksheet, and retransfer
them to an array. No guarantees that it's faster than a UDF.

E.g., with Sheets(9) active and without declarations(watch the word wrap)

Sub sldkgghlkh()
Set rng = Sheets(7).Range("b1:f24") 'The first two lines are
arr = rng 'arbitrary to load array
Set tempRange = Range("A1").Resize(UBound(arr), UBound(arr, 2))
tempRange.Value = arr
Set tempRange2 = Range("H1").Resize(UBound(arr), 3)
tempRange2.FormulaArray = "=INDEX(" & tempRange.Address & ",ROW(1:"
& UBound(arr) &"),{2,3,5})"
arr = tempRange2
tempRange.ClearContents 'To tidy up
tempRange2.ClearContents 'To tidy up
Sheets(8).Range("a1:c" & UBound(arr)).Value = arr 'This
is just to 'check to confirm
'transfer to array
End Sub

Alan Beban

VBA Matrix class
 

R Avery wrote:

I have conducted my own speed tests using
Application.Worksheetfunction.Index, and it is slower than a custom
function by a factor of 1.4 to 30, depending on the size of the array.

Does the Index function offer any benefits over what my ArraySlice
function (other than the fact that I would need to add code to make it
handle Rows as well)?

Also, are there any builtin functions that can return a subset of
columns from the original array? For example, if I want to get a
variant array containing only columns 2, 3 and 5.

You might want to take a look at the freely downloadable file at
http://home.pacbell.net/beban, although speed of execution is not their
forte. It includes RowVector, ColumnVector, SubArray, and
ReplaceSubArray procedures, along with some others that might give you
some ideas.

Alan Beban