To write a comment into a cell with VBA in Excel for Microsoft 365 on Windows, use the cell’s AddComment method; it creates a classic note: Worksheets("Sheet1").Range("A1").AddComment "Check this value". In current Excel terminology, threaded discussions are called comments and the older, single-text annotations are called notes, so the distinction matters when choosing a VBA method.
Notes and threaded comments in Excel
Excel has two annotation types that are easy to confuse. A note is a simple piece of text attached to a cell. It does not have a reply thread. A threaded comment supports replies and is designed for discussion and collaboration. In the Review tab, Excel distinguishes the commands for creating a new note and a new comment. The labels and placement can vary slightly with updates, but the underlying distinction remains.
For compatibility and straightforward cell annotations, Range.AddComment is the usual VBA choice. It creates a note, even though many people still casually call that a comment. If you specifically need a reply-enabled discussion, use Range.AddCommentThreaded in a supported Microsoft 365 version of Excel. These methods create different object types; a note is not automatically converted into a threaded discussion just because the interface uses the word “comment.”
Basic VBA code to add a note
This macro adds a note to cell B2 on a worksheet named Report. Change the sheet name, cell address, and text to suit the workbook. Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.
Sub AddNoteToCell()
Dim ws As Worksheet
Dim target As Range
' Set the worksheet and the single cell that will receive the note.
Set ws = ThisWorkbook.Worksheets("Report")
Set target = ws.Range("B2")
' Remove an existing note so the macro can be run again safely.
If Not target.Comment Is Nothing Then
target.Comment.Delete
End If
' Add a classic note to the cell.
target.AddComment "Check this value before publishing."
End Sub
Put the procedure in a standard VBA module, then run it while the workbook containing the Report sheet is open. ThisWorkbook means the workbook that holds the macro. If the code is stored in a separate personal macro workbook, ThisWorkbook points to that macro workbook, not necessarily the file being edited. In that situation, explicitly set a workbook variable to the intended open file before selecting its worksheet.
What each line does
The example uses worksheet and range variables to make its target clear. Worksheets("Report") identifies a sheet by its tab name, and Range("B2") identifies one cell on that sheet. Using an explicit worksheet is safer than writing Range("B2") on its own: an unqualified range refers to the active sheet, which could change while a macro runs.
Dim ws As WorksheetandDim target As Rangedeclare the variables used in the procedure.Setassigns an object, such as a worksheet or cell range, to a VBA variable.target.Commentrefers to the cell’s classic note, if one exists.target.AddCommentcreates a note containing the supplied text.
The check before adding is useful because a cell cannot hold two separate notes of this type at once. Deleting the old note first makes the procedure repeatable, but it also replaces its contents. If the existing note must be preserved, do not delete it automatically; choose an update or skip rule that matches the task.
Choose a cell or build the note text
You can set the target from a fixed address, a variable, or the current selection. A fixed address is appropriate for a reliable reporting macro. A variable is useful when the address is determined by other code. To use the active cell, assign Set target = ActiveCell, but first confirm that the correct workbook and worksheet are active. An accidental selection can put the note in the wrong place.
Note text can also be assembled from values. For example, a macro can create a date-stamped reminder with a string variable and then pass that variable to AddComment. Keep the text readable: a note is an annotation, not a good place to reproduce a whole report. If the content changes frequently or needs sorting and filtering, it may belong in a separate worksheet column instead.
For a note that contains multiple lines, use VBA’s vbNewLine constant when building the text. For example, a string can combine a short status with a second line containing a date. The note will display those as separate lines. Avoid assuming that long text will be immediately visible in full; users may need to open or resize the note to read it.
Updating or replacing an existing note
There are two common policies for rerunnable macros: replace the note every time, or leave an existing note untouched. The first code sample uses replacement. To leave existing notes alone, test whether a note exists and add one only when the cell has none. That prevents repeat runs from erasing a person’s edits, but it also means the macro will not refresh outdated text automatically.
When the macro owns the note and should keep it current, deleting and recreating it is a simple approach. When people may edit the note manually, safer choices include skipping cells that already contain notes or writing the information to a separate data field. Before choosing, consider whether the macro is expected to preserve user-entered content. A macro that silently overwrites notes can remove useful context even if its code runs without an error.
Also distinguish between deleting an annotation and clearing a cell’s value. The note’s Delete method removes the note; it does not clear the cell contents. Conversely, clearing a cell’s contents is not a reliable way to manage its note. Use the object method that corresponds to the item you intend to change.
Creating a threaded comment instead
If the goal is a reply-enabled discussion rather than a simple note, use the threaded-comment method available in supported Microsoft 365 Excel versions. This example targets one cell and adds a new threaded comment. It deliberately does not delete an existing thread: threaded comments have a different object model from notes, so the note-deletion check in the earlier macro should not be reused as a general-purpose comment manager.
Sub AddThreadedCommentToCell()
Dim ws As Worksheet
Dim target As Range
Dim commentText As String
' Set the worksheet and target cell.
Set ws = ThisWorkbook.Worksheets("Report")
Set target = ws.Range("B2")
commentText = "Can you confirm this figure?"
' Add a reply-enabled threaded comment.
target.AddCommentThreaded commentText
End Sub
Use this method only when the installed Excel version supports threaded comments through VBA. If the method is not recognized, check the Excel version and available object-model support rather than substituting AddComment and expecting the same result. AddComment creates a note; it does not create a threaded discussion. For a collaborative thread, the Excel interface may be the simpler option, particularly when the workbook is stored in a location that supports co-authoring.
Adding notes to several cells
For a list of cells, loop through a defined range and apply the same logic to each cell. Decide in advance how to handle cells that already have notes. Replacing them is convenient for generated annotations, while skipping them is safer when people may have added their own text. A loop should target a known worksheet and a bounded range rather than relying on whichever cells happen to be selected.
For example, a procedure could examine cells B2 through B20 and add a note only when a cell contains a value and has no existing note. The key parts are a For Each loop over the range, a check that the cell is not empty, and a check for an existing Comment before calling AddComment. This pattern avoids blank annotations and reduces the risk of replacing notes, but it does not handle threaded comments as though they were notes.
If the annotation text depends on each row, build it from that row’s values inside the loop. Keep references qualified—for example, use the current cell and its neighboring cells—so the text corresponds to the correct record. For large ranges, avoid repeatedly selecting cells; direct references are clearer and generally more efficient than using the selection or activating each cell.
Display, visibility, and formatting
Excel notes may appear when a user points to a cell or may be shown on the worksheet, depending on display settings and the note’s visibility property. A note can be made visible in VBA with its Visible property, as in target.Comment.Visible = True. Setting it to False hides the note box without deleting the note. For a cell that should show a note box when the macro finishes, set the property after adding the note.
Do not confuse a visible note with a cell value. The annotation is attached to the cell but remains a separate object; formulas that read the cell value do not retrieve the note text. If another process needs to use the annotation as structured data, store that information in a cell or table column instead. Notes are best for contextual reminders that people can inspect, not for data that calculations depend on.
Excel also offers formatting controls for note boxes, but formatting is separate from adding the text. Keep formatting changes optional unless the workbook requires a particular presentation. A macro that changes note size or appearance may behave differently depending on the note text and Excel display, so check the result in the intended workbook rather than assuming a fixed box size will fit every message.
Common errors and practical checks
A “subscript out of range” error commonly means the worksheet name in the code does not match a tab name in the workbook. Sheet names must be spelled correctly, including spaces. A “method or data member not found” error on AddCommentThreaded can mean the Excel version or object library does not expose that method. Test the intended feature in the actual version used by the workbook’s recipients.
- Confirm that the named worksheet exists in the workbook referenced by the code.
- Make sure the target is a single cell when adding one note or threaded comment.
- Check whether an existing note should be replaced, preserved, or skipped.
- Verify that macros are permitted by the organization’s security settings and that the file is saved in a macro-enabled format, such as
.xlsm, when the VBA code must be retained. - Run the macro on a saved copy first, especially if it loops through many cells or edits existing annotations.
Macros may be blocked by Trust Center settings or organizational policy. Do not enable macros in a file from an untrusted source simply to test a comment routine. For your own workbook, use an approved trusted location or follow the organization’s macro-signing and security procedures.
Frequently Asked Questions
Does VBA AddComment create a note or a threaded comment?AddComment creates a classic note, the single-text annotation called a note in current Excel. It does not create a reply-enabled threaded comment. Use AddCommentThreaded where that method is supported and a discussion thread is what you need.
How do I add a note to the active cell?
Set a range variable to ActiveCell, then call AddComment on it. This is convenient for an interactive macro, but it depends on the user having the intended workbook and cell selected when the procedure runs.
How can I stop the macro from adding a duplicate note?
Check whether the cell’s Comment object already exists. You can skip that cell or delete and recreate its note. Skipping preserves existing text; replacing keeps macro-generated text current but removes the previous note.
Can I add a comment to multiple cells at once?
Yes. Loop through the intended cells and call the appropriate method for each one. Decide how to handle existing annotations, and use a specific worksheet and range so the macro does not depend on the current selection.
Why can’t I undo a VBA comment change with Ctrl+Z?
Running a VBA macro clears Excel’s ordinary undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running code that adds, deletes, or replaces annotations.
Choosing the right method
Use AddComment when you want a short, non-threaded note attached to a cell, such as a reminder about a figure or an explanation for a reviewer. Use AddCommentThreaded when participants need to reply in a conversation and the Excel environment supports threaded comments in VBA. If the information must drive formulas, filters, or reports, put it in worksheet data instead of relying on either annotation type. Matching the method to the purpose prevents the common surprise of creating a note when a collaborative comment was expected.
Related
- Postcode Heat Map in Excel: Data, Setup and Limits
- Polynomial Solver in Excel: Methods, Limits, and Tips
- Excel Solver: Setup, Optimization Models, and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find in Range: Search Cells and Return MatchesTo find a value in an Excel range with VBA, use the range’s Find method and check whether…
- Excel VBA Array of Worksheets: Create and Use OneIn Excel VBA, you can work with several worksheets as a group by passing an array of sheet…
- VBA Cell to Range: How to Reference Cells in ExcelIn Excel VBA, a cell is already a one-cell Range object: use Set target = Worksheets("Sheet1").Range("A1") to store…
- Excel VBA Dim: How to Declare VariablesIn Excel VBA, Dim declares a variable: a named place for a value your code can use, such…
- VBA Function Calls: Syntax, Examples, and Common ErrorsA VBA function call runs a function and, in the usual case, uses the value it returns. In…
- Find Unique Values in VBA: Methods and ExamplesTo find distinct values in VBA, read the source cells into a Scripting.Dictionary and add a value only…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.