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

[SpeakEasy]

>never arranged that you could chuck an array of key, Item pairs at it all in one go

You can in VB.NET...

[DocAElstein]

Ahh, OK, so it’s like it is a native dictionary thing, but for some reason not possible in VBA. It’s nice to know, thanks.
I guess at the end of the day its really very fast to loop in VBA, filling the dictionary from a simple 1 dimension array ( or 2 of them ) , and those arrays could be easily made quickly with a Split of my text string(s) , so I could have the dic stuff, and my long pretty text strings as well.
Still having that knowledge about the possibility to do it somewhere somehow is useful IMO .. … *who knows….


I can do a lot of nice, dare I say, novel, things with the 1 D arrays of the available from the dictionary dic.Keys() and dic.Items(). So it’s a shame I can’t do it the other way around. But no big deal.

( I can never get my head fully around all the different VB____ variations as there are certain personal preferences about what is what or was and Microsoft and other documentation sometimes re define when or what something was or came about, … but …I think the average opinions say VBA came from something of the form
VB ……. Basic, Visual Basic sort of
, and maybe not quite from something defined by something of the form
VB ……. “Net world” … some use of Net in the definition …
So maybe the dictionary in VBA came along a different chain of events where the chucking an array at it in one go feature fell off, or didn’t make it around a bend when someone derailed the wagon or naughtily rerouted the train of thought )

( Still , *who knows…., I might get it to work in VBA some day through an illegal hack that can’t be done to break into “Net world” from VBA because I am too stupid to understand why it can’t be done. It might take a decade or two though, so I will need to keep fit, and maybe only drink a lot occaisionally and not a bit often )

[SpeakEasy]

The dictionary we can use in VBA comes from the Windows Scripting host and was designed to be used by any of the scripting languages that could use that host (eg VBscript, Jscript, Javascript). It doesn't (to the best of my knowledge) have any interface to allow it to be directly loaded from an array.

The dictionary object in the .NET world is a completely different implementation, so there isn't a hack through the .NET world to get to the scripting host dictionary.

That being said, there are ways to get to (some) .NET classes from VBA...

[DocAElstein]

These different things are stretching my brain a bit too far just now, but good to have here as it might help things fall into place in my brain much later.

( VBScript rings a bell as being on the same train as VBA ,
.Net being something to do with a lot of extra stuff to do nice things, for example I made a simple user form window thing in PowerShell look a lot better, when somehow I got to .Net stuff. But I really had no idea what I was doing, copied some stuff found when I was looking for something else. Seemed to make < > </ > thingies work inside PowerShell script,and something said somewhere it was getting at .Net stuff to do that. All getting a bit too vague for me for now. )

[SpeakEasy]

Powershell IS .NET

[snb]

To use a dictionary I would write it this way:

Code: Select all

