Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Matrix class
I had never realized that I could use those functions with VBA arrays.
Thanks a lot, Harlan! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a value out of a Matrix | Excel Discussion (Misc queries) | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
Matrix Help | Excel Worksheet Functions | |||
Matrix | Excel Worksheet Functions | |||
RaiseEvent from a class contained in a 2nd class collection? | Excel Programming |