Word VBA Replace multiple Spaces in Text with BB Code String

[HansV]

> Does not work, or does not work very well?
Does not work at all. Microsoft simply hasn't exposed a class name for DataObject, unlike for example Scripting.FileSystemObject or WScript.Shell.
Most object we use do have a class name. For those we can use early binding by setting a reference, or late binding by using GetObject/CreateObject with a class name.
DataObject is an exception, so we MUST use its hexadecimal ClassID.

[SpeakEasy]

>why I have never needed to employ 36-character strings before;

It's the formal string representation of the underlying 128bit UUID (which Microsoft tend to refer to as a GUID; a CLSID is just a GUID being used for a specific role - identifying a class object), that representation being 32 hexadecimal digits separated by 4 hyphens ...

We humans are sheltered from them normally, but there are odd occasions where we find we have to use them. Not very often for most users, it has to be said.

[Doc.AElstein]

Hi,
Sorry it’s a bit off topic, but as we all touched on the subject, I thought just out of interest , and for future reference… …… after I gave up looking for how to programmatically get a list of all those CLSID things, I found some stuff by accident and did it!! ( I must remember to give up wasting time looking for things, - it’s very inefficient as I rarely find stuff, - I find things quite efficiently when I am not looking for them.)

I can get a full list very quickly - Sub ListCLSIDs() in the uploaded file does that.
In the uploaded file in column A you can see that I have about 6500 CLSIDs on the computer I initially tested it on.

Getting the names using the idea I had does not work so well. My idea was of a macro using error handling and trying something like pseudo like Set OOPObj = CreateObject("New:" & {The CLSID String} & "") followed by TypeName(OOPObj)
I wrote a macro to do that, but it keeps opening all sorts of applications, doing other strange things and crashing. I guess it’s not very sensible trying to create every object possible.
Never mind, - I wrote that macro, Sub CLSIDsValueNames() , to run on a selection of the CSIDs in column A. So you can do a few rows at a time , and build the list up that way. It is a very slow inefficient macro as it saves every time it loops so that you don’t loose too much information when it crashes.
I have not got around to completing the list, but what I will do whenever I have the time is build up that list and a few more, and re upload the file to the file sharing place. So as time goes on that file will get more and more info in it. the share link will stay the same. As time goes on I will add a sheet for different computers, as I expect the CLSID lists will be a bit different on different computers.
I expect as time goes on I will get to see what things cause a crash, and probably add a few notes in the file.

( If anyone out of interest wants to add a sheet on my file of their results then let me know and I will add it to the main file I keep at the link ).

Might be academically interesting to compare list on different computers

File: CLSDsUndClassNames.xls https://app.box.com/s/gx5zfvnnjkqoakalj81h7thdme1cib1d


Alan

Code: Select all

 ' ' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey    '   https://www.vbforums.com/showthread.php?552899-Getting-all-sub-keys-from-a-registry-value                  https://www.vba-tutorial.de/apireferenz/registry.htm
Sub ListCLSIDs() '  http://www.eileenslounge.com/viewtopic.php?f=26&t=22603&p=289007#p289007
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim Registry As Object, varKey As Variant, varKeys As Variant
 Set Registry = GetObject("winmgmts:\\.\root\default:StdRegProv")
 Registry.EnumKey 2147483650#, "SOFTWARE\Classes\CLSID", varKeys ' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey
    For Each varKey In varKeys
    Let Ws.Range("A" & Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row + 1 & "").Value = varKey
    Next
End Sub

Code: Select all

Sub CLSIDsValueNames()
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim RngSel As Range: Set RngSel = Selection
    If RngSel.Cells.Count = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
    If RngSel.Cells.Columns.Count <> 1 Then MsgBox prompt:="Please select at least 2 cells in only column A": Exit Sub
    If Not RngSel.Cells.Column = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
Dim stearCel As Range
    For Each stearCel In RngSel
     Let Ws.Range("B" & stearCel.Row & "").Value = "Tried" ' To indicate I tried - this can be useful to see where it crashed
    Dim OOPObj  As Object
    On Error GoTo EyeEyeSkipper
     Set OOPObj = CreateObject("New:" & stearCel.Value & "")
     Let Ws.Range("D" & stearCel.Row & "").Value = TypeName(OOPObj)
     Let Application.DisplayAlerts = False
     ThisWorkbook.Save ' This is done to save all got so far incase Excel crashes on next loop or below
     Let Application.DisplayAlerts = True
    On Error Resume Next
    OOPObj.Close
    On Error GoTo 0
    Set OOPObj = Nothing
EyeEyeSkipper:
    On Error GoTo -1
    On Error GoTo 0
    Next stearCel
End Sub

_._______________________________________

P.S. The name is not always that revealing as it could be, In our example, the key of "{1C3B4210-F441-11CE-B9EA-00AA006B1A69}" gives the name of IDataAutoWrapper

[ChrisGreaves]

>why I have never needed to employ 36-character strings before;

@Hans & @Speakeasy: Thank you. It appears that I have led a sheltered life to date!
Cheers
Chris

[DocAElstein]

Hi
I stumbled recently over the later posts in this Thread here ( this Thread being that one I am writing in now), which is from a few years ago, Oct 2021. We seem to have had a discussion on things "Clisd Clipboard" and the such. We seem to have had a very similar discussions to these last few post in a more recent thread, - this un, from about page3, March 2024 – https://eileenslounge.com/viewtopic.php ... 3&start=40

