Prev: A generic "find" function
Next: Locked problem when using .Clear
From: NoSpam on 5 May 2010 09:21
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
module's macros from the names 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
issues an error mesage saying it cannot find
WorkbookName!ModuleName.OldMacroName even though that name exists in the
code and in the list of maros.

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 away for awhile. When it came back, I changed
the name again and it has gone away again - at least until ....? Nothing
else I have tried fixes the problem, even removing all the code in the Sub.

Why is this happening and how I can stop it?

Many thanks for any help?


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
From: Barb Reinhardt on 5 May 2010 11:27
You don't say what OLDNAME and NEWNAME are? Also, what are the names of all
of the modules?
--
HTH,

Barb Reinhardt



"NoSpam(a)aol.com" 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
> module's macros from the names 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
> issues an error mesage saying it cannot find
> WorkbookName!ModuleName.OldMacroName even though that name exists in the
> code and in the list of maros.
>
> 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 away for awhile. When it came back, I changed
> the name again and it has gone away again - at least until ....? Nothing
> else I have tried fixes the problem, even removing all the code in the Sub.
>
> Why is this happening and how I can stop it?
>
> Many thanks for any help?
>
>
> 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
> .
>
 | 
Pages: 1
Prev: A generic "find" function
Next: Locked problem when using .Clear