Hi Derek,
Wow!!!!!
That's so great that you took so much time to answer!! Thank you!!
I'm answering here so that other list members can see my reaction.
> my post formatted itself.
Do you copy and paste from the VBE or a different editor Derek?
This isn't all that important, but it *is* annoying and I'd like to have my code formatted too... jealous!
> three time consuming and unnecessary assignments
Agree... I'm going to recode the test and take them out of the Split code.
> you should be populating array elements
Okay... I'll do that as well.
> you should be using the native type of the Split function
That I don't understand. What do you mean by "native" please?
> if vSplitString is properly declared as a Variant
Okay.
> exactly three substrings
Okay.
> much more code
Not sure about that... I'm going to experiment a bit.
> slow Redim Preserve inside the loop
Now I'm *sure* that Redim Preserve definately is slow!!
Why inside the loop? If I put it outside both of the loops wouldn't that sort of level things out?
> very attractive even if were slower
LOL!! Agree. I think I said I still use it because it's convenient.
> you are quite right
Phew!!
> which in my view is good
Which? The rant or the simplicity? Not being funny there... I *think* you mean the simplicity... Just want to be sure.
> If you look at how .NET does it
Mmmmm been wanting to "do" .NET for a while now but it hasn't happened. Maybe I need a round tuit.
> you DO have a naming convention
Yup. And you know what... Bound to be some deviance sure but once you get into the habit it's not that difficult to keep up.
> please send it
I'm attaching it to a seperate email to your address.
It's also availiable on thecodecage forums for free along with a description of the project at http://www.thecodecage.com/forumz/view.php?pg=vbacleanup. You will have to register to download from there but there's a lot of good stuff across the office suite so maybe you would want to anyway.
As mentioned above, you've prompted me to play a bit. I'll post new timings and code when I've made alterations as suggested.
Regards
Lisa
Sent: Mon, Aug 27, 2012 1:37 pm
Subject: Re: [ExcelVBA] Re: specfic character pick
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
+++
[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