Copy/Paste of unmodified VBA Code in Word returns an error

randyspaulding12 · #1 05-28-2024, 05:59 AM

Hello, I copy and pasted sample VBA code from CoPilot into Word as a macro and tried running it but received the attached error message. I am starting here because it seems every time I copy and paste VBA code snippets into my Word document I get either the attached error or (more frequently) a run-time error 13 type mismatch, without modifying any of the sample code. I am guessing it is some simple universal setting that I am doing wrong???

gmaxey · #2 05-28-2024, 06:12 AM

Well to start, no one here can copy a picture of your code into a VB Project and few will take the time to type it all out.

You get that error because "DefaultSorting" is not a method associated with a bookmark object. "DefaultSorting" is a method of the Bookmarks collection.

A better guess is CoPilot (and most of the rest) while artificial, is not really that intelligent.

Guessed · #3 05-28-2024, 06:36 AM

You are asking AI to create code and it pretends it knows what it is doing but I can tell you, in this case it really doesn't. I don't recognise the .DefaultSorting command and it absolutely doesn't belong there (what would you expect to happen if you sorted a single thing? what would it go in front or behind of?). If you removed that line AND the subsequent one then I would expect the code to actually have a chance to work without the error you are seeing.

The CleanText function would also need to be edited to actually do something useful. Looking at how the code is calling CleanText, the intention is to return a valid Bookmark name. I don't know the comprehensive rules for valid bookmark names but I can say that a bookmark name can't include spaces or begin with a number. If your selected text is a single word then the macro 'could' run without an error but in most cases it will probably fail because of what was selected when the macro started.

The MsgBox lines aren't right either but at least they don't stop the macro from proceeding.

Then you have to ask, what is the ultimate point of the code. Are you thinking it is useful to create a bookmark around a word with a name that matches the word? How is this going to be something useful?

You should look at CoPilot generated code as an opportunity to learn about coding rather than expecting it to actually work. If this is an example of what it can do then it might be rare that it produces errorless code. This could be because of limitations in AI or perhaps because you asked the question in a 'non-optimal' way. Perhaps if you experimented with asking the question in different ways, you could compare the various code outputs to see if there are variants that work as you expected.

randyspaulding12 · #4 05-28-2024, 06:57 AM

Thank you for the prompt replies. I think you can tell I am very amateur to VBA code and to what CoPilot is/can do. Now I know.

The point of the code I am looking for is simple: take a selected list of names from a table and create bookmarks for each of them. So I'm expecting some kind of for loop that goes through the range of selected text line by line and creates a bookmark for each. Does anyone have some REAL sample code I could try?

Example:
Table A:
Column 1 Column 2 Column 3 ...
Row1 Name1
Row2 Name2
Row3 Name3
Row4 Name4

In my word document Table A I select just Name2 and Name3, run the macro and end up with two new Bookmarks: Name2, Name3

gmaxey · #5 05-28-2024, 11:06 AM

May not be perfect for your requirement, but should get you close:

Code:

Option Explicit
Sub ScratchMacro()
'A basic Word Macro coded by Gregory K. Maxey
Dim oTbl As Table
Dim lngIndex As Long
Dim colBMs As New Collection
Dim oBM As Bookmark
Dim strBMName As String
Dim oRNg As Range
Dim bValid As Boolean
  'Get the collection of existing bookmarks
  For Each oBM In ActiveDocument.Bookmarks
    colBMs.Add oBM.Name, oBM.Name
  Next oBM
  Set oTbl = Selection.Tables(1)
  bValid = True
  For lngIndex = 1 To oTbl.Rows.Count
    Set oRNg = oTbl.Cell(lngIndex, 1).Range
    oRNg.End = oRNg.End - 1
    strBMName = fcnValidateBMName(oRNg.Text)
    On Error Resume Next
    colBMs.Add strBMName, strBMName
    If Err.Number = 0 Then
      ActiveDocument.Bookmarks.Add strBMName, oRNg
    Else
       oTbl.Cell(lngIndex, 1).Range.Shading.BackgroundPatternColor = wdColorRed
       bValid = False
    End If
    On Error GoTo 0
  Next lngIndex
  If bValid Then
    MsgBox "Processing complete."
  Else
    MsgBox "Processing complete. One or more rows could not be bookmarked due to a duplicate name."
  End If
