WICruiser

Dave:

Sorry no experience with VBA here so I will wait patently with the others.

worf928

I just figured out the problem with Office 2003 and the one Sharkskin was having:

You need to have the "Analysis ToolsPak" installed. Tools:Add-ins (select "Analysis ToolsPak"). I didn't need my install CDs to add this on 2003. YMMV.

But, if one or two folks would like to take it for a spin on office 2003 now... go ahead. Just install the ToolsPak and read the first tab with the instructions for security.

One other thing - if you change or add mileage at the top of the sheet, the sheet will not recalculate automatically. You'll need to click on "Calc Now" to recalculate everything. That's the way I wanted it to work. But, it may be confusing.

Post questions or bugs in this thread so that all the Beta testers can see.

bronto

Is there any chance you can digitally sign the spreadsheet? I trust you, but turning off security requires me to trust everyone else too.

SharkSkin

I added the "Analysis ToolPak" and "Analysis ToolPak VBA" for good measure. Works fine now -- I'm going to have to take some time and adjust it to suit the different requirements of a 78 5 speed.

Very nice start you have here, Dave!

How do the calcs know when they've reached the last entry, does it go till it encounters a specific number of blank rows or is the total number of rows hardcoded somewhere? Can I just add as many rows as I need without having to adjust anything?

worf928

The instructions on the first sheet suggest setting the security level to medium. This doesn't turn off security and doesn't require you to trust everyone else. It simply requires that you trust your click-finger. Setting the security to Medium level results in a dialog box popping up when you load any sheet with Macros (or VBA code.) So, in this case when you load the Maintenance Sheet and click on Enable Macros you are enabling macros only for my maintenance sheet. You aren't enabling macros for any and all sheets.

There is a chance. If enough people decide that this micro-application is cool then I will look into it. There is typically a yearly cost involved (at least that is the case for Verisign and signed Java JARs.)


Thanks Dave. Have you tried Print Preview yet?

I tried to structure the application to make it easy for users to customize the Maintenance Events.

Right now the calculations will go to the last row of the sheet. Thus if you insert rows after the last-described item they should be included in the range. If you have lots of blank rows at the end of the sheet the time to do a full recalculation ("Calc Now") will take a long time. The calculations skip blank rows, but if you have 65535 rows in the sheet it still has to look at every row. VBA isn't the speediest thing either.

Also, the sheet has two 'entry points' for recalculation:

1) When you change a cell in the date/item matrix, or the mileage or time interval for a single event, it will recalculate THAT row.
2) When you click on "Calc Now" it will recalculate the whole sheet.

However, when you change mileages, planning date, in-service year, or log-start year (i.e. any of the global variables) it will not automatically recalculate the whole sheet. At some point I'll need to add some usability features to prompt the user to known when they need to force a full recalculation along with dirty bits for changed rows, etc.

In short, if you think the sheet isn't being 'responsive' to a change, try clicking on "Calc Now".

SharkSkin

Haven't tried the print preview, I just entered some stuff I knew off the top of my head and checked to see what it did with the data. I'll probably have more time to play with it next week though.

I thought Excel always had 65535 rows? Maybe you need some kind of stop term, when it finds "END" in col A it stops, or some such.

worf928

I've uploaded the "Version 7" Excel Sheet to this link - right click and Save As...

I also updated the link in the first post.

In this version I
- fixed the calculation of relative maintenance items (e.g. timing belt retension. etc.)
- made the "Calc Now" button a bit smarter. After the first full recalculation it grays itself out until you change something that requires a full recalculation (but it is still active, so if you think you need a full recalc and the button doesn't, you can still force it.)

Since it's a bit of a PITA to copy user data from the old sheet to the new sheet, or to transfer VBA code from one to the other, I'll not post any more changes to the sheet until I accumulate a number of bugs or feature requests.

worf928

Excel (at least 2000 or later) can have a bazillion rows, but it only adds rows (and columns) if it needs to. So, in the sheet I use Excel's concept of how many rows are in use. I could put a magic cookie (i.e. END in some cell) but I'd like to avoid that as much as possible since a magic cookie could get pasted-over inadvertently as you add rows for maintenance items.

SharkSkin

Thanks Dave, I'll take a look at this in the coming week.

worf928

Bump.

Has anyone played with the maintenance sheet? Comments? Complaints? Feature requests?

SharkSkin

I'm hoping I'll have some time to play with it this weekend...

worf928

That's cool Dave.

Everybody else can try it too. No waiting required....

Mark Stahl

Thanks for sharing this...

This may be a silly question, but... there are 4 panes. Which pane do I enter info. into? And the "relative mileage".... is that how many miles driven in each quarter (so if I drive 6,000 miles/year I enter 1500 in each green cell?)

just starting to play with it a little.

Maybe missing: spark plug wires, distrib. caps, shocks, (?tires)?

worf928

It's all the same 'pane' - that's just Excel's 'split window' feature that allows you to see and scroll through different parts of the sheet independently.

Take a look at the 'instructions' tab.

The mileages get entered into Row 7. "Relative" refers to the column. (I guess I'll need to move that string somewhere else.)

There are no hard rules about entering mileages into Row 7. If you have records for mileages in certain quarters - or when certain maintenance events happened - then use them. If not then don't. You could put end of quarter mileages, whatever.

For instance: if you have records from your PO that an oil changed happened in September 1997 at 32,756 miles then enter '32756' in the cell for Q3 1997 (O:7). Scroll down to the row that indicates oil changes and put a 'space' character in the cell that corresponds to oil change and q3 1997. (Cell O:14 if you haven't changed the sheet.) After you hit return the sheet will calculate when the next oil change is due. It will probably put a red 'triangle' in S:14 indicating that the next oil change is due in q3 1998.

The 'prediction' of next-scheduled maintenance items is based upon the interval data for each item (Columns B and C). If you want to change your oil twice a year or every 3k miles then change B:14 and C:14 to "3000" and "1/2".


Probably. I expect the list of items to grow as folks play with it.

And everybody can have their favorite items too. You can customize it to suit. For instance if you want to keep track of when you need to clean your leather seats, then just find an empty row and enter "Clean Leather", "6,000", and "1" into the first three cells in the row. Then put a 'space' in the cell that corresponds to the last quarter in which you cleaned your leather.

Ed Hughes

I've played with it a bit, it seems it wants to endlessly calculate when I enter an event or change a figure. I've not spent a lot of time on it, so maybe there is something I'm missing. But, when it calculates, it seems to go on forever.