I took a look at your stuff, Chris. – (Originally I was slightly confused by your “
UW.” and
Attribute VB_Name = "Module1" stuff. - But I figured it was some Class Object OOP codswallop to just get rid of, and your
book1.xls workbook confirmed that, :)
Interesting that you have your own split thing.
I remember originally thinking when I started with Excel that if there are in-built functions then you should always use them, and so keep up to date and if they add more, then, great, use them as well and keep update and getting the latest version of Excel.
That initial idea/ opinion of mine, was, because I kind of figured that whoever makes these functions is smart and that whatever low level stuff he used makes the thing much more efficient than any high level VBA UDF I could ever come up with would be.
That way of thinking still makes the most common sense to me, but experience has taught me otherwise, and I am much more inclined to go for keeping some stable earlier version and, at least to some extent, make my own UDFs. I am not sure how or why it comes about, but it seems that the newer the stuff is, the more messed up problems seem to occur.
(But looking on the bright side, without that strange state of affairs, I would never be here! – I only stated because things were either broke or not working as good as I needed, and that I found they could.)
I think perhaps the built in Split is something I might use though, and have used a lot.
I, perhaps a bit naively, or in ignorance, thought it was a very old strings thing, and so therefore thought it would be perhaps stable and OK.
Also there is a third optional argument with
Split, often overlooked, which allows you to specify how many times from the left you use the separator . That has sometime been useful: Here is an example of that using the test data being considered in this thread. (
Its just intended by way of example to demonstrate the use of the 3rd argument in the VB Split function. The UDF from snb is better )
Code: Select all
Sub TestSptUn3rdArgumant() '
' Debug.Print SptUn3rdArgumant(ThisWorkbook.Worksheets.Item("Split").Range("A1").Value)
Debug.Print SptUn3rdArgumant("3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx")
End Sub
Public Function SptUn3rdArgumant(ByVal strIt As String) As String
Dim arrSpt() As String: Let arrSpt() = Split(strIt, " ", 4, vbBinaryCompare) 'This will give an array of 4 elents
' example "3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx"
Let SptUn3rdArgumant = arrSpt(3) ' -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx 1 dimensional array typically by default arte in base 0, so indexes are 0 1 2 3 So 3 will be the 4th element
Let SptUn3rdArgumant = Left(arrSpt(3), 2) ' - 1
' Or just
Let SptUn3rdArgumant = Left(Split(strIt, " ", 4, vbBinaryCompare)(3), 2)
End Function
' ______________________________________________-
My gut feel is that this sort of requirement is what Power Query is really good at. Unfortunately I expect it will be a few years if I ever get around to learning that. In the meantime, I am fortunate to know the likes of that Demon head character Mr hamster, ;) , who is one person I am glad to know that takes care of these things nicely.
'________________________________________________
Evaluate Range can be a nice solution as well for these things, and it’s always nice here to come in when someone else has done the hard work and done a formula, that is then easy to convert into an Evaluate Range solution: Example, this will do the complete range used as example in this Thread, by the Evaluate range way.
Code: Select all
Sub EvaluateRangeIt() ' https://eileenslounge.com/viewtopic.php?p=306160#p306160
' =IFERROR(TRIM(MID(A1,SEARCH("sold",A1)+LEN("SOLD"),3)),TRIM(MID(A1,SEARCH("bot",A1)+LEN("BOT"),3))) https://eileenslounge.com/viewtopic.php?p=306160#p306160
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets.Item("Split")
Dim vTemp As Variant
Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(A1,SEARCH(""sold"",A1)+LEN(""SOLD""),3)),TRIM(MID(A1,SEARCH(""bot"",A1)+LEN(""BOT""),3)))")
Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(" & Ws.Range("A1").Address & ",SEARCH(""sold""," & Ws.Range("A1").Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Ws.Range("A1").Address & ",SEARCH(""bot""," & Range("A1").Address & ")+LEN(""BOT""),3)))")
Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(A1,SEARCH(""sold"",A1:A2)+LEN(""SOLD""),3)),TRIM(MID(A1:A2,SEARCH(""bot"",A1:A2)+LEN(""BOT""),3)))")
Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(" & Ws.Range("A1:A2").Address & ",SEARCH(""sold""," & Ws.Range("A1:A2").Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Ws.Range("A1:A2").Address & ",SEARCH(""bot""," & Range("A1:A2").Address & ")+LEN(""BOT""),3)))")
Dim Rng As Range: Set Rng = Ws.Range("A1:A2")
Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(" & Rng.Address & ",SEARCH(""sold""," & Rng.Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Rng.Address & ",SEARCH(""bot""," & Range("A1:A2").Address & ")+LEN(""BOT""),3)))")
Let Ws.Range("F1:F2").Value = vTemp
Let Ws.Range("F1:F2").Value = Ws.Evaluate("=IFERROR(TRIM(MID(" & Rng.Address & ",SEARCH(""sold""," & Rng.Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Rng.Address & ",SEARCH(""bot""," & Range("A1:A2").Address & ")+LEN(""BOT""),3)))")
End Sub
Alan
You do not have the required permissions to view the files attached to this post.