I thought I would drop that newer link in here for ease of future reference, (and I can’t edit easily my older posts here), and also I thought it could be helpful for some "re navigation", especially as the macro I cobbled together to get a "Clsid List" here , (the link to which I already passed on in a few places), was not so good, or rather the second macro above, Sub CLSIDsValueNames(), was a bit .. weird .


But it’s now all been done a bit better/ simpler in a single macro, in that more recent post from about here: https://eileenslounge.com/viewtopic.php ... 41#p314941


Also for future reference, I am building up some notes just now on this page 54 , notes on of things …well … for now … I will call it Clsid Clipboard & Co. … for want of a better title.
This similar discussion crops up quite often. We had it a few times now.

I thought it might be of interest to mention that at this stage in case anyone has any interest, comments or thoughts on what I have done so far at this stage. If so, probably best place to look is from about here and here , where I re arranged the two coding offerings, (my older one and the newer better one), so that it’s much easier to compare them. We can see some similarities in the two.
As ever, I am just interested in getting a good review and notes about all this for the benefit of me and other people’s future reference.


I might finally try to write another third macro based on what I have learnt here so far, - ( a macro, for example, with more descriptive variable names for example, as I am a bit confused currently on some of our choices. )
Maybe as well I might see if I can get anything else relevant and useful from the registry objects, ( and, of course, I will be adding a peripheralaraly of useful notes and references hidden in the ' comments over to the right for the greater benefit of later mankind, (if there is one, Lol) ).


The big advantage of the newer macro is that it gets the Class name in the same macro directly and easily. I had tried to get that name in a rather cumbersome way using that second macro above, Sub CLSIDsValueNames(). ( Running that had some strange annoying secondary effect of making things now always appearing on a restart. Those I have to get rid of with that Task Manager thing , ( the (hold Ctrl+Shift, and then Esc thingy) )
_.____

So I spent some time to rearrange the two codings and make very minor changes just so that a direct comparison was much easier
Post #535
Post #537
_.____

Alan

P.S. a last passing thought in the next post…._

[DocAElstein]

P.S. a last passing thought in the next post…._

_.... continued a bit from the last post

Just one last extra passing thought based on my last post above and similar Clipboard things I have in my brain just now,

One other small thing that might be of interest to someone to comment on at this stage.. I had a thought . the thought popped up in my mind after I re read carefully a few times, the comments and replies of the smarter people in the two Threads, and some other clipboard related discussions that we have had …_
So
_.. we had been talking about, and often do talk about , things Clsid in particular with reference to the Late Binding of the Clipboard, (perhaps more accurately said Late Binding of the MSForms library’s object called the DataObject).
Many of us also know there are sometimes problems in using the Clipboard via the DataObject in the last 10 years or so
Based on all that, how does this sound …._

_.... Maybe the lack of a Late Binding name for the DataObject came about partly because…._
The DataObject was perhaps intended to be used in a UserForm or similar Form things. That would tie up with it being automatically available to you to Early Bind when you add a UserForm. Now, we know one of the main advantages of Late Binding is to make it easier to share, as you don’t have to need the receiver to add the reference. But that problem does not arise if the shared file has had a Form added
Maybe then the DataObject was optimised somehow to work in coding in a Form thing, with little interest if it worked so well as we do often to put something in the Clipboard in coding generally. There was little need to ever Late Bind it in a form.

Now this next is getting a bit advanced technical…_

_.... The clipboard only has one thing but in many formats, but it’s a lot more complicated than that:
_ for one thing Excel stuff, when controlling the clipboard at any time does it often in this weird delayed render "just in time" thing
_ a further complicating factor is we have this unholy hybrid Office thing , a pseudo clipboard, commonly called the Office Clipboard which is just on the notification chain knowing about when something is added to the Clipboard, but can and usually does makes a few of its own versions of what the Clipboard tells the Office Clipboard that it’s either got or is likely to get.
_ In some cases, its only on a Paste operation that the Clipboard has, "just in time" , what we naively think we put there.
There is a lot of quirks and contradictions going about: Before the Clipboard is used, its cleared, The best people seem at a lost to explain how it can work after its cleared in some cases. But it does, sometimes. I have seen myself that if I copy from Excel, and close Excel, I can Paste, all be it I lose some formatting, but not always all – I may still have a bit more available than simple text. I am thinking that through the deferred entry something might come from the Office Clipboard. Whether that is by design, or by some spaghetti cross wiring is probably anyone’s guess in the meantime

Maybe when we do things inside a Form thing we are sheltered a bit somehow.
I am thinking that perhaps the formats in the Office Clipboard are sufficient for anything we may do in a Form. (Forms generally I am talking about – Forms in VB can have pictures, (I am not sure about VBA UserForms, - I know a lot of stuff about them looks very similar), the Office Clipboard does pictures quite well.
What I am trying to say is that maybe the DataObject is somehow optimised to be used in a Form. And because of the contradictions and exceptions in the Windows Clipboard workings, maybe indirectly it may use the Office Clipboard versions. Maybe that works good in a Form. (Maybe if you use a .Copy from in a Form , then secretly the DataObject controls it, since it’s the Boss for anything Clipboard in a Form)
Use a DataObject outside and it might try to overdo it’s abilities to control things in the chain when it can’t handle some Window application it might not have anything to do with if used in coding in a form


I have little experience with UserForms, so I don’t know if the problems comes up as often when coding is inside Forms. I suppose if it does then my thinking of it being optimised for use in Forms things is a bit weak, if it’s just as bad when used inside there?


Alan