How can I rename a module using VBA macro?

Question

I put one button on sheet. I want to assign macro for that button which change the module name.

For e.g. There are two modules named Module1 and Module2. So I want to replace module name of above two modules with each other. Can anyone suggest me how to rename a module using macro?

Answer 1

You can program Visual Basic Editor (VBE) (actually it is funny I just answered this awhile ago).
Check it out to see how to add reference to VBA Extensibility Library.
Something like below should work for you once you've properly set up the required reference:

Dim VBProj As VBIDE.VBProject, wb as Workbook
Set wb = ThisWorkbook
Set VBProj = wb.VBProject
VBProj.VBComponents("Module1").Name = "Module2"

Important:

1. Add reference to VBA Extensibility Library

   Microsoft Visual Basic For Applications Extensibility 5.3

2. Tick Trust access to the VBA project object model under Developer Macro Settings.
3. Project should be unlocked.