Rename Code Modules Programmatically

[Stormwatcher]

I am attempting to write a group of macros to assist with data organization. It's not completely finished, but it is in use... however, it gets updated regularly and I have begun to write a userform that will remove the previous modules and create new ones from the updated file. All of the code is stored in the Personal Macro Workbook and it gets updated from a standalone file. I am stuck on the following line:

Workbooks("Workbook1").VBProject.VBComponents("Module1").Name = "New Name"

Error given is: "Method 'Name' of object '_VBComponent' failed"
The version of Excel that I am getting this error in is Excel: Mac 2011. All the code works fine when I test it on my Windows box, which uses 2003. Here is the full Sub that gets called:

Private Sub copyMisc()
Dim VBCodMod1 As Object
Dim VBCodMod2 As Object

Set VBCodMod1 = Workbooks("Updated File").VBProject.VBComponents("Misc").CodeModule
Set VBCodMod2 = Workbooks("Personal Macro Workbook").VBProject.VBComponents.Add(1).CodeModule

VBCodMod2.Name = "Misc"

VBCodMod2.DeleteLines 1, VBCodMod2.countoflines
VBCodMod2.addfromstring VBCodMod1.Lines(1, VBCodMod1.countoflines)
End Sub

I have made sure that I have a reference to the MS VB for Applications Extensibility 5.3 reference library. As you can see, I have also tried being more or less specific with the code, without much success. If this were a Windows based Excel, I would think that it has something to do with the "Trust access to Visual Basic Project" setting, but as far as I can tell, Mac doesn't really have this setting.

Can someone point out what I'm doing wrong, whether it's code or a setting? I've spent quite a bit of time doing research and not having much luck.

Thanks,
-Stormwatcher

[GTO]

Hi there,

I do not have Mac and only use WIN, so more of a general comment/question than anything super helpful. You mention concern as to it being at a "Trust access..." issue in Mac. Have you tried a bit of test code to see if you can simply add code programmatically to a workbook in Mac? For instance just adding a module and a procedure to a test workbook. I was just thinking this might show whether it is a problem in renaming a module, vs. a problem in adding/editing code outright.

Mark

[snb]

Instead of rewriting code in VBA I'd prefer to use an addin and replace that addin if necessary. All users will be 'updated' simultaneously.

[Kenneth Hobs]

Welcome to the forum!

Please use code tags when posting code.

I don't see that line of code in your example sub. Be sure to save the workbook first. Try adding the file extension. e.g. "Workbook1" to "Workbook1.xlsm".

[Stormwatcher]

I do not have Mac and only use WIN, so more of a general comment/question than anything super helpful. You mention concern as to it being at a "Trust access..." issue in Mac. Have you tried a bit of test code to see if you can simply add code programmatically to a workbook in Mac? For instance just adding a module and a procedure to a test workbook. I was just thinking this might show whether it is a problem in renaming a module, vs. a problem in adding/editing code outright.

Mark: I have tried programmatically adding and removing modules. I didn't have any problems with either of them. For that matter, I can also set a string variable and return the name of a module, but have been unable to to figure out how to rename it. In the same interests, I have also tested the line of code in Microsoft Excel 2010 on the Windows side of things and got the same error, even after I made sure the trust access setting was correct.



I don't see that line of code in your example sub. Be sure to save the workbook first. Try adding the file extension. e.g. "Workbook1" to "Workbook1.xlsm".

The original line of code was in the macro:

Private Sub copyMisc()
Dim VBCodMod1 As Object
Dim VBCodMod2 As Object

Set VBCodMod1 = Workbooks("Updated File").VBProject.VBComponents("Misc").CodeModule
Set VBCodMod2 = Workbooks("Personal Macro Workbook").VBProject.VBComponents.Add(1).CodeModule

* VBCodMod2.Name = "Misc" * <--- (line that renames code)

