View Single Post
Old 10-18-2005, 06:23 PM
NateO's Avatar
NateO NateO is offline

Forum Leader
* Expert *
Join Date: Jun 2004
Location: Minneapolis MN - deceased
Posts: 2,483

Originally Posted by Diurnal
Also, do you think it would help performance to use a long array instead of a variant to hold the look up data?
This was a timely kick in the right direction.

Why am I mucking around with a Variant Array, here?

I'm not sure I like the prospect of stacking an array of longs, but, seeing as I am talking about working with a Byte Array, why not go Byte Array to Byte Array? Just not seeing the forest through the trees...

First, a few tests to make sure my logic is binding tightly:

Sub tstBArrStack() Dim b() As Byte Let b = "Test" Debug.Print ChrW$(b(1 * 2 - 2)); ChrW$(b(2 * 2 - 2)); _ ChrW$(b(3 * 2 - 2)); ChrW$(b(4 * 2 - 2)) End Sub Sub tstBArrStack2() Dim b(0 To 7) As Byte, s As String b(0) = 84: b(2) = 101 b(4) = 115: b(6) = 116 Let s = b Debug.Print s End Sub

And the revised function:

Public Function rndStr(ByRef StrLength As Long) As String Dim b() As Byte, keyArr() As Byte Dim i As Long Let keyArr = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" ReDim b(1 To StrLength * 2) For i = 1 To StrLength * 2 Step 2 Let b(i) = keyArr((Int((62 - 1 + 1) * Rnd + 1)) * 2 - 2) Next Let rndStr = b End Function
This is much faster. If I run this:

Option Explicit Public Declare Function QueryPerformanceFrequency _ Lib "kernel32.dll" ( _ lpFrequency As Currency) As Long Public Declare Function QueryPerformanceCounter _ Lib "kernel32.dll" ( _ lpPerformanceCount As Currency) As Long Private Sub TimerTime() Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency Dim Overhead As Currency, a As String, i As Long QueryPerformanceFrequency Freq QueryPerformanceCounter Ctr1 QueryPerformanceCounter Ctr2 Overhead = Ctr2 - Ctr1 ' determine API overhead QueryPerformanceCounter Ctr1 ' time loop For i = 1 To 1000 Let a = rndStr(16) 'Let a = rndStr2(16) Next i QueryPerformanceCounter Ctr2 Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq End Sub
On an average of 30 times, it clocks in at 0.0088 Seconds, as opposed to 0.010 seconds for the mid & concatenate approach, and the performance gain should grow with Str size. This does speak to the OPs question.

Looking at your revised Mid$() approach, I would think it would be faster, but I haven't tested it yet...
Nate Oliver
Microsoft Excel MVP

Last edited by NateO; 10-18-2005 at 06:57 PM. Reason: Removed garbage Variable on test procedure.
Reply With Quote