Some extended notes for this main forum post
https://eileenslounge.com/viewtopic....a7d27b#p315620
https://eileenslounge.com/viewtopic.php?p=315620&sid=922f8eb190547f727ca48c954aa7d27b#p315620
This is post
https://www.excelfox.com/forum/showt...l=1#post240923
https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24093&viewfull=1#post24093
snb's offerings, for Word VBA / VBA alternative for App Match of Excel VBA
Code:
Sub M_snb_000() ' https://eileenslounge.com/viewtopic.php?p=315620#p315620
sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr gg_ss hh_tt ii_uu jj_vv kk_ww")
c01 = "dd"
MsgBox Split(Filter(sp, c01)(0), "_")(1)
End Sub
Similar to the last offering , its not working case insensitive for the key word, and for no match it will not work, in this case it will error
This time we have a simple modification to get the case insensitive, changing in the Filter( ) function,
Compare:=vbBinaryCompare
, to
Compare:= vbTextCompare
To stop the error and give me a "" instead if no match is found, I will once again use my If
InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
, for ease of comparison.
( The coding also has the feature of working if only a portion of the key word(s) are selected )
What’s the basic idea
The use of the Filter( ) function gives a very nice simple way to get at the pair, from which it is child’s play to get the second bit, the URL.
This coding also takes the pair as a pair, which is preferable for me.
Here is a full open ed up code version of the basic snb offering
Code:
Sub SplitFilterM_snb_000() ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094
Dim strItAll As String, strEileen As String, strFox As String
Let strEileen = "Eileen's Lounge_https://eileenslounge.com/app.php/portal, eileenslounge_https://eileenslounge.com/app.php/portal, The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947, "
Let strFox = "Excel Fox_https://www.excelfox.com/forum/forum.php, excelfox_https://www.excelfox.com/forum/forum.php, "
Let strItAll = strEileen & strFox
Dim SptstrItAll() As String
Let SptstrItAll() = Split(strItAll, ", ", -1, vbBinaryCompare) ' 1 D array of pairs like eileenslounge_https://eileenslounge.com/app.php/portal The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,
Dim Excample As String
Let Excample = "ox" ' an example word key
Dim FltIt As String ' This should return the wanted pair - The Filter( ) function returns a subset array, ( of one element in this case ) , the element will be that incuding the search text, Excample The (0) is the first element , the only one
Let FltIt = Filter(SptstrItAll(), Excample, Include:=True, Compare:=vbBinaryCompare)(0)
Dim wantedURL As String
Let wantedURL = Split(FltIt, "_", -1, vbBinaryCompare)(1): Debug.Print wantedURL ' gives https://www.excelfox.com/forum/forum.php the second element of the found pair string is the URL
End Sub
This next version puts it in a form more comparable with my full instr version, Sub InstrIt()
Code:
Sub SplitFilter() ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094 https://eileenslounge.com/viewtopic.php?p=315620#p315620
Rem 0 The text you selected
Dim SelTxt As String
Let SelTxt = Selection.Text ' A text I highlighted in Word
Rem 1 Some groups of name_URL pairs
Dim strItAll As String, strEileen As String, strFox As String
Let strEileen = "Eileen's Lounge_https://eileenslounge.com/app.php/portal, eileenslounge_https://eileenslounge.com/app.php/portal, The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947, "
Let strFox = "Excel Fox_https://www.excelfox.com/forum/forum.php, excelfox_https://www.excelfox.com/forum/forum.php, "
Let strItAll = strEileen & strFox
Dim SptstrItAll() As String
Let SptstrItAll() = Split(strItAll, ", ", -1, vbBinaryCompare) ' 1 D array of pairs like eileenslounge_https://eileenslounge.com/app.php/portal The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,
Rem 2 Find the URL but this will error if there is not one
Dim FltIt As String ' This should return the wanted pair - The Filter( ) function returns a subset array, ( of one element in this case ) , the element will be that incuding the search text, Excample The (0) is the first element , the only one
Let FltIt = Filter(SptstrItAll(), SelTxt Include:=True, Compare:=vbTextCompare)(0)
Dim strURL As String
Let strURL = Split(FltIt, "_", -1, vbBinaryCompare)(1)
Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub
And here is TLDR version to try and make a better comparison to my shortest version so far, Sub BBCodeTagsURL()
Code:
Sub SplitFilter_TLDR() ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24094&viewfull=1#post24094 https://eileenslounge.com/viewtopic.php?p=315620#p315620
Rem 0 The text you selected
Dim SelTxt As String
Let SelTxt = Selection.Text ' A text I highlighted in Word
Rem 1 Some groups of name_URL pairs
Dim strItAll As String, strEileen As String, strFox As String
Let strEileen = "Eileen's Lounge_https://eileenslounge.com/app.php/portal, eileenslounge_https://eileenslounge.com/app.php/portal, The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947, "
Let strFox = "Excel Fox_https://www.excelfox.com/forum/forum.php, excelfox_https://www.excelfox.com/forum/forum.php, "
Let strItAll = strEileen & strFox
'Dim SptstrItAll() As String
' Let SptstrItAll() = Split(strItAll, ", ") ' 1 D array of pairs like eileenslounge_https://eileenslounge.com/app.php/portal The Windows Clipboard,_https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,
Rem 2 Find the URL if there is one
Dim strURL As String
If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
Let strURL = Split(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)(0), "_")(1)
Else
Let strURL = ""
End If
Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub
Conclusions
The main coding line
Split(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)(0), "_")(1)
looks nice compared with mine
Mid(strItAll, InStr(InStr(1, strItAll, SelTxt, vbTextCompare), strItAll, "http", vbBinaryCompare), InStr(InStr(InStr(1, strItAll, SelTxt, vbTextCompare), strItAll, "http", vbBinaryCompare), strItAll, ",", vbBinaryCompare) - InStr(InStr(1, strItAll, SelTxt, vbTextCompare), strItAll, "http", vbBinaryCompare))
In addition, I like the way one enters an additional key word_Url pair in one go
Edit: A slight modification in a follow up post, … ( https://eileenslounge.com/viewtopic....315705#p315705 )
This,
Code:
sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr gg_ss hh_tt ii_uu jj_vv kk_ww")
c01 = "dd"
MsgBox Split(Filter(sp, c01)(0), "_")(1)
, Changed to
Code:
sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr DD_ss hh_tt Dd_uu jj_vv kk_ww")
c01 = "DD"
c02 = ""
sn = Filter(sp, c01,,1)
If UBound(sn) > -1 Then c02 = Split(Filter(sp, c01,,1)(0), "_")(1)
MsgBox c02
For consistency / less confusingly comparison/ and to make the comparisons generally that I am doing a bit easier, we can say you changed this ,
Code:
sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr gg_ss hh_tt ii_uu jj_vv kk_ww")
c01 = "dd"
MsgBox Split(Filter(sp, c01)(0), "_")(1)
, to this
Code:
' c02 = ""
sp = Split("aa_mm bb_nn cc_oo dd_pp ee_qq ff_rr DD_ss hh_tt Dd_uu jj_vv kk_ww")
c01 = "DD"
If UBound(Filter(sp, c01, , 1)) > -1 Then
MsgBox Split(Filter(sp, c01, , Compare:=vbTextCompare)(0), "_")(1) ' See Settings https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function
End If
So you
_(i) use the Compare:=vbTextCompare in the filter function as I did to get the case insensibility,
_(ii) The If UBound(Filter(sp, c01, , 1)) > -1 is effectively an alternative to my If InStr(1, strItAll, SelTxt, vbTextCompare) > 0
Specifically in my TLDR versions , this line would be that alternative, If UBound(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)) > -1
_(iiI) you noticed, as SpeakEasy did, that I had an unnecessary Else bit to make the URL string "" for no match, since the variable would be already at that "" ( I am not sure why you did the c02 = "" though? Perhaps because as you don’t generally declare your variables. Whilst that does not seem to give us problems in the demo coding so far, maybe it could in some other variation of the coding – I mean there might be a situation where the use of an undefined variable might cause Excel not to take it as a zero length string, "" , but rather, it might take it as something else which might then chuck a spanner in the works?)
Bookmarks