Comments, though they're attached to cells, are elements of the sheet, not the cell (range). You insert a comment using the add comment command, but it is rather different in AppleScript than in Visual Basic for Applications (VBA).

Public Sub AddComment()
	Dim cmt As Comment
	With Worksheets("Sheet1").Range("A10")
	Set cmt = .Comment ' Check to see if comment exists
	If cmt Is Nothing Then _  ' if not, add one
		Set cmt = .AddComment
	End With
	With cmt
		.Text Text:="My Comment"  ' Overwrite existing comment
		.Visible = True
		.Shape.Select  'Select to allow editing
	End With
End Sub

In AppleScript:

tell application "Microsoft Excel"
	tell range "A10" of active sheet
		set cmt to its Excel comment 
		-- no error if nothing (dummy comment)
		set vis to visible of cmt 
		--get any property , returns missing value if empty
		if vis is missing value then
			set cmt to add comment
		end if
	end tell
     
	tell cmt
		Excel comment text text "My  comment" 
		--overwrites, but omit 'over write'!
		set visible to true
		select its shape object -- to allow editing
	end tell
end tell

In VBA, you set a variable to the putative comment, and if it's Nothing, you Add one. Normally in AppleScript, you'd do the same thing by putting it in a try/error block. If there's no value (it might require calling the variable you set to it to discover that), you would get an error, and in the on error block, you'd add the comment.

However, the Excel comment has unusual behavior. When a cell does not have a comment, it still has some a sort of artificial Excel comment property, which does not error. Note that you must include the its — its Excel comment — if in a tell block targeted at the range. Otherwise it does error, even if there is a genuine comment there, and you'll never get hold of it.

However, all of the properties of this artificial Excel comment return missing value. So you check for its visible property, and if that returns missing value — neither true nor false — you know that you need to add a comment.

You do that with the add comment command in the Excel Suite. You could include its comment text parameter to add the text then and there, but that would make the script rather complicated since cells with genuine comments have not been dealt with yet. So you add comment, exactly as the VBA macro did, and handle all of them the same way after the if/end if block concludes.

After the if block, this cell is guaranteed to have a valid, not an artificial, Excel comment. Either it had a proper value for the visible property (true or false) in the first place, or else you just made a new comment (without text). They're both genuine.

Now call the Excel comment text command. This is different from the VBA code where you can set properties of the comment. The Excel comment class has properties — author, shape object, and visible — but no property for its text.

Instead you use the Excel comment text command. Without using parameters, 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. It also has a boolean over write parameter as well (note that it's two words), but if you include it (with over write, as over write true compiles) the line errors as range "A10" does not understand the Excel comment text message.

The solution is just to omit the over write parameter, which is redundant anyway. Without it, everything works. 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 you are planning to replace any existing text entirely — start 1 is the default.

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

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
			set cl to cell i of ur
			set cmt to Excel comment of cl 
			-- no error if nothing (dummy comment)
			set vis to visible of cmt 
			--get any property , returns missing value if empty
			if vis is not missing value then
				set cmtText to Excel comment text cmt
				set AppleScript's text item delimiters ¬
					to {oldName}
				set chunks to text items of cmtText
				set AppleScript's text item delimiters ¬
					to {newName}
				set cmtText to chunks as Unicode text
				delete cmt
				add comment cl comment text cmtText
			end if
		end repeat
		set AppleScript's text item delimiters to {""}
	end repeat
end tell

Public Sub DeleteActiveSheetHyperlinks()
	On Error Resume Next
	ActiveSheet.Hyperlinks.Delete
	On Error GoTo 0
End Sub

tell application "Microsoft Excel"
	try
		delete every hyperlink of active sheet
	end try
end tell