VBCodMod2.DeleteLines 1, VBCodMod2.countoflines
VBCodMod2.addfromstring VBCodMod1.Lines(1, VBCodMod1.countoflines)
End Sub

However, when that had issues, I broke down the macro to analyze the only line which was actually throwing an error and attempted to test that singly:

Workbooks("Workbook1").VBProject.VBComponents("Module1").Name = "New Name"
I tested it in a clean workbook, with modules that didn't have any other code in them so that no unknown variables would be introduced. After your suggestion, I did go back and make sure that the workbook was saved and called as
Workbooks("Workbook1").xlsxUnfortunately, it didn't make any difference. =/

Since I've tested this on the Windows side, I don't think it's an issue with something that's inherent to the Mac OS X environment. It has to be something in the code.


Instead of rewriting code in VBA I'd prefer to use an addin and replace that addin if necessary. All users will be 'updated' simultaneously.
SNB, I'll do some research into this. Anything you know about it would be appreciated.

(Edit: because some of the auto-added "color" tags were interesting...)

[Paul_Hossler]

Win7 and Excel 2010



Option Explicit
Private Sub copyMisc()
Dim VBCodMod2 As Object

Set VBCodMod2 = Workbooks("Personal.xlsm").VBProject.VBComponents.Add(1).codemodule

VBCodMod2.Name = "Misc"

End Sub



this works for my Windows box. Are you sure that you're using the correct way to refer to the Personal WB? I don't speak Mac so I don't know the proper naming





Workbooks("Workbook1").xlsx

Probably should be

Workbooks("Workbook1.xlsx"). .....


Paul

[Stormwatcher]

11369

Paul - I think the "Option Explicit" made a difference. As you can see above, the first macro created the module, but didn't rename it. However, when I tried it with Workbook1 instead of the Personal Macro Workbook, everything worked fine. It was only when I tried to call the Personal Macro Workbook that I got the error picture.

If anybody knows the correct way to call this off the top of their head, that would be wonderful. =)

[GTO]

Not tested, as I don't use a Personal WB. But wouldn't the Personal Workbook be referred to (string-wise) like any other? i.e. - Workbooks("Personal Macro Workbook.xlsm") ?

[Stormwatcher]

That's what I would think, but when I try that, I get a "Subscript out of range" error. Same with if I use .xlsx as well.

[GTO]

Try .xlsb

[Stormwatcher]

I found that too - dug up where the file was actually located. Same result, though - subscript out of range error. =/

[Stormwatcher]

11370

I swear, I did nothing different. But now it works. Why?

Or rather, instead of:

VBProject.VBComponents("Module1").Add(1).CodeModule I wrote
VBProject.VBComponents("Module1").Add(vbext_ct_stdmodule)

But I'd tried that before with no success...

[GTO]

Not sure, but glad you got it working. FWIW, if code replacement is only in standard modules, I would just replace the module. Remove the old one, import the new one already named.

Again, just my thoughts,

Mark

[Stormwatcher]

Ok so I've played around with it some more, and so far I've been able to determine that Excel is actually having a problem with the string in quotes.

example: I can set the future name of the module to "URGHFRUSTRATING" and it will work fine. If I set it to "Misc" then I get the "Application defined or object defined error" just like a couple posts above. That doesn't make sense to me - a string should be a string regardless, what's between the quotes shouldn't matter (within reason - I know module names can't have spaces). Can anyone shed some light on this?

Mark: that's what I'm doing now, but I'm trying to automate the process so that anybody can update the code, and do it without going into the VBE. I also think that in Macs, going outside the application is limited for VBA. Not to mention trying to sort out where files are located on each computer, etc. Apple's Finder != Windows Explorer. =/

[GTO]

Or rather, instead of:

VBProject.VBComponents("Module1").Add(1).CodeModule

I wrote

VBProject.VBComponents("Module1").Add(vbext_ct_stdmodule)

But I'd tried that before with no success...

