Fun: Longest Macro

[kroz]

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]

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

 

[snowblizz]

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]

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]

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]

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]

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]

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.

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]

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

 

[Greg Truby]

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