Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 89

Now – after the if block – this cell is guaranteed to have a valid, not a dummy, Excel comment: either it had a proper value for visible (true or false) in the first place, so it's bona fide, or else we just made a new comment (without text), so it's bona fide too. We now call the Excel comment text command. This is different from the VBA code where we can set properties of the comment. The Excel comment class has properties all right – author, shape object, and visible – but no property for its text!

Instead we need the Excel comment text command. And what a peculiar command it is. With no parameters used, it returns the existing text. As soon as you include the text parameter with some text, it writes the new text instead of returning the old text. Now it also has a boolean over write parameter as well (that's right, two words – there seems to be certain degree of illiteracy lurking here), but if you include it (with over write, as over write true compiles) the line errors 'range "A10" does not understand the Excel comment text message'!

The solution is just to omit the over write parameter, which is not only redundant, but destructive. Without it, everything works swimmingly. As long as you include the text parameter, and no start parameter, it overwrites the current text. You do not need the start parameter if planning to replace any existing text entirely – start 1 is the default.

Editing a comment

In VBA, one can format comments using code, as well as add text. But there seems to be a bug with being able to get the characters of a text frame in AppleScript, and thus no way to modify the font. Getting characters of text frame of shape object of theComment always returns {} – an empty list. This is a bug, which will hopefully be fixed in a later version.

Changing the Name on all comments

In VBA you can change the name applied to comments by deleting and recreating the comments. But that requires that you know which cell the comment is attached to. You wouldn‘t choose to search every cell in the worksheet, or even every cell in the used range, for their comments – it would take a very long time. Instead you get every comment on the sheet.

In VBA, before you delete the comment you can find where to recreate it by getting its Parent Property. Almost all Objects in Excel, Word and PowerPoint VBA have a Parent property, but for some reason that was not implemented in Office 2004 AppleScript for any of the Office applications. So to get every comment, you would have to loop through every cell of the used range, and basically throw away the VBA version. Here's the VBA code:

Public Sub ChangeCommentName()

   Dim ws As Worksheet

   Dim cmt As Comment

   Dim sCmtText As String

   Dim sOldName As String

   Dim sNewName As String

   sNewName = "new name"

   sOldName = "old name"

   For Each ws In ActiveWorkbook.Worksheets

      For Each cmt In ws.Comments

          With cmt

             sCmtText = Application.Substitute( _

                         .Text, sOldName, sNewName)

             .Delete

             .Parent.AddComment Text:=sCmtText

          End With

      Next cmt

   Next ws

End Sub

Here's the very different AppleScript, looping through cells instead of comments:

tell application "Microsoft Excel"

     set newName to "new name"

     set oldName to "old name"

     repeat with ws in (get every worksheet in active workbook)

          set ur to used range of ws

          repeat with i from 1 to count cells of ur