# Fun: Longest Macro



## kroz (Nov 30, 2010)

Hey guys,

I have a fun (almost off-topic) question.

What is the longest macro you have ever written for an excel file (with all the sub's and functions) ?

Over 1000 lines? 2000 ?
I got to 670 lines on this file and i still didn't add that many comments to make it easy to read.


----------



## Ruddles (Nov 30, 2010)

Don't worry, your VBA will improve with experience!


----------



## snowblizz (Nov 30, 2010)

There's a limit to how much code one "macro" can contain, though I can't recall the limit. And I've seen people have problems with reaching that limit.


----------



## Sandeep Warrier (Nov 30, 2010)

I had one.... more than 3500 lines.... I shudder everytime I look at it now.... filled with loads of .Select  .... my next big project is to re-write that code........ the code didn't have a single comment...

I couldn't run it in a single module and had to split it across 3 modules and several Subs.


----------



## TinaP (Nov 30, 2010)

My longest is over 2800, split among three modules.  If I wrote it today, however, I'm sure my code would be much tighter with all the knowledge I've gleaned here over the years.


----------



## arkusM (Nov 30, 2010)

My longest (including blank lines) 3840 line a whole bunch of modules. Yikes.
I did learn a whole bunch in that workbook. And I have been slowly rewriting it as I can, with all the cool things I have learned.


----------



## Beezkneez (Nov 30, 2010)

I once got roped into building a calculator for a bank.  Unfortunately, as I developed, the scope of what the calculator was meant to do kept changing.  As a result I kept adding, removing or modifying the code to keep the thing in line with the current requirements.
Once we reached a point where everything seemed to be settled and doing what everyone expected, I took the code and rewrote the entire thing for optimization and added notations.

The code dropped from about 1800 lines without much notation to around 750 lines with notation.  It also took less than 20% of the original time for the calculator to run its batch of calculations.


----------



## Greg Truby (Dec 1, 2010)

kroz said:


> Hey guys,
> 
> I have a fun (almost off-topic) question.
> 
> ...


 
That depends on what you're calling a "macro".  Typically the term refers to one procedure.  As a general rule of thumb, if you have one procedure that is going above say 200 or 300 lines then chances are you're probably trying to do too much in one procedure and probably ought to be breaking it into smaller procedures. 

You shouldn't go over 64K in a code module.  To do so will cause the app to be unstable.  It depends on the density of the lines of course, but I find that when I get between 1,200 & 1,300 lines [including comments] that I'm pretty close to 64K. 

Now, if you're talking overall # of lines in an application, I'm not up there with the big boys like Chip, JKP, Jon, Ron or Tom.  But thus far, about 15K lines of code w/ 9K lines of comments for a total of about 24K lines.   But do note that those 15K lines are spread across 663 procedures.  The largest single procedure I can remember doing is a little over 600 lines, and it's one of the more complex bits of logic I've ever coded up.


----------



## JamesW (Dec 1, 2010)

Off topic a little, but wasn't Windows ME (*shudder*), or something, the longest thing ever written?


----------



## Greg Truby (Dec 1, 2010)

JamesW said:


> Off topic a little, but wasn't Windows ME (*shudder*), or something, the longest thing ever written?


 
No idea, however if Win ME was written in Excel, using the macro recorder, that would certainly explain a lot...


----------



## MrKowz (Dec 3, 2010)

I currently have a procedure with 253 lines, but it is nowhere NEAR my biggest one ever written.  I think my largest one was around 1200 lines.


----------



## Cindy Ellis (Dec 3, 2010)

My longest is a little over 1900 lines, including comments (but not blank lines).  It was used successfully in our manufacturing department for about 5 years with no changes, until Office 2007 
(I really do like Excel 2007 now, but it took a while)


----------



## hatman (Dec 17, 2010)

In a dictator application I built over the course of 3 years, I've got 21,583 lines spread amongst 409 procedures within 21 forms, 23 code modules and 10 class modules.  It is by far the largest most robust application I have ever attempted, let alone completed.  It even has a stand-alone context senstive help file with over 200 separate entries.  And that doesn't count the lines in the Ribbon XML I added when I ported this monster over to XL 2007 (which took a month).


----------



## Greg Truby (Dec 17, 2010)

(a) That's a pretty nice-sized little app you built there, Paul.  I've never actually done a dictator app - that's a lot of work just to lock down the UI.

(b) 





hatman said:


> And that doesn't count the lines in the Ribbon XML I added when I ported this monster over to XL 2007 (which took a month).


 
If you were flipping back and forth between the MS CustomUI editor and XMLNotepad (and possibly other XML editing tools like Notepad++) then Andy Pope's tool is an absolute Godsend.

http://www.andypope.info/vba/ribboneditor.htm

Andy's done a lot of truly brilliant stuff over the years but with this one he's really outdone himself.


----------



## hatman (Dec 17, 2010)

a) I would not trade the lessons learned building this application for anything.  But one of the lessons I learned is that a dictator application should be avoided, if possible.  DA's are incredibly powerful, but they are very tricky.  They are easier to build and maintain under 2007, with teh Ribbon interface, but without the ability to customize/hijack the right-click menu anymore, many of teh advantages are lost, in my opinion.  If you ever choose to build one, step carefully, and keep PED close by.

b)   Whoa.  I'll definitely put that in my back pocket.  I have no ribbon creation on my plate in teh forseeable future, but I'll be installing Andy's addin next time.  For sure and certain.


----------



## MrKowz (Dec 17, 2010)

My ignorance coming into play here, but what is a "dictator application"?  Does it wage war against other applications and take them over forcefully?


----------



## hatman (Dec 17, 2010)

I'm going to steal from Professional Excel Developement by Bullen, Bovey, Green and Wallentin.



> Dictator applications take control of the entire Excel session, modifying the Excel user interface to make it appear and behave like an independent Windows application. By doing this, the dictator applications can leverage Ecel's rich feature set while maintaining a high dgreee of control over the actions that can be performed by the end user.


 
Imagine locking down each and every aspect of the user instraface with Excel, then exposing selected interfaces through your own front end.  Re-write File-> Opne, and File->Save, as well as Copy, Paste, and everything in-between.  It's very daunting, but when done carefully, it can actually SAVE you a bunch of work.


----------



## MrKowz (Dec 17, 2010)

hatman said:


> I'm going to steal from Professional Excel Developement by Bullen, Bovey, Green and Wallentin.
> 
> 
> 
> Imagine locking down each and every aspect of the user instraface with Excel, then exposing selected interfaces through your own front end. Re-write File-> Opne, and File->Save, as well as Copy, Paste, and everything in-between. It's very daunting, but when done carefully, it can actually SAVE you a bunch of work.


 
Wow - sounds crazy, but useful.  Thanks for the info!


----------



## Greg Truby (Dec 17, 2010)

hatman said:


> ...but without the ability to customize/hijack the right-click menu anymore...


 


Admittedly I haven't tried to completely nix the r-click menu, but I certainly continue to modify it under XL2007...

*Snippet from the class module for XL app: WB_Activate Event Handler*

```
'// modify the cell (right-click) menu for matrix worksheets
            '// to allow user to add start/stop times via the matrix
            Set cbCellMenu = Application.CommandBars("Cell")
            Set ctlNew = cbCellMenu.Controls.Add(Type:=Office.MsoControlType.msoControlButton)
            With ctlNew
                .Tag = gc_strCellMenuMtrxCtlTag1
                .Caption = "Add/Edit E&xceptions..."
                .OnAction = "AddEditExceptions"
            End With
            Set ctlNew = cbCellMenu.Controls.Add(Type:=Office.MsoControlType.msoControlButton)
            With ctlNew
                .Tag = gc_strCellMenuMtrxCtlTag2
                .Caption = "Remo&ve Exceptions"
                .OnAction = "RemoveExceptions"
            End With
        End If
```
 
*Snippet from the class module for XL app: WB_Deactivate Event Handler*

```
On Error Resume Next
    Application.CommandBars("Cell").FindControl(Tag:=gc_strCellMenuMtrxCtlTag1).Delete
    Application.CommandBars("Cell").FindControl(Tag:=gc_strCellMenuMtrxCtlTag2).Delete
    On Error GoTo m_appExcel_WorkbookDeactivate_Error
```


----------



## hatman (Dec 17, 2010)

Forgive me for not stating more clearly.  I meant to say that you cannot hijack ALL of the context menus.  Yes, the Cell Menu, and others are still hijackeable... but the ones based on the new OfficeArt cannot be customized.  So as long as you have no drawing objects in your workbook, you are all set, but as soon as you add drawing objects into the mix, it all falls apart.


----------



## Greg Truby (Dec 17, 2010)

Well, that's easy enough to remedy. Just convince management that all of the users of your application need to upgrade to 2010 and that they need to give you the time to make the additional customizations. [However I asked Andy & he said that he hasn't yet had the time to make his tool handle the new stuff for XL2010 ]

http://msdn.microsoft.com/en-us/library/ee691832.aspx


----------



## hatman (Dec 17, 2010)

Umm,yeah, we got upgraded to Excel 2007 in summer 2009.  I expect to be upgraded to 2010 no sooner than 2012.


----------



## Greg Truby (Dec 17, 2010)

hatman said:


> Umm,yeah, we got upgraded to Excel 2007 in summer 2009. I expect to be upgraded to 2010 no sooner than 2012.


 
Hey - the jar of blueberry preserves is half-full my friend, not half-empty. By then there's a good chance Andy will have the 2010 stuff built into his tool. [_to self: when am I gonna learn to quit making food references here - every time I do, I want a snack..._]


----------



## hatman (Dec 17, 2010)

Pro: Andy's tool will be updated.
Con: I won't remember what functions I once had on teh context menu.


----------



## SuperFerret (Dec 20, 2010)

hatman said:


> Imagine locking down each and every aspect of the user instraface with Excel, then exposing selected interfaces through your own front end. Re-write File-> Opne, and File->Save, as well as Copy, Paste, and everything in-between. It's very daunting, but when done carefully, it can actually SAVE you a bunch of work.


 
Sounds like the old system I worked on years ago, whenever it broke it asked you to restart Excel and I never knew why 
Although if that is the case why they couldn't get it to communicate with another Excel spreadsheet for customer documents I'll never know (or the other way round)


----------



## Gerald Higgins (Dec 20, 2010)

Greg Truby said:


> You shouldn't go over 64K in a code module.


 
Apologies for dragging this thread back a couple of days but, as someone who is still learning the basics of VBA, I don't know how to work out the size of a code module. Is there an easy way to do this ? Thanks in advance.


----------



## RoryA (Dec 20, 2010)

Export it as a text file, or I think some of the code documenters will tell you.


----------



## Gerald Higgins (Dec 20, 2010)

OK thanks Rory, I can do the export to text file bit. I'm sorry for being thick on this, but will the size of the text file be (roughly) the same as what Excel thinks it is, in respect of Greg's 64k figure ?


----------



## RoryA (Dec 20, 2010)

Yes, should be near enough.


----------



## Greg Truby (Dec 20, 2010)

Gerald Higgins said:


> Apologies for dragging this thread back a couple of days but, as someone who is still learning the basics of VBA, I don't know how to work out the size of a code module. Is there an easy way to do this ? Thanks in advance.


 
I'm surprised Rory didn't mention this, cuz it's how I do it...

http://www.oaltd.co.uk/VBETools/


----------



## Gerald Higgins (Dec 20, 2010)

Thanks Rorya and Greg. I'm guessing I won't be allowed to download Greg's link, but I can use the text file solution easily enough. It turns out I have some that are much larger than 64k, I probably should clean them up !


----------



## Andy Pope (Dec 22, 2010)

Here is a sinlge liner I posted on DDoE.
http://www.dailydoseofexcel.com/archives/2004/10/13/module-size-limits/

Enter this in the Immediate pane, making sure the code module is active.

```
?len(application.VBE.ActiveCodePane.CodeModule.Lines(1,application.VBE.ActiveCodePane.CodeModule.CountOfLines))/1000
```


----------

