Excel WorksheetFunction Match in VBA, alternative function in Word VBA? (URL BB Code Tags with VBA)

[DocAElstein]

Hi
The example short coding below works OK for me, it will do, but it seems an inefficient roundabout way to do it. That is because I know some Excel VBA, but am less familiar with Word VBA, and could not find an equivalent pure Word VBA way to do it.
So I thought I would ask if anyone better at Word VBA has a better idea?

So what I am doing is highlighting a text in WORD, then running the coding. If that text is a web site listed in the macro, then it tells me the URL.

(In case it helps in the choice of a solution, the fuller picture of the requirement to which this is just the first part, is:
For example if I had in a word text …
Over at Eileenslounge
… then finally I want the text to be changed to make the BB code type thingy for the site:
Over at [url=https://eileenslounge.com/app.php/portal] Eileenslounge [/url]
The lists of URLs against (site) names will likely be added to and also the list will grow to include thing’s like post titles against their URL etc. so eventually the list will be very big. I would probably prefer to have the coding working on a small text selection rather than a large selection or full document

So this first bit is just matching a site name to it’s URL. I can have a go at doing the rest, but mentioned the fuller requirement just in case it makes a particular solution idea for this first bit more appropriate)


Thanks
Alan

Code: Select all

 ' Select a text in Word of, for example, a web site name, whose URL you want
Sub ExcelVBAMatchInWordVBA()  '   '  https://eileenslounge.com/viewtopic.php?f=26&t=40789
Rem 0  The text you selected
Dim SelTxt As String
 Let SelTxt = Selection.Text ' A text I highlighted in Word

Rem 1 Two arrays, one with web site names, and the other gives the corresponding URL in the same array position
Dim arrURL() As Variant, arrTxt() As Variant ' These arrays gives me s sort of list of  site name  agaist the  URL link  for it
 Let arrTxt() = Array("Eileen's Lounge", "eileenslounge", "Excel Fox", "excelfox")
 Let arrURL() = Array("https://eileenslounge.com/app.php/portal", "https://eileenslounge.com/app.php/portal", "https://www.excelfox.com/forum/forum.php", "https://www.excelfox.com/forum/forum.php")

Rem 2 A thing to enable me to use Excel VBA functions in Word VBA
Dim XL As Object
 Set XL = CreateObject("Excel.Application")

' 2b Get the URL corrsponding to a selectwd web site name using the Excel Match function
Dim MtchRes As Variant
 Let MtchRes = XL.match(SelTxt, arrTxt(), 0)  '  If  SelTxt  is found in  arrTxt()  then the Excel Match function will return the integer "position along" in the array of the match.  (The start left is position 1)
    If IsError(MtchRes) Then                  '  If no match is found, then a fatal error does not occur, - instead a vbError is returned, which we can test for   ( https://eileenslounge.com/viewtopic.php?p=297474#p297474 )
     MsgBox prompt:="No match found"
    Else
     MsgBox prompt:="The URL you want is  " & arrURL(MtchRes - 1)  '  -1 is because VBA arrays start at indicia 0, (in other words the start left is position 0)
    End If

 Set XL = Nothing '  Apparantly this is not needed in VBA, but just to be on the safe side, it does no harm
End Sub

[HansV]

Since you're using Excel to do the heavy lifting anyway, I'd create the lists of text strings and URLs in a two-column range on an Excel sheet. You can then use VLOOKUP to find the URL corresponding to a given string, if any.
Also, maintaining a range on an Excel sheet is easier than editing the arrays in the source code.

Otherwise, using Excel from Word is resource-intensive. As long as your arrays are not overly long, you might as well loop through arrTxt and if you find a match, get the corresponding element from arrURL and exit the loop.

[DocAElstein]

Thanks. Yes I was wondering about a better way to have a list to add to easily.
What I might not have made clear is that this would ideally have nothing to do with Excel. I would be preparing a forum post in Word, as I usually do. I already have short macros working conveniently from keyboard short cuts to add a lot of BB code tags to selected text. The final macro this is concerned with is for adding the URL BB Code tag around a selected word, either with the place where the URL goes empty, so I would add that bit manually
, or
, if it recognises the name in the list, then it includes it so gives me the complete URL BB Code thing


A scripting dictionary thing might be one alternative, but I was wondering if I might in ignorance missed some Word VBA function working similar to the Excel Match
, ( and it just occurred to me I should finally get it right and call it Excel or Excel worksheet function Match in VBA, not Excel VBA Match. I will edit the Thread ( Topic ) title..

A simple looping as you suggest might also be OK for me

The other thing is… The macros of this sort always go in my Normal document thing. I never see that document. I don’t know if it is one, but just invisible? If I could see it temporarily, put a 2 column table in it, and access that in some clever Listing Object or whatever there might be way??? – wild thoughts as I really know very little about Word

[HansV]

You should not put a list in your Normal template. Normal is the template used for new blank documents, so if you place a list in it, that list would end up in all your new documents.
You might save the list and macro in another template though, and store that template in your startup folder for Word (by default C:\Users\<username>\AppData\Roaming\Microsoft\Word\STARTUP). It will then be loaded as an add-in aka global template.

[DocAElstein]

Ok, thanks

[DocAElstein]

Here’s another way, - a bit of string manipulation. This allows to add further text pairs of a
name, URL,
, quite easily.

I think strings can be pretty enormous in length, ( - Chris told me a few times now that a computer is just a long bit of text anyway, so playing with string manipulation should be good, even if Mike ( Speakeasy ) told me once that string manipulation ‘aint efficient.)
This next offering allows a nice practical way to add pairs to and group the pairs – and if a string gets a bit big in the coding, then I can just start another one and tack it on to the final long string, strItAll .
( I don’t know if this is some poor ignorant man’s version of some efficient Hash table concept that I never understood, and / or I am just at the very start of re wheel invention of the dictionary concept…..

Code: Select all

 Sub InstrIt()  ' https://eileenslounge.com/viewtopic.php?p=315503#p315503
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

Rem 2 Find the URL if there is one
Dim strURL As String
        If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
    Dim Pos1 As Long ' Position of text
     Let Pos1 = InStr(1, strItAll, SelTxt, vbTextCompare)
    Dim Pos2 As Long ' Position of URL start
     Let Pos2 = InStr(Pos1, strItAll, "http", vbBinaryCompare)
    Dim Pos3 As Long ' Position of comma at end of URL
     Let Pos3 = InStr(Pos2, strItAll, ",", vbBinaryCompare)
     Let strURL = Mid(strItAll, Pos2, Pos3 - Pos2)
    Else
     Let strURL = ""
    End If
End Sub 

Just to clarify what I am doing. Here is a full coding that does what I want

Code: Select all

 Sub BBCodeTagsURL()  ' https://eileenslounge.com/viewtopic.php?p=315503#p315503
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

Rem 2 Find the URL if there is one
Dim strURL As String
        If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
     Let strURL = 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))
    Else
     Let strURL = ""
    End If

Rem 3 Make the BB Code Tag URL thing
    With Selection
     .Text = "[URL=" & strURL & "] " & .Text & " [/url]"
     .Collapse Direction:=wdCollapseEnd
     .Font.Color = wdColorAutomatic
    End With

End Sub

So what you do is select a text you want to put in a BB Code URL Tag pair thing, example:

Select a text which probably has an asoiciated URL.JPG

Now run the macro. You should then end up with this

Made a URL BB Code tag thing.JPG

( If you did not have that name in a
name, URL,
pair, then the URL bit would not be there and you would have to put it in manually.

I can probably go with that for now, or I might do a simple looping of arrays - that long pretty InStr code line is not always a nice thing to come back to later...

[SpeakEasy]

>A scripting dictionary thing might be one alternative

This is pretty much a textbook example of where a using a scripting dictionary would be a good solution

[DocAElstein]

Yes, I guess getting an item from its key is one thing a dictionary is about. That would probably knock the socks of my long Instr code line. But there would be the extra looping need to fill the dictionary.
Maybe the If Exists works better than my If Instr. Not sure I would notice the difference though.
I am still missing a simple way to have my full list in Word, although, whilst I realise that storing a lot of text in the VB Editor is a bit unconventional and not really the done thing, I never the less don’t mind doing it. Scroll bars mean I don’t have to see it much and can easily move around it. A simple copy paste can back up the data somewhere.

[SpeakEasy]

>But there would be the extra looping need to fill the dictionary

Surely no more so than to populate the strings

[DocAElstein]

In the unconventional way that my last coding suggestion , Sub BBCodeTagsURL() is,
the strings are there already. That coding just showed the idea, so maybe it was not obvious. In the practice, if I went with that idea permanently, then those strings would be quite long. As time went on I would tack on occasionally a pair to one of the main sub strings, ( In that example I had just two strings, one for each of two forum sites, ( but in the example I have two word possibilities that I might use in a text I write for the same basic forum link). So I would add on a pair like this manually
"………………………………..Word, URL Link, "
´, example - to refer to the Board Index here a Eileen's Lounge, in any text I write referring to that, I would likely use those two words
Board Index
, so I would, in this example, add this the next time I was going to add the BB URL tags manually
"........................... Board Index, https://eileenslounge.com/index.php, "
I would probably add it to the Eileen Lounge sub string, strEileen, but it could in any string or even added to the final total string, strItAll
_.___________________________________


I probably did not explain that so well. Let me try again with an example. Let’s say my last coding suggestion is my coding in use. Just pretend the initial strings are much bigger. Now let’s say, that yet again I want to refer someone to a post of yours, ( so as to stop them wanting to kill me for, in their opinion, posting nonsense, Lol), this one
https://www.eileenslounge.com/viewtopic.php?p=300947&sid=6302a9c8912f9440201471d79165cd88#p300947
https://www.eileenslounge.com/viewtopic ... 88#p300947

Now I generally prepare all my forum and similar postings in Word, - I have short cuts to short macros to add a lot of BB coding on selected words, including since a few days that last coding suggestion . Instead of constructing the BB code for that manually, as I still do for a few things, I go to my normal document coding, (which is a quick Alt + F11 so no big deal ) and I change this line

Code: Select all

 Let strEileen = "Eileen's Lounge, https://eileenslounge.com/app.php/portal, eileenslounge, https://eileenslounge.com/app.php/portal, " 

To this

Code: Select all

 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, "

So you see, I added another pair to the eileens lounge string- tacked it on the end, so no big effort.

Now, one day, and probably in the future again, when in word, I may have written this, whilst preparing a post
….. don’t blame me, SpeakEasy enlightened the world about the windows clipboard …….

So, now, because I modified the eileens lounge string, then from now on, I highlight the appropriate bit that I usually use in the BB code tag URL link, when giving you the blame

Blame Mike, - he told me.JPG

and hit my short cut, ( for the macro Sub BBCodeTagsURL() , I set it to Ctrl+Shift+U,L ), and then as if by magic I get this

Blame Mike, - he told me with URL BB Code tags.JPG

Constructing the final massive strings won’t be a hassle or great priority for me. What I will do from now on, just as I go along, and occasionally yet again refer to something that I coincidently remember that I already done a few times , then I will add the pair to the appropriate string


_.____________________

Of course, if I decide to do it differently, where some list is acccessed, to populate the strings, then as you say, i could populate the dictionary instead in about the same time. But for now, the strings are already there, always.

[SpeakEasy]

>So you see, I added another pair to the eileens lounge string- tacked it on the end, so no big effort.
You can do similar for a dictionary. Unless I am missing something.

>But for now, the strings are already there
Ah, there we go - technical debt ...

[snb]

Either:

Code: Select all

Sub M_snb()
   c00 = "aa bb cc dd ee ff gg hh ii jj kk"
   sp = Split("mm nn oo pp qq rr ss tt uu vv ww")
   
   c01 = "dd"
   MsgBox sp(UBound(Split(Split(c00, c01)(0))))
End Sub

or

Code: Select all

Sub M_snb_000()
   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

[DocAElstein]

Either:...or.....

Thanks,
.... for my sins, I took a good look at those two
Sub M_snb()
Sub M_snb_000()

They don’t quite fully do the job. A couple of things are missing
_(i) They are not case insensitive, - I probably did not make that too clear that I wanted that. ( I used vbTextCompare in some of my Instr( ) bits to achieve the case insensibility in my last coding versions. )
I modified your first offering with some UCase( ) bits to get the case insensibility, and for the second offering, it’s a bit easier as we can use vbTextCompare option in the Filter( ) bit
_(ii) I want the code tags to be put on empty if no match is found to the key word, like
[URL=] Key word [/url]
The first snb’s offering gives a wrong answer, and the second snb’s offering errors for when no matching key word is found.


But there is some useful ideas hidden in them. I made a few different versions which where based on those ideas.

The coding samples I put at the end here ( in next post ) , the TLDR versions, puts my last shortest offering and the snb offerings in a similar general form, so that makes comparing them a bit easier. The most notable difference shows up in the main code line in Rem 2, that is the bit that does the most important bit of matching the key word to the URL. So that line is something close to the original requirement of the Thread, which was that of some sort of Excel VBA worksheet function match alternative that will work in Word VBA
Here those three main code lines

Code: Select all

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)) 
Split(URLs, ",")(UBound(Split(Split(Wdkey, SelTxt)(0), ","))) 
Split(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)(0), "_")(1) 

