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]
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