Dear Lisa
First of all I have no idea how my post formatted itself. It just happened.
In your time comparison in the Split section you are doing three time consuming and unnecessary assignments that you are not doing in the second test loop, and in the second section you should be populating array elements to make a proper comparison as Split returns an array, not individual variables. Importantly, also you should be using the native type of the Split function. Thus :-
Sub TestSplitWithInstr()
Dim sStringToSplit As String
'Dim vSplitString() As String ' try this
Dim vSplitString As Variant
Dim sRightPart As String
Dim nLoopIndex As Long
Dim nDelimiterPosition As Integer
Dim fNow As Double
Const COMMA = ","
Const ITERATIONS = 100000
Debug.Print "----------------------------"
sStringToSplit = "subWhereArewe,zmWhereAreWe,vbacodecodeaddin(codecodea.xla)"
fNow = Timer
For nLoopIndex = 0 To ITERATIONS
vSplitString = Split(sStringToSplit, COMMA)
Next nLoopIndex
Debug.Print "Split " & Timer - fNow, vSplitString(0), vSplitString(1), vSplitString(2)
'
fNow = Timer
ReDim vSplitString(0 To 2)
For nLoopIndex = 0 To ITERATIONS
nDelimiterPosition = InStr(sStringToSplit, COMMA)
vSplitString(0) = Mid(sStringToSplit, 1, nDelimiterPosition - 1)
sRightPart = Mid(sStringToSplit, nDelimiterPosition + 1)
nDelimiterPosition = InStr(sRightPart, COMMA)
vSplitString(1) = Mid(sRightPart, 1, nDelimiterPosition - 1)
vSplitString(2) = Mid(sRightPart, nDelimiterPosition + 1)
Next nLoopIndex
Debug.Print "Code " & Timer - fNow, vSplitString(0), vSplitString(1), vSplitString(2)
End Sub
The Split code takes 1.5 seconds to do 1000000 iterations and the InStr code takes 1 second if the vSplitString is declared as a String. However if vSplitString is properly declared as a Variant then both sections of code take about 1 second, with the Split code being marginally faster. This is because Split returns a Variant and there is an overhead in converting that to a String as in the original version.
However your code assumes that each string it is presented with always has exactly three substrings, and therefore is not generic and fails if presented with only two substrings. In Tayyab 's current exercise he does not know how many words he has in his sentences so you would have to put in much more code to allow for this, and as well do something ingenious with the slow Redim Preserve inside the loop. How do you get vSplitString(3) ?
All this makes the one line simplicity of "vSplitString = Split(sStringToSplit, COMMA)" very attractive even if were slower..
As for your comment about my Application.WorksheetFunction.Trim line, yes you are quite right. I said there might be issues and this is one of them. It is more elegant to use Replace inside a loop until all double spaces are eliminated. The fact that the Excel TRIM behaves differently from the VBA Trim is an example of the Microsoft developers being over anxious to please and getting it wrong, as this new behavior should really have been given a new function name to avoid confusion. This brings me nicely onto the link you sent :- http://vb.mvps.org/hardweb/mckinney2b.htm
The item comparing the inferior VBScript dictionary object to the VB collection is relevant because it has been mentioned in this thread. It also illustrates the point above that the developers are trying too hard to please. However, the 'Split sucks' section is only a rant about the simplicity of the Split function which in my view is good. If you look at how .NET does it he got what he wanted from the developers, but have a look at the Help.
Your comment on naming conventions is fascinating because you DO have a naming convention.
Yes I would be interested to see your project, please send it.
Regards
Derek
+++
>________________________________
> From: "1z@compuserve.com" <1z@compuserve.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Saturday, 25 August 2012, 18:25
>Subject: Re: [ExcelVBA] Re: specfic character pick
>
>
>
>
>Nice code Derek!
>
>I usually try and make my VBA "generic" to other applications so I wouldn't personally use Application.WorksheetFunction.Trim... but this is in Excel so what the heck.
>
>> I gave up using single letter variable names
>This has bit me on the bum so many times!!! For quite a while now I've gotten into the routine of prefixing my variables too... ie: wiat for it... a naming convention!!! ( People drop to the floor in horror!! )... :-)
>
>> Split... never noticed any speed issues with it
>I have an ongoing project up on thecodecage that I use split in a lot. I did some timing though and it was slower than using own rolled code. The project looks at code lines in VBA projects and splitting them all meant a *lot* of splits.looking for variables in thousands of lines of code. If you're intrerested I can email you the current working project but it's a bit old now... from before the silly season.
>
>I used this code to test Split just before posting this...
>
>Sub subtestSplitInstr()
>
>Dim slA() As String
>Dim slB As String
>Dim slC As String
>Dim ilN As Long
>Dim clTimer As CHiResTimer
>Dim slMessage As String
>Dim slA1 As String
>Dim slA2 As String
>Dim slA3 As String
>Dim ilCPos As Integer
>
>Set clTimer = New CHiResTimer ' Inserted.
>
>slB = "subWhereArewe,zmWhereAreWe,vbacodecodeaddin(codecodea.xla)"
>clTimer.StartTimer ' Inserted.
>
>For ilN = 0 To 100000
>slA = Split(slB, ",")
>slA1 = slA(0)
>slA2 = slA(1)
>slA3 = slA(2)
>Next ilN
>
>Debug.Print slA1
>Debug.Print slA2
>Debug.Print slA3
>
>slA1 = ""
>slA2 = ""
>slA3 = ""
>
>clTimer.StopTimer ' Inserted.
>slMessage = "Time Elapsed :" & CDec(clTimer.Elapsed) ' Inserted.
>Debug.Print slMessage ' Inserted.
>
>clTimer.StartTimer ' Inserted.
>For ilN = 0 To 100000
>ilCPos = InStr(slB, ",")
>slA1 = Mid(slB, 1, ilCPos - 1)
>slC = Mid(slB, ilCPos + 1)
>ilCPos = InStr(slC, ",")
>slA2 = Mid(slC, 1, ilCPos - 1)
>slA3 = Mid(slC, ilCPos + 1)
>Next ilN
>
>Debug.Print slA1
>Debug.Print slA2
>Debug.Print slA3
>clTimer.StopTimer ' Inserted.
>slMessage = "Time Elapsed :" & CDec(clTimer.Elapsed) ' Inserted.
>Debug.Print slMessage ' Inserted.
>
>Set clTimer = Nothing ' Inserted.
>slMessage = "END Procedure :subtestSplitInstr Module :amTerst" ' Inserted.
>' *********************************************************************
>Debug.Print slMessage ' Inserted.
>End Sub
>
>I *hate* it when copy and paste removes the formatting! How did you get your code "formatted" Derek??
>
>This is the result from the immediate window...
>
>subWhereArewe
>zmWhereAreWe
>vbacodecodeaddin(codecodea.xla)
>Time Elapsed :1.68985315636717
>subWhereArewe
>zmWhereAreWe
>vbacodecodeaddin(codecodea.xla)
>Time Elapsed :0.212253057593163
>END Procedure :subtestSplitInstr Module :amTerst
>
>The times elapsed tell their own story.
>
>The CHiResTimer class is in my project. The comments "' Inserted" are because I used code from my project to insert timing code. I also used code from my project to insert the other Debug.Print lines... :-)
>
>It's also worth having a look at http://vb.mvps.org/hardweb/mckinney2b.htm and doing a Ctrl F for "split sucks". I don't nessesarilly agree with him but he does seem knowledgable and the book is from VB5 times... quite a while ago.
>
>Anyway, I now try and keep my splits to a minimum but I do still use it quite a bit just because it's convenient.
>
>Lisa
>
>Sent: Fri, Aug 24, 2012 3:18 pm
>Subject: Re: [ExcelVBA] Re: specfic character pick
>
>Greetings
>
>I think that Tayyab wants a user Defined Function like this :-
>
>Public Function MatchWordInSentence(ByVal vSentence As Variant) As Variant
>Dim nCharacterPosition As Long, vSplitSentence As Variant, vMatchList As Variant
>Dim nOuterIndex As Long, nInnerIndex As Long
>Const SPACE = " "
>vMatchList = Array("lazy", "jumps", "over")
>vSentence = LCase(vSentence) ' hence ByVal
>For nCharacterPosition = 1 To Len(vSentence)
>Select Case Mid(vSentence, nCharacterPosition, 1)
>Case "a" To "z", "1" To "0"
>Case Else
>Mid(vSentence, nCharacterPosition, 1) = SPACE
>''''
>End Select
>Next nCharacterPosition
>vSplitSentence = Split(Application.WorksheetFunction.Trim(vSentence), SPACE)
>' this Trim removes internal multiple spaces
>For nOuterIndex = 0 To UBound(vSplitSentence) ' zero base
>For nInnerIndex = 0 To UBound(vMatchList) ' zero base
>If vSplitSentence(nOuterIndex) = vMatchList(nInnerIndex) Then
>MatchWordInSentence = vMatchList(nInnerIndex)
>Exit Function ' thus first match only
>End If
>Next nInnerIndex
>Next nOuterIndex
>MatchWordInSentence = "no match"
>End Function
>
>I did this in a hurry. It seems to work for me but there may be some issues. It
>demonstrates a principle anyway.
>
>A further refinement would be to get the vMatchList from a cell in the
>spreadsheet to make it more generic.
>
>By the way, Paul (I greet you well) and Dave G, I gave up using single letter
>variable names in about 1978 when I got my first Apple II. Take the plunge.
>
>Lisa, I use Split all the time and have never noticed any speed issues with it.
>
>Regards
>
>Derek +++
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar