Are youlimited to the number of macro's in Workbook

[Aussiebear]

Sorry for the bland question but are you limited to the number of macro's you can have in your macro selection list whilst working in Excel or does it just become a scrolling list of thousand's ( for those so inclinded)?

Ted

[mvidas]

Hi Ted,

I've never reached a limit, and after running this macro I just wrote, I dont think there is one. But I have to ask, why would you have so many macros?Sub hahahahahahahahahahahahaha()
Dim i As Long, vFF As Long, vFile As String
vFile = "C:\TempFileDeleteMe.txt"
vFF = FreeFile
Open vFile For Output As #vFF
Print #vFF, "Attribute VB_Name = ""ModuleHahaha"""
For i = 1 To 10000
Print #vFF, "Sub Hahaha" & CStr(i) & "()"
Print #vFF, " msgbox ""Hahaha " & CStr(i) & """"
Print #vFF, "End Sub"
Next
Close #vFF
vFF = 0
On Error Resume Next
ThisWorkbook.VBProject.VBComponents.Import vFile
vFF = Err.Number
On Error GoTo 0
If vFF <> 0 Then
msgbox "You have programmatic access to VBA turned off. Either turn it on in " & _
"the security settings (and rerun this code), or Insert " & vFile & " manually"
Else
msgbox "You now have 10,000 macros"
Kill vFile
End If
End SubMatt

[Aussiebear]

Matt, Does the macro listing as deisplayed in Excel display all the macros that you store in Personal Macro.xls or only the macro's assigned to each particular workbook.

I've been trying to read John Walkenbach's Excel 2003 Power Programming with VBA over the last couple of days and its heavy going for someone who has little knowlege about VBA. Somewhere in this book John talks about storing your macro's in Personal Macro.xls and I thought he said its available for display. So.. if you keep building macro's and storing them in there, does it show up in the macro's list box in Excel?

Ted

[JonPeltier]

The limit on macros is that each module should not be larger than around 64k. This is pretty large. The problem is not that as soon as you hit 64k you will instantly crash, but as you exceed this size, things become unstable and possibly corrupt.

Consciousness of this limit ought to help encourage better programming techniques. It's better to have several smaller modules, with the macros grouped into modules according to their purposes. For example, one module for menu and command bar macros, another for file system macros, another for charting, etc. This also makes it easier to reuse code, because you can export these self contained modules into a code library directory, then import them as required.

[Cyberdude]

Does the macro listing as deisplayed in Excel display all the macros that you store in Personal Macro.xls or only the macro's assigned to each particular workbook.
Hi, Aussie! You apparently don't have a Personal.xls workbook yet. When you do have one, then when you display the macro list it will give you the option to view "All Open Workbooks", "This Workbook", and "Personal.xls".
Normally you will select "This Workbook" to see the macros associated with it. If you select "All Open Workbooks", it shows them in a hopeless jumble.

The Personal workbook is a normal workbook that has macros associated with it just like any other workbook. It gets a little crazy sometimes when you have a macro in Personal with the identical name as one in another workbook, but it's OK to do that. When you execute a macro from the currently active workbook, the system searches through the list for that workbook first. If not found, then it will search other places, and Personal.xls is one of those places.

Personal is apparently unique (but isn't) because the common practice is to store that workbook in a folder that Excel always looks at when it starts up. If there is ANY workbook stored in that folder, then that (those) workbook(s) will be opened automatically, but kept in a "hidden" state, so it's not apparent that it is open. But because Personal will be open, it will be searched for a macro name that is referenced in the active workbook but isn't resident in a module belonging to the active workbook.

If you are using Win XP, then you should store your Personal.xls workbook in folder XLStart, which you will find at:

C:\Program Files\Microsoft Office\Office11\XLStart\Personal.xls

[JonPeltier]

Personal is apparently unique (but isn't) because the common practice is to store that workbook in a folder that Excel always looks at when it starts up. If there is ANY workbook stored in that folder, then that (those) workbook(s) will be opened automatically, but kept in a "hidden" state, so it's not apparent that it is open.

Clarification: Any workbook stored in the XLSTART directory will be opened when Excel starts. They will open normally. PERSONAL.XLS is hidden because its window's state has been set to hidden (Window menu...).

[Zack Barresse]

The Personal workbook is a normal workbook that has macros associated with it just like any other workbook. It gets a little crazy sometimes when you have a macro in Personal with the identical name as one in another workbook, but it's OK to do that...

I don't know if I'd go that far as to say it's okay to rename procedures even though it's in a different workbook. I would say a good solid naming convention without duplication would be a better way to go.

Can't hardly agree anymore with what Jon said. This gets increasingly important the larger the projects get. Even when you start getting more and more modules, it becomes extremely important to be able to find procedures. If that navigation structure isn't mapped with your module names, it will be much more of a PITA (which I learned with an early project I did).

(Nice to see you again Jon!)

[Cyberdude]

I guess once again I didn't make myself clear. I'm NOT advocating multiple macros with the same name. I was just saying it's OK if you do. It won't cause any errors. HOWEVER, it IS a bloody nuisance, so not recommended.

I realized after I posted that Personal is a tad unusual because it always opens hidden, and others might or might not, depending on how the user has set them up. Pfft!

[Aussiebear]

Well next issue is, that through the alcoholic haze I read "PITA" , but have no idea what this means?

Ted

[Zack Barresse]

Hover your mouse over "PITA", the board Glossary will popup a little smart tag which tells you what it means. :)

[Aussiebear]

Well, I asked for that didn't I. LOL.

Ted