What qualifier can I use for vbcomponents, other than sheets.add

DAVID DURLIN 0 Reputation points
2023-05-26T21:43:42.41+00:00

I am trying to add VBA source code to an EXISTING Excel worksheet. I have an example intended to create a NEW worksheet.

Sample:

Dim NewSheet as Worksheet

Set NewSheet = Sheets.Add

...

With ThisWorkbook.VBProject.vbcomponents(NewSheet.Name).codemodule

End Sample

Can I substitute a different method for "Sheets.Add" and/or change "NewSheet.Name" to something else?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,640 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.1K Reputation points
    2023-05-27T10:17:25.27+00:00

    Try using the name of existing worksheet:

    Dim ws as Worksheet
Set ws = Sheets("some name")
. . .

    or:

    With ThisWorkbook.VBProject.VBComponents("some name").CodeModule
. . .