Fun: Longest Macro

[MrKowz]

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]

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]

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]

(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)

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]

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]

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]

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]

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]

...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]

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.