The two snb’s lines look like a good improvement on my long Instr( )s code line.
The last coding in the next post, ( based on snb's second offering, using the last line above ), is my favourite so far, as it takes, ( as my last coding one did ) a new
key word_URL
pair, in that, or similar sort, of combined single string form. The second coding ( based on snb’s first ) requires the key word and the corresponding URL to be added separately, and if the strings get a bit bigger it might make it more difficult to add new pairs as I might get mixed up and put them in the wrong place. ( The worse thing I can do if I get mixed up when using the last coding ( based on snb’s second ) , is to put that single key word_URL text in the wrong string. That just messes up organising the sub strings based on some site, Theme or other criteria grouping, but does not stop the coding working as I want, ( since the coding works on a final string which has combined all the sub strings.

Alan

Ref:
Mine https://www.excelfox.com/forum/showthre ... #post24092 (Post 103)
Snb’s https://www.excelfox.com/forum/showthre ... #post24093 (Post 104)
Snb’s https://www.excelfox.com/forum/showthre ... #post24102 (Post 104)

Three Codings in next Post ( and in uploaded file )

[DocAElstein]

Coding for last post.

Code: Select all

 Option Explicit
Sub BBCodeTagsURL()  '     https://eileenslounge.com/viewtopic.php?p=315503#p315503  https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24092&viewfull=1#post24092
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

Rem 2 Find the URL if there is one
Dim strURL As String
     If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
     Let strURL = 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))
    Else
     Let strURL = ""
    End If

Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)

End Sub
Sub SplitytySplit_TLDR()    '  https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24093&viewfull=1#post24093
Rem 0  The text you selected
Dim SelTxt As String
 Let SelTxt = UCase(Selection.Text) ' A text I highlighted in Word in  UCase  as part of way to get the key text search case insensitive

Rem 1 Some groups of   name, URL,   pairs
Dim WdEileen As String, WdFox As String
 Let WdEileen = "Eileen's Lounge,eileenslounge,The Windows Clipboard,"
 Let WdFox = "Excel Fox,excelfox,"
Dim Wdkey As String
 Let Wdkey = UCase(WdEileen & WdFox)                   '  word(s) Keys like       "Excel Fox, Eilen's Lounge

Dim URLEileen As String, URLFox As String
 Let URLEileen = "https://eileenslounge.com/app.php/portal,https://eileenslounge.com/app.php/portal,https://www.eileenslounge.com/viewtopic.php?p=300947#p300947,"
 Let URLFox = "https://www.excelfox.com/forum/forum.php,https://www.excelfox.com/forum/forum.php,"
Dim URLs As String
 Let URLs = URLEileen & URLFox
                            'Dim SptURLs() As String
                            ' Let SptURLs() = Split(URLs, ",")  ' 1 D array of URLs like    "https://eileenslounge.com, https://www.excelfox.com
 
Rem 2 Find the URL if there is one
Dim strURL As String
    If InStr(1, Wdkey, SelTxt, vbTextCompare) > 0 Then
     Let strURL = Split(URLs, ",")(UBound(Split(Split(Wdkey, SelTxt)(0), ",")))
    Else
     Let strURL = ""
    End If

Rem 3 Make the BB Code Tag URL thing
Call MakeABBCodeTagURL(strURL)
End Sub
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







Sub MakeABBCodeTagURL(ByVal strURL As String) ' https://www.excelfox.com/forum/showthread.php/2860-Notes-tests-string-manipulation-of-text-files-and-string-manipulations?p=24092&viewfull=1#post24092
    With Selection
     .Text = "[URL=" & strURL & "] " & .Text & " [/url]"
     .Collapse Direction:=wdCollapseEnd
     .Font.Color = wdColorAutomatic
    End With
End Sub

[DocAElstein]

>But for now, the strings are already there
Ah, there we go - technical debt ...

Yeh, I don’t disagree, - I am a computer messy sometimes, and pretty well unprofessional most of the times, when it comes to anything computer like, and I figured text is cheap these days. But I might do it better later.
Just now I got too many other things going on in my life that I have to be correct and proper with, so I let myself go a bit with computing, and as far as computers are concerned I am most very keen currently to learn and get things right . But ironically with me, doing things wrong , breaking them, rethinking them , managing to fix them, ( if I am lucky, Lol ), along the way etc. etc., is often how I learn best :)
(If people show me the proper way, or tell me better, and tell me when I am being a xxxx, then that is a great welcome bonus also. The snb offerings were good, although needing a lot of time figuring them out can be characteristically annoying )