Please take these comments with a 'grain of salt' if-you-will, as I need to bail soon... so without checking/more at memory...

I would not think either of those would work. In either case, you are trying to Add (create) a new module. But including ...VBComponents("Module1").Add(... means you are referring to a pre-existing module. Including .CodeModule after the .Add would also fail on its own, as the CodeModule is a property of the VBComponent, not something you can create. You are just trying to create the VBComponent.

See if the attached helps at all.

(for others) the code to which is:

WB1: Editor.xlsm

Standard Module: basEditorMain


Option Explicit

Sub ReplaceAndRename_Misc()
Dim wb2bEdited As Workbook
Dim PRJ As VBProject
Dim CMP As VBComponent

On Error Resume Next ' reference WB to be edited, include a bailout if not loaded
Set wb2bEdited = Workbooks("WB2Bedited.xlsm")
On Error GoTo 0

If wb2bEdited Is Nothing Then
MsgBox "ACK!"
Exit Sub
End If

Set PRJ = wb2bEdited.VBProject

'reference the old code module to replace
On Error Resume Next
Set CMP = PRJ.VBComponents("Misc")

'If it doesn't exist, bail (testing only)
If Err.Number <> 0 Then
'msg; close destination wb?
Exit Sub
End If

'remove the old module and "unset" the reference
PRJ.VBComponents.Remove PRJ.VBComponents(CMP.Name)

Set CMP = Nothing

'Not sure if needed, but my effort at ensuring we're done and no hiccups
DoEvents

'This wb has the new module. Export it
ThisWorkbook.VBProject.VBComponents("Misc").Export ThisWorkbook.Path & "\Misc.bas"

'Then import it to the destination wb
PRJ.VBComponents.Import ThisWorkbook.Path & "\Misc.bas"

DoEvents

'Set a new reference to the new/replacement module in destination
Set CMP = PRJ.VBComponents("Misc")

'rename (again, I would SOOO already have this named before importing; it's just plain easier.
CMP.Name = "NewMisc"

Set CMP = Nothing

'Adding/Creating a new module in destination, rename, add code
Set CMP = PRJ.VBComponents.Add(vbext_ct_StdModule)

CMP.Name = "basMyEmptyModule"
CMP.CodeModule.AddFromString " 'Here's a Comment"

wb2bEdited.Save

'cleanup/remove the .bas file
On Error Resume Next
Kill ThisWorkbook.Path & "\Misc.bas"
On Error GoTo 0
End Sub


Standard Module: Misc



Option Explicit

Sub MyMacro()
' New Code
Dim lRet As VbMsgBoxResult

lRet = MsgBox("Do you want to do this?", vbYesNo, vbNullString)

If lRet = vbYes Then
MsgBox "You chose <Yes?>.", vbInformation, vbNullString
End If

End Sub


WB2: WB2Bedited.xlsm

Standard Module: Misc



Option Explicit

Sub MyMacro()
'OLD
Dim lRet As VbMsgBoxResult

lRet = MsgBox("Do you want to do this?", vbOKCancel, vbNullString)

If lRet = vbOK Then
MsgBox "You chose <OK>.", vbInformation, vbNullString
ElseIf lRet = vbCancel Then
MsgBox "You chose <Cancel>.", vbInformation, vbNullString
End If

End Sub



Hope thta helps,

Mark

[GTO]

ACK... Not sure why rendering in code tags is funny, but before signing out... here's two links. Both Ron De Bruin's article and Chip Pearson's are very helpful :cloud9:

http://www.rondebruin.nl/win/s9/win002.htm

http://www.cpearson.com/excel/vbe.aspx

[snb]

You might also have a look over here:

http://www.snb-vba.eu/VBA_Excel_VBproject_en.html

[mikerickson]

re:
Workbooks("Workbook1").VBProject.VBComponents("Module1").Name = "New Name"
Spaces are not allowed in the name of vbComponents for Mac. "New Name" is not a legal component name.