Sub snb_dic()
  c00 = Selection
  
  With CreateObject("scripting.dictionary")
    .Item(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal"
    .Item(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal"
    .Item(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    .Item(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php"
    .Item(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php"
      
     MsgBox .Item(UCase(c00))
  End With
End Sub

In Word you can also use the docvariables:

Code: Select all

Sub snb_docv()
    c00 = Selection
  
   With ThisDocument
    .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php"
    .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php"
      
     MsgBox .Variables(UCase(c00))
  End With
End Sub

You might have a look at
array, dictionarys, collection, arraylist, sortedlist in https://www.snb-vba.eu/inhoud_en.html

[DocAElstein]

Powershell IS .NET

I guess you would have to have a lot of background knowledge to justify saying that. I don’t think I could manage any attempt again currently for me to get any clarity in the whole confusing, (to me) VB Net terminology world. Still, always useful to have it said by someone who likely knows about it all, JIMHO.
_._____________________________________________________________

To use a dictionary I would write it this way:
...
In Word you can also use the docvariables:
......

thanks, I took a look
Yours and SpeakEasy’s dics are very similar.
(You have likely not overlooked something that me and SpealEasy may have missed. Certainly I missed it. That being that because of the way these dic codings are working, we don’t need the
If dic.Exists(SelTxt) Then
The reason why we don’t need that, is that if we try to get the .Item from a .Key that does not exist, ( which is the same as saying if we try to get at a dictionary entry that is not there ), then it does not error. Instead of erroring, it has the feature that it makes (.Adds) the dictionary entry giving it the Key you used, and the item is left at some sort of nothingness. Exactly what nothingness of the various nothingnesses there are in VBA I am not too clear about. But the end result in the codings we have been looking at, is that, if such a code line like ,
strURL = mydic(SelTxt) or your strURL = .Item(SelTxt) etc.
, is done when that Key/ dictionary entry is not there, then our URL variable , strURL , will be left in some "" form, which is exactly what we want in such a case of there being no dictionary entry.
( I know you know about that as I think I picked this feature up from you a few years back. I am just mentioning it for anyone else passing, particularly as it appears that a few of the dictionary codings around these days anywhere, also don’t need this, and I think a lot of people have not realised this feature yet ) )
Apart from that, people’s opinions differ a bit about the With CreateObject( being good because you do away with an object variable. Personally I am less keen most of the time with the With and End With pair, but in some uses I quite like it, the With CreateObject( being one of them.
I could not make any guess as to which of the two dics is the better one in whatever way. I will hazard a guess that there may not be a lot in it.

_.______________

The document variable way looked initially interesting. It seems to be like a simplified dictionary. The disadvantage here seems to be that if the document variable does not exist, then attempting to get that non-existent variable, will error. There is no simple way that I know to allow for that. For example there is no equivalent of the dictionary If dic.Exists(SelTxt) Then
Probably error handling would be the only simple way to do a check. We usually all frown a bit on error handling***, if we can do without it. So unless this document variable way has significant other advantages that I don’t know about, then, whilst it certainly is interesting to know about it, I think, for now, it does not get on my short list, or at least low down the list. Shame as my initial thoughts were that it might be some sort of word dedicated and optimised type of dictionary. So then I would have had it all neatly in in word.
I wonder if possibly Error handling in VBA, ( ***I mean specifically using Error handling in the way I do below, not necerssarily Error handling in VBA in general, which can be a good idea ), has a different reputation in Word as Excel? My opinion of Error handling in VBA in the way I do below, being a bad thing, comes from partly listening to the average smarter than me in Excel person, who more often than not says it’s a bad thing if you can find a way to do without it.

The document variable way is new to me, so I will defer making too much judgment on that one just now. Perhaps smarter Word experts passing might at some time have a comment on it or advice about it?


Something like this would add the two latest snb offerings to the main bit to make comparisons from, at least as regards the main working bit of the coding ( correcting SpeakEasy’s dic to remove the If mydic.Exists( bit

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)

     Let strURL = mydic(SelTxt)    '   SpeakEasy dic                                           Let strURL = mydic(Key:=SelTxtSelTxt)

     Let strURL = .Item(SelTxt)     '   snb dic                                         Let strURL = .Item(Key:=SelTxt)

    On Error Resume Next
     Let strURL = .Variables(SelTxt)  '   document variable way
    On Error GoTo 0

_._______________________

You might have a look at
array, dictionarys, collection, arraylist, sortedlist in https://www.snb-vba.eu/inhoud_en.html

Yes, I most usually routinely would check out again there, whenever my brain is fully in the thinking of Dictionary, Lists and the such.
_.______________________________________________________________________________________________

[snb]

I'd use:

Code: Select all

Sub snb()
  With ThisDocument
    .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php"
    .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php"
    
    On Error Resume Next
    MakeABBCodeTagURL .Variables(UCase(Trim(Selection))), Selection
  End With
End Sub

Code: Select all

Sub MakeABBCodeTagURL(c00, c01)
  With Selection
    .Text = "[URL=" & c00 & "] " & c01 & " [/url]"
    .Collapse 0
   End With
End Sub

[SpeakEasy]

>overlooked something that me and SpealEasy may have missed

Just to be clear here, I did not miss it. I am well aware of this behavior. Indeed, that is why I put the Exists check in - to avoid adding unnecessary entries to the dictionary. And, to be honest, the 'feature' of automatically adding previously non-existent entries every time we try and access the Item property I consider a bad design decision from Microsoft. Your mileage may differ (and there are certainly occasions where this behaviour can prove useful), but adding new key/item pairs to a dictionary should really be an explicit action dictated by the programmer, not something that magically happens in the background. Microsoft fixed this for their later dictionary class in .NET

>correcting SpeakEasy’s dic to remove the If mydic.Exists
Modifying/changing, yes. Correcting, no. The code is not incorrect

>Yours and SpeakEasy’s dics are very similar.
They use the same scripting dictionary class. So not similar - identical.

>I guess you would have to have a lot of background knowledge to justify saying that
It's pretty much explicitly stated in the Microsoft documentation, e.g.

PowerShell is built on the .NET Common Language Runtime (CLR). All inputs and outputs are .NET objects.

>We usually all frown a bit on error handling
We do?

[DocAElstein]

Yes, I probably should have worded that better and said something along the lines of "SpeakEasys adjusted to remove the If mydic.Exists( bit for better comparison with snbs". Sorry. You’re more likely to do coding in a particular way for a reason other than just it seems to work, that’s less common. I prefer to do that as well, but have the handicap of not yet knowing enough, Lol. (Not at all criticising others at all, just saying, that’s all. All to their own. )
I did think at the time that you might have been aware of the feature but that you never the less added that If mydic.Exists( bit for some reason.
Interesting to know your reasoning for using it. It comes across to me that many people just don’t know about that feature of a dictionary elemnt being made if it dies not exist, This feature usually gets missed out in many blogs. Snb’s has it. He refers to it as something like the
x = .item( ) way.
I like the sound of
Anything = dic(Key) way
For what I am doing it’s not a big deal to leave If mydic.Exists( in. So now you’ve explained that a bit, I will think again about using it or not. (If I do use it, in the situations I could have not used it, I shall give you the blame again, Lol.)
(Especially the insight jem about Microsoft apparently fixing it later so as it does not happen was very interesting, ( although of course, one should always be a bit cautious about Microsoft changing things, Lol, - it’s what Microsoft seem to do increasingly as time goes on, changing things. Why is often debatable, often it seems they have the philosophy of, "if it 'aint broke, break it at some point later, so as to help force them into buying another one") )

One common way people seem to use the dictionary in VBA is to get a unique list from , say, a single column of data where there might be duplicates. There they would loop typically be adding a key of each value in the list if that key does not exist.
I will take a guess that at least some of them don’t know that just looping a single line like this pseudo coding would get them that same unique list of keys
For all cells
Let Anything = dic (Key:=cell value)
Next cell
Maybe it’s not too bad to do that, although as a Laymen it always seemed to me the dic key way to get a unique list, is a somewhat abstract way to do something. I assume it just happens to work very well compared to other ways?
_.________________________________________
I think Error handling in VBA coding is more often than not a good idea. I have found it very useful. The thing I was referring to specifically was using On Error Resume Next
The average smarter person tells me to avoid using it if you can. I don’t really know enough about the inner entrails of what is going on to analyse the pros and cons enough myself.
But opinions vary and I have learnt to be careful of asking those to justify their views. It seems to be a subject area thet often touches a nerve, and they want to kill me. Perhaps they had good reasons and forgot. Happens to most all of us , sadly , I guess. I have in recent years been needing to tie a knot in my handkerchief more often. That method won’t work for the vast area of VBA. That is one reason why I am trying to get some things very clearly and full explained and written down. Most documentation is very patchy and rarely tells clearly the whole story.
As we often discussed before, there are an awful lot of permutations to be aware of in Error Handling in VBA, so possibly this is a subject more likely to cause irritations when the odd permutation slips out of the brain and that can lead to annoying frustrations.
_.____________________________________________
I figured the 2 dics were the same. It was just the With CreatObject(" ") that was the thing. It does without using an object (or things**), variable(s**). Usually I go the other extreme having many variables I could do without with a lot. But this thing is an exception with me,- in the past has seemed a good idea and nice a few times. - I cannot recall now all the reasons, my brain is not fully in the dictionary list area just now.
**I also for other objects/ things used something like
With CreatObject(" ").something.sommethhingElse
Somehow it looked nice and good and beautiful. But mostly for me it was just a Layman gut good feeling.
_._____________________________
That Microsoft PowerShell documentation tells me next to nothing I can understand. Maybe it would be helpful for some revision when I already knew it all, like the knot in the handkerchief to jog the memory.
_._______________________-
I am a bit more enlightened about the dictionary now , thanks.

_.___________________________________________________________________________________________

I'd use:....

That seems to be very similar to my version of your last, Sub snb_docvc_TLDR()
It seems to be a few minor changes, and I see you have missed out the On Error GoTo 0 that would normally accompany the On Error Resume Next in such coding. Opinions vary, but more often omitting that in such a situation is regarded as bad.
The way you have done it, it’s hard to put that in for it to do the job it would normally be put in for, which would be to confine the user defined error handling to just what you are expecting could error. In this coding , an error in the coding in the function would not get handled by the default VBA error handling. An unknown, unexpected, not handled in a controllable way, error could be bad I think, or so I am mostly told.
Still does no harm to see another version, thanks. I may have missed something else hidden in it.

[snb]

It's redundant to use on error goto 0 if no code follows.

[DocAElstein]

I realise that, and initially I thought you might be cleverly doing it for that reason. But unless I am mistaken, when an error occurs, (because the variable does not exist), then the code lines in the function will continue to be done. … well not all of them, the first line of the function will then go on to error, so for the case of the variable not existing, the coding does not work. By that not working, I mean, nothing happens, rather than me getting like I want for that situation like
[URL=] Something not in the variable list [/url]
And of course, if the variable does exist, then all the lines in the function are done after the On Error Resume Next , which is generally thought to be bad – you should restrict the On Error Resume Next to the minimum
What have I missed?

[SpeakEasy]

>The average smarter person tells me to avoid using it
Dunning–Kruger applies

>unless I am mistaken ...[]... the code lines in the function will continue to be done
You are mistaken

[DocAElstein]

Dunning–Kruger applies
Interesting , an old flame of mine, a Lady psychologist now, once suggested something like Narcissistic personality disorder when we discussed some of my experiences in forums, … ( I had to google that one as well, to figure out what she was talking about, Lol.) Seems to be some similar ideas in the two

You are mistaken
Either
_ (i) I worded myself badly again – I should have said, it appears that it would error on the first line of the function, but because of the On Error Resume Next in the calling routine, ( and remembering what we (you) said recently …..the current procedure cannot handle an error …….then …. the calling procedure has an enabled error handler, it is activated to handle the error…….. ) , and so we go to the End With , then End Sub ( both those lines in the main calling routine )
So nothing is done – So the coding does not work. By that not working, I mean, nothing happens, rather than me getting like I want for that situation like
[URL=] Something not in the variable list [/url]
Or
_ (ii) I missed something again, because,
If I step through the coding in the case of no document variable existing for a selected text, then it appears to follow as I explained in _(i) ,
In other words, we seem to do , or attempt to do , "[URL=" & c00 & "] " & c01 & " [/url]" , in the function. But then it’s good night.

(And of course, if the variable does exist, then all the lines in the function are done after the On Error Resume Next , which is generally thought to be bad – you should restrict the On Error Resume Next to the minimum)

[SpeakEasy]

>it appears that it would error on the first line of the function

And that is true. - but not by design. It is an artifact of snb's propensity to write barebones code with undeclared variables and parameters

This

.Variables(UCase(Trim(Selection)))

returns an object - which is not an error., albeit that the contents of that object are undefined, and thus an error. That object is happily passed intact to the subroutine, and only in that subroutine does VBA try to read a value from that object (by reading the default property of the object). And that attempt to read a value is what causes the error in the procedure.

If, instead, you had

.Variables(UCase(Trim(Selection))).Value

or

Sub MakeABBCodeTagURL(c00 As String, c01)

then the error would more properly (depending on your point of view, I guess) occur in the calling routine and the subroutine would not be called.

it should also be pointed out that it doesn't much matter in this specific case where the error is actually raised, and you are right that as written you do not get the empty URL that you want if the document Variable does not exist. I'd be tempted, if I were to do it this way, to rewrite slightly as:

Code: Select all

Sub snb()
  With ThisDocument
    .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php"
    .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php"
    
    MakeABBCodeTagURL .Variables(UCase(Trim(Selection))), Selection
  End With
End Sub

Sub MakeABBCodeTagURL(c00, c01)
  On Error Resume Next
  With Selection
    URL = c00.Value ' error if missing document Variable, will occur here
    .Text = "[URL=" & URL & "] " & c01 & " [/url]"
    .Collapse 0
   End With
End Sub

[DocAElstein]

Enlightening. I did see the reference to an object in the error when I removed the user error handling. After which, l did also at some point do the c00 As String in the signature line of the function. I could not quite figure out what it was all about though.

_._________________________-

... I'd be tempted, if I were to do it this way, to rewrite slightly as:

What about this slightly modified version of your suggestion. I am thinking it could be slightly better?

Code: Select all

 Sub snbSpeakEasy()  '  https://eileenslounge.com/viewtopic.php?p=315925#p315925
  With ThisDocument
    .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php"
    .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php"
    
    MakeABBCodeTagURLsnbSpeakEasy .Variables(UCase(Trim(Selection))), Selection
  End With
End Sub

Sub MakeABBCodeTagURLsnbSpeakEasy(c00, c01)
  With Selection
  On Error Resume Next
    URL = c00.Value ' error if missing document Variable, will occur here
  On Error GoTo 0
    .Text = "[URL=" & URL & "] " & c01 & " [/url]"
    .Collapse 0
   End With
End Sub

My reasoning being, just the, perhaps, good practice of limiting the use of On Error Resume Next to the minimum.

[snb]

The check for the existence of the variable is in the main macro.
But I'd refrain from using a separate macro for this purpose

Code: Select all

Sub M_snb()
  With ThisDocument
    .Variables(UCase("Eileen's Lounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("eileenslounge")) = "https://eileenslounge.com/app.php/portal"
    .Variables(UCase("The Windows Clipboard")) = "https://www.eileenslounge.com/viewtopic.php?p=300947#p300947"
    .Variables(UCase("Excel Fox")) = "https://www.excelfox.com/forum/forum.php"
    .Variables(UCase("excelfox")) = "https://www.excelfox.com/forum/forum.php"
    
    On Error Resume Next
    selection.text="[URL=" & .Variables(UCase(Trim(Selection))) & "] " & selection & " [/url]"
  End With
End Sub

[DocAElstein]

….The check for the existence of the variable is in the main macro.
But I'd refrain from using a separate macro for this purpose…..
I fear it may be that time again, Lol, when you start posting irrelevant or riddling misleading confusing rubbish, either to hide your mistake or just for the hell of it, or both…. aka _______
Never mind. It was interesting to find out about the document variables. Thanks again for that.

[HansV]

Be polite, please!