lbl_Exit:
  Exit Sub
End Sub

Function fcnValidateBMName(strIn As String) As String
  strIn = Trim(strIn)
  strIn = Replace(strIn, " ", "_")
  If IsNumeric(Left(strIn, 1)) Then
    strIn = "_" & strIn
  End If
  fcnValidateBMName = strIn
End Function

randyspaulding12 · #6 05-28-2024, 01:01 PM

Thank you, that works! Only question is, how would I limit the iterations in the For loop to the number of rows I have selected in the table beforehand versus the total number of rows in the table? I believe

For lngIndex = 1 to oTbl.Rows.Count

gives me the latter.

gmaxey · #7 05-28-2024, 02:10 PM

Here is one way:

Code:

Sub ScratchMacro()
'A basic Word Macro coded by Gregory K. Maxey
Dim oTbl As Table
Dim lngIndex As Long
Dim colBMs As New Collection
Dim oBM As Bookmark
Dim strBMName As String
Dim oRNg As Range
Dim bValid As Boolean
  'Get the collection of existing bookmarks
  For Each oBM In ActiveDocument.Bookmarks
    colBMs.Add oBM.Name, oBM.Name
  Next oBM
  Set oTbl = Selection.Tables(1)
  bValid = True
  For lngIndex = 1 To oTbl.Rows.Count
    Set oRNg = oTbl.Cell(lngIndex, 1).Range
    If oRNg.InRange(Selection.Range) Then
      oRNg.End = oRNg.End - 1
      strBMName = fcnValidateBMName(oRNg.Text)
      On Error Resume Next
      colBMs.Add strBMName, strBMName
      If Err.Number = 0 Then
        ActiveDocument.Bookmarks.Add strBMName, oRNg
      Else
         oTbl.Cell(lngIndex, 1).Range.Shading.BackgroundPatternColor = wdColorRed
         bValid = False
      End If
      On Error GoTo 0
    End If
  Next lngIndex
  If bValid Then
    MsgBox "Processing complete."
  Else
    MsgBox "Processing complete. One or more rows could not be bookmarked due to a duplicate name."
  End If
lbl_Exit:
  Exit Sub
End Sub

Function fcnValidateBMName(strIn As String) As String
  strIn = Trim(strIn)
  strIn = Replace(strIn, " ", "_")
  If IsNumeric(Left(strIn, 1)) Then
    strIn = "_" & strIn
  End If
  fcnValidateBMName = strIn
End Function

Guessed · #8 05-28-2024, 08:45 PM

Greg

Making a collection seems like extra effort when we could use the Bookmarks.Exists instead.

Code:

Sub AnotherWay()
  Dim aCell As Cell, oRng As Range, strBMName As String
  For Each aCell In Selection.Range.Cells
    If aCell.ColumnIndex = 1 Then
      Set oRng = aCell.Range
      oRng.End = oRng.End - 1
      strBMName = fcnValidateBMName(Trim(oRng.Text))  'needs Greg's function
      If Not ActiveDocument.Bookmarks.Exists(strBMName) Then
        ActiveDocument.Bookmarks.Add strBMName, oRng
      End If
    End If
  Next aCell
End Sub

I didn't include the cell shading where a bookmark already exists because it would need a bit more code to figure out if the selected cell has already been bookmarked from an earlier run.

randyspaulding12 · #9 05-31-2024, 05:19 AM

Thank you, Greg, that works even better.

This error handling exposed an interesting issue with case-sensitivity for bookmarks. Specifically, I have two bookmarks I am trying to create (among many others):

OP_Safe_HIPAA_164_504e2iiI_DR
OP_Safe_HIPAA_164_504e2iii_DR

Every time I try to create these two separate bookmarks I end up with one. If I add the first followed by the second, it replaces the first with the second. If I add the second followed by the first, it replaces the second with the first. Either way it does not recognize these as two separate bookmarks even though case is different.

Any ideas? Does the same happen for others?

gmaxey · #10 05-31-2024, 03:12 AM

Agreed.

Guessed · #11 05-31-2024, 05:38 AM

There is a limit to the length of a bookmark name so if your strings are too long then the truncated name could be the same.