>So you see, I added another pair to the eileens lounge string- tacked it on the end, so no big effort.
You can do similar for a dictionary. Unless I am missing something.

The point is, I can add the things to what is there.
With the dictionary I could run a code bit to .Add something, but that would be pointless. - Since doing it "properly" with a dictionary would likely mean that I would have a list somewhere that is accessed to fill the dictionary. I would add the thing to that list. But then the code would have to run and have to loop to make and fill the dictionary every time, which my messy unconventional approach avoids.…( [Joke?]Simple but brilliant [/Joke?] )

[SpeakEasy]

But why. You seem happy to have your list in a string oin your code, why would ypu not be happy to have the dictionary b uilt in code?

So instead of (using your earlier example)
Let strEileen = "Eileen's Lounge, https://eileenslounge.com/app.php/portal, eileenslounge, https://eileenslounge.com/app.php/portal, "

You’d have

Code: Select all

mydic.Add "Eileen's Lounge", https://eileenslounge.com/app.php/portal
mydic.Add "eileenslounge", https://eileenslounge.com/app.php/portal

And adding the new stuff would simply lead to

Code: Select all

mydic.Add "Eileen's Lounge", https://eileenslounge.com/app.php/portal
mydic.Add "eileenslounge", https://eileenslounge.com/app.php/portal
mydic.Add "The Windows Clipboard", https://www.eileenslounge.com/viewtopic.php?p=300947#p300947

whicxh I'd hazard is hardly more onerous than adding to your string, and is also easier to see ...

So, here’s the (case-sensitive) dictionary version of your BBCodeTagsURL method

Code: Select all

Sub BBCodeTagsURLDictionary()  
    Rem 0  The text you selected
    Dim mydic As New Scripting.Dictionary
    Dim SelTxt As String
    Dim strURL As String
    
    Let SelTxt = Trim$(Selection.Text) ' A text I highlighted in Word
    
    Rem 1 Some groups of   name, URL,   pairs
    mydic.Add "Eileen's Lounge", "https://eileenslounge.com/app.php/portal"
    mydic.Add "eileenslounge", "https://eileenslounge.com/app.php/portal"
    mydic.Add "The Windows Clipboard", "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    
    mydic.Add "Excel Fox", "https://www.excelfox.com/forum/forum.php"
    mydic.Add "excelfox", "https://www.excelfox.com/forum/forum.php"
     
    
    Rem 2 Find the URL if there is one
    If mydic.Exists(SelTxt) Then
        strURL = mydic(SelTxt)
    End If
    
    Rem 3 Make the BB Code Tag URL thing
    With Selection
        .Text = "[URL=" & strURL & "] " & .Text & " [/url]"
        .Collapse Direction:=wdCollapseEnd
        .Font.Color = wdColorAutomatic
    End With
End Sub

And let's not worry about the legacy string(s) ... Just feed them to a function such as this

Code: Select all

Public Sub GenerateDictionaryCode(strSource As String)
    Const vbCharQuote = """"
    Dim URLPairs
    URLPairs = Split(strSource, ",")
    Dim lp As Long
    For lp = LBound(URLPairs) To UBound(URLPairs) - 1 Step 2
        Debug.Print "mydic.add " & vbCharQuote & Trim(URLPairs(lp)) & vbCharQuote & ", " & vbCharQuote & Trim(URLPairs(lp + 1)) & vbCharQuote
    Next
End Sub

and copy'n'paste the output into your code instead of the string ...


But in the end, if your code works the way you want, then stick with it.

[snb]

Case sensitive:

Code: Select all

Sub M_snb_000()
   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)
   If UBound(sn) > -1 Then c02 = Split(Filter(sp, c01)(0), "_")(1)
   
   MsgBox c02
End Sub

Case insensitive:

Code: Select all

Sub M_snb_000()
   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
End Sub

[DocAElstein]

Case sensitive:
...
Case insensitive:
...

Thanks, Merry Easter

I think I see what you are doing,
but maybe for less confusingly comparison/ and to make the comparisons generally that I am doing a bit easier, we can say you changed something like this ,

Code: Select all

   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 something like this

Code: Select all

  '  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 as far I can easílly see, 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
At first glance I think the If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 might be better, but I don’t know for sure, and it is good to see the alternative for comparison, … and for the greater glory of…. etc.
_(iiI) you noticed maybe, 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 "" ( Or maybe better said , more likely, you and SpeakEasy did not do the stupid extra Else bit that I did. )
( I am not sure why you did the c02 = "" though? Perhaps because as you don’t generally declare your variables…. Whilst ' commenting that out, 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?)

I modified slightly the TLDR versions of my and your offerings based on you and SpeakEasy’s last posts and included for comparison the dic stuff that SpeakEasy did.
Here it is. (Also in the uploaded file)

I will discuss it amongst other things in the next couple of posts.

[DocAElstein]

But why. You seem happy to have your list in a string oin your code, why would ypu not be happy to have the dictionary b uilt in code?
So instead of (using your earlier example)
Let strEileen = "Eileen's Lounge, https://eileenslounge.com/app.php/portal, eileenslounge, https://eileenslounge.com/app.php/portal, "
You’d have

Code: Select all

mydic.Add "Eileen's Lounge", https://eileenslounge.com/app.php/portal
mydic.Add "eileenslounge", https://eileenslounge.com/app.php/portal

.....

Happy Easter…..
Yes, that dic stuff does actually look a bit neater. Much neater actually when I look at it, quite pretty as well , so the dic way has gone up a little bit in my preferences. But I tend to like some things though, that influence slightly my preferences with these things. ….
For example
I really like the horizontal scroll bar and the ability to have a lot of stuff "hidden" to the right, stuff for example…
_ I like to have, perhaps for later reference, but don’t always want to see,
or,
_ as in this case, a lot of data, farmed over to the right, out of site and out of mind so I can concentrate my thoughts on the working, more active coding, as opposed to the inactive data
or
_ similarly it’s nice to know the stuff is there, tantalising for a little peak by a light loving caress on the scroll bar.
Just personal preference I guess


Of course I could use your dictionary coding way and do like

Code: Select all

mydic.Add "Eileen's Lounge", https://eileenslounge.com/app.php/portal : mydic.Add "eileenslounge", https://eileenslounge.com/app.php/portal

, so as to satisfy some of my horizontal desires. But somehow it seems more abrupt to me. I am not sure why that is. Perhaps as I know that the : is really a code line end (or something similar???), at any case something of an abruption as it were , and so because of that, then somehow I just don’t get that same tranquil continuum thoughts as I scroll through it. It’s difficult to explain exactly what I mean, but it’s like this: when I scroll to and throw through maybe a long string, or one of my beautiful long code lines, then I get pleasant thoughts of things like a Lady skater meandering gracefully on a frozen lake by moonlight. (And that’s when I am sober.!!!).
Your dic lines just don’t give me those same thoughts for some reason. Just personal preferences again I guess.

As regards comparing the long string way to your dic lines for storing the data in the VB Editor…. Whether straight vertical, or horizontally with the :s, each text pair in the dictionary coding requires a line to .Add it, whereas each of my sub strings might have a dozen or so text pairs in it. So naively I expect the reading on running the codng would be a bit quicker for the long string way. But I agree there is probably not much in it, and any ideas and suggestions are always welcome, I am not in a rush on this one, so I can take the time to perhaps come up with a solution I am most happy with, and pick up other useful things. ( For example,
_ I notice from your coding and the next offering from snb that I was being a bit stupid in a redundanty sort of way, with my If Then Else stuff to either get the URL string or make it empty. Obviously, I only need to fill the variable if I have a matching URL string, otherwise it is already and remains empty. So I can forget the Else bit. I overlooked that )
_ The Trim on the selected text is a good idea. I probably aught to always do that, since selecting a word in MS Word is often a bit difficult, as it often seems to catch a space or two, or even more I don’t want if I am unlucky )

I note that an exact match is needed for the dic coding, unlike all the other offerings so far. But I don’t know yet whether that is better or worse, it might be a case of swings and roundabouts probably. I will only know for sure as I use the codings more

I re did my shortest code versions of mine, snb’s, and also included a one like you did for me.
( https://www.excelfox.com/forum/showthre ... #post24103 )
Yours I just modified a tiny bit
_ to make the comparisons better to see
_ made it case insensitive using the UCase( ) way

snb’s latest did not change so much from his last, at least not when put in a form more similar to my shortest version of it

Looking at the offerings then so far: If we consider again now the main "working" bit of the codings then once again it’s the Rem 2 that its the main bit to look at for comparison purposes: This is what we got for the 4 basic code ideas so far

Code: Select all

    If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
     Let strURL = 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))

    If InStr(1, Wdkey, SelTxt, vbTextCompare) > 0 Then
     Let strURL = Split(URLs, ",")(UBound(Split(Split(Wdkey, SelTxt)(0), ",")))

    If InStr(1, strItAll, SelTxt, vbTextCompare) > 0 Then
  '  If UBound(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)) > -1 Then
     Let strURL = Split(Filter(Split(strItAll, ", "), SelTxt, True, vbTextCompare)(0), "_")(1)

    If mydic.Exists(SelTxt) Then
     Let strURL = mydic(SelTxt)

The dic is the clear winner there, - but I don't think any of us expected any different. - if I understand correctly , dictionary stuff uses something not native to VBA and its all to do with efficient ways to get at lists. Its just the filling of it we are , …. well, not really disagreeing so much about, more like just discussing really. On those lines, I wonder why the "makers of" never arranged that you could chuck an array of key, Item pairs at it all in one go. Perhaps the possible occurrence of duplicate keys might have chucked a spanner in the works as uniqueness of Keys seems to be inherent or important to it.
Anyways, all good interesting stuff.

[DocAElstein]

And let's not worry about the legacy string(s) ... Just feed them to a function such as this...

Yes, thanks that’s nice. I did that sort of thing once or twice. Another unusual unconventional idea, to generate a repetitive code syntax . Remembering that sort of thing is helpful as I can move on, but at the same time stay flexible , not being as committed to one way as it might appear initially – something like that can help to ease the pain of "migration backwards, legacy leprosy", or what ever the clever sounding computer professional terminology is.
( Also something similar along those lines that I do/ have, is that I have a function I find very useful to check out exactly what is in a string of text, (that coding is hopelessly unprofessional and messy, - I did it just after I started getting into VBA, - but it works ) , That gives me a few output forms including the VBA syntax form, so that something like this copied and passed to the function,
Hello
there
would give me amongst other things an output that might look something like this
"Hello" & vbCr & vbLf & "there" )

_._______________

But in the end, if your code works the way you want, then stick with it.

I tend not always to do that, or think like that…. When I was very little, I had what might be described as some sort of divine enlightenment/ intervention. I noticed my parents were idiots so that made me a super idiot. So I decided to force myself often to ignore what I think sometimes doing what maybe someone smarter suggests, or forcing myself to do something I really don’t want to do or think I can’t do.
I eventually led very successfully a team of smart people. I was an idiot, I knew just about how much. They were very clever, some very very clever. I knew just about how much. They all thought they were super geniuses. That would have messed things up, but I had no illusions of my or their intelligence, which often saved the day, very profitably.
I just wanted to get the right answer. If that meant I was wrong over and over again or right all the time or any combination there of, it made no difference to me.
Another way of saying that is that I don’t rely on my instincts. Maybe occasionally I might rely on my experience as I try to get as much of that as I can, even if it’s painful sometimes.

Alan