I have a module which contains two Subs. The code of the 2nd Sub is given
below. Twice since I added the 2nd Sub, Excel has changed the names of the
macros from the name I gave them to something like:
WorkbookName.ModuleName.OldMacroName

I see this changed name when looking at Excel (Not VBE) Tools/Macros/macro
and also in the code. When Other code tries to call the macros, Excel says
it cannot find it and says the new macro cannot be found, even though it is
in the list of macros.

By process of elimination, I have found that simply changing the name of
the 2nd sub corrects the problem. The 1st thme this occurred, I changed
the name & the problem went aeay for awhile. When it came back, I changed
the name again and it has gone away again - at least until ....?


Does anyone have any idea why this is happening and how I can fix it?

Many thanks.


Sub FirstName
' very simple code
end sub

Sub SecondName(OldName As String, NewName As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim Code As String
Dim nCode As Long

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(Sheets(OldName).CodeName)
Set CodeMod = VBComp.CodeModule
'Get number of lines of code in old sheet
nCode = CodeMod.CountOfLines
'Get code starting at line 2
' (Option explicit is already in new sheet)
Code = CodeMod.Lines(2, nCode - 1)

Set VBComp = VBProj.VBComponents(Sheets(NewName).CodeName)
Set CodeMod = VBComp.CodeModule
'Write code to new sheet
CodeMod.InsertLines 2, Code
End Sub

I should have mentioned that I am using Excel 2003 with all available
updates.
On Mon, 03 May 2010 14:16:13 -0400, wrote:

I have a module which contains two Subs. The code of the 2nd Sub is given
below. Twice since I added the 2nd Sub, Excel has changed the names of the
macros from the name I gave them to something like:
WorkbookName.ModuleName.OldMacroName

I see this changed name when looking at Excel (Not VBE) Tools/Macros/macro
and also in the code. When Other code tries to call the macros, Excel says
it cannot find it and says the new macro cannot be found, even though it is
in the list of macros.

By process of elimination, I have found that simply changing the name of
the 2nd sub corrects the problem. The 1st thme this occurred, I changed
the name & the problem went aeay for awhile. When it came back, I changed
the name again and it has gone away again - at least until ....?


Does anyone have any idea why this is happening and how I can fix it?

Many thanks.


Sub FirstName
' very simple code
end sub

Sub SecondName(OldName As String, NewName As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim Code As String
Dim nCode As Long

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(Sheets(OldName).CodeName)
Set CodeMod = VBComp.CodeModule
'Get number of lines of code in old sheet
nCode = CodeMod.CountOfLines
'Get code starting at line 2
' (Option explicit is already in new sheet)
Code = CodeMod.Lines(2, nCode - 1)

Set VBComp = VBProj.VBComponents(Sheets(NewName).CodeName)
Set CodeMod = VBComp.CodeModule
'Write code to new sheet
CodeMod.InsertLines 2, Code
End Sub