Looking at your two strings, it appears to be a Case Sensitivity issue. The difference between the two strings in the capitalisation of one character. Unfortunately, it appears that when you try to add a bookmark name that uses the same characters (ignoring case) as a bookmark that already exists then the bookmark gets MOVED (ie the original bookmark is removed).

This could be an insurmountable issue for the purposes of this macro. Can you explain the point of this bookmarking - what are you using the bookmarks for?

randyspaulding12 · #12 05-31-2024, 06:01 AM

The point is to show in the bookmark specific sections in an international standard. I could patch around this specific issue but then am left wondering where else I might face the same issue, i.e. what are the rules that apply with Bookmarks and case-sensitivity (and length). Even if I manually add these two bookmarks one by one in Insert->Bookmark->Add they only create one bookmark. So, this is clearly "baked in" to Word. Again, just want to know the rules of the game.
I did another simple experiment. I tried creating opsafe and OPSAFE manually one-by-one (no macro). It only ended with 1 bookmark. It appears bookmarks are not case-sensitive???

Guessed · #13 05-31-2024, 03:55 PM

I don't understand what this means "The point is to show in the bookmark specific sections in an international standard"

What I want to get to is the reason why you think a series of bookmarks that align with the exact same 'case variable' text in the document is better than the actual text in the document. To me, that text is far more useful and visible to every user whereas the bookmarks are hidden from view and easily unaligned if the text on the page is subsequently edited.

Are you saying that you want users to see a navigable list (navigation pane) that they can use to jump to an area in the document? This same thing in Acrobat files happens to be called bookmarks - is this where the confusion is coming from? You would be able to see a list like that if your text was styled with an outline level but not if the text in inside table cells which it appears to be. Table cells also prevent a TOC field from finding paragraph styles although adding character styles in a table can work.

Since you already have the real 'unique' information on the page, the bookmarks could just be random strings that increment a number at the end. Without knowing WHY, there is zero reason to make the bookmark name exactly the same as the text it marks.

randyspaulding12 · #14 06-01-2024, 05:09 AM

The bookmarks lead you to specific sections in an international standard. Perhaps it is helpful to think of it as a legal document. Go to Article 164, clause 504, section e, subsection i, ii, iii, etc. The text that follows is lengthy and detailed, too much for a bookmark. When I open the document I most likely am intending to go to a specific article/clause/section/subsection (i.e. 164.504.e.iii). So that's what is available in the bookmarks. All medical industry standards are arranged this way. It just so happens that the legal standard has a section ii that has subsections A, B, C ... up to I (iiI) and then has a separate section iii. Completely different sections, but one has an uppercase character in it and another doesn't. I can patch around this (make this case iiIa, for example), I just was checking what the rules are for case so I can see if there will be other conflicts like this.

Guessed · #15 06-02-2024, 05:58 PM

You have already discovered that there are problems with using Bookmarks for this purpose.
1. Bookmark names are case insensitive
2. Bookmark names are more limited than text on the page (eg can't include spaces, start with a digit, include ()@ or other characters, must be less than ~40 characters etc)
3. Bookmark names are not automatically updated when text on the page changes
4. Bookmark ranges could be 'broken' easily (and invisibly to the user) by edits to text in the document.
5. Bookmark names can only appear once in a document

If your intention is to see a list of the document content so people can quickly navigate to locations in the document then there are two features that most users are already familiar with for exactly this purpose:
a) Table of Contents
b) Navigation Pane

IMO, both of these features avoid all the limitations of bookmarks. I can't see any benefits of using Bookmarks for navigation other than cross-references. But if you are using bookmarks for cross-refs then you can name the bookmark completely independently to the actual text it is pointing at.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Code to copy data from two sheet ,paste into corresponding sheet through cmdbutton	jackfruit88	Excel Programming	1	07-08-2022 09:27 PM
paste returns the wrong not the latest cut	Peborgh	Word	2	11-23-2021 03:31 AM
Error 4605 while trying to copy and paste with same document	Ilmari	Word VBA	2	05-05-2020 12:08 AM
Copy/Paste error	stuart255	Office	5	05-06-2019 02:13 AM
VBA Newbie (Trying to write a code to copy and paste info from one sheet to another)	Forum2000s	Excel Programming	2	05-07-2017 04:51 AM