Weekend Forum

Every week I get half a dozen, or so, Excel questions. I invite them via the link a the top of this page labeled “Suggestions”. But I don’t answer the questions, much to the disappointment of the questioner I assume. I just don’t have time. I used to answer questions in the Microsoft’s newsgroups and I was awarded as an MVP because of it. Fortunately for me, the criteria for being an MVP has changed, because I don’t have time to answer questions there either. I did answer two questions on the programming newsgroup today just to see if I still new how.

Why have a link if I’m not going to answer questions? When I created the link, I fully intended to answer those questions. Also, it would stop people from asking unrelated questions in the comments, although that hasn’t stopped entirely. The questions in my Outlook folder where I store such things date back to November of last year. The most recent are two questions from yesterday. All-in-all, there are ninety-three unread messages of 125 total messages in that folder.

Here’s one:

I read your page nearly every day. Lot’s of interesting and useful stuff.

I am using Excel 2007 Beta 2 as much as I can to familiarize myself with the new software. I like a lot of the new features including:

  1. 65k rows (I occasionally draw in data with 100k rows)
  2. Data filtering with unlimited criteria.
  3. Sorting with more than 3 criteria
  4. Right click to change many common parameters. etc

The ribbons are nice, but until you can either import you old menus/buttons or easily add content to the new ribbons, I’m not sure if they’re an improvement for serious Excel users. So far they slow me down a lot. I’m hoping Microsoft will come up with a customize function like the old style drag/drop of previous versions.

My problem is:

I have added customized Excel XP buttons linked to vba code to improve my productivity. If I’m doing some serious work under a deadline, I need to go back to Excel XP. Unfortunately XP and 2007 use the same personal.xls file so once Excel 2007 is opened and closed it asks to update personal file. If you answer “Yes”, it renders the file unreadable in Excel XP. I have tried in vain to find a way to keep a copy of both versions alive. I know Excel 2007 creates a personal.xlsb file if you don’t have a personal.xls file. If I create a macro in Excel 2007 to create the personal.xlsb file and then add my XP personal.xls to the same directory, Excel 2007 will read them both and render the XP version useless.

Any ideas?

My answer would involve installing Virtual PC. What’s yours?

I’ve been driving a rental car for the last 10 days due to an auto accident. Today I found out that the other party’s insurance company isn’t going to pay for my repairs or my rental car. Well, they say they’re not going to pay, but I think they will. The other driver was cited for a moving violation and I wasn’t. Now my insurance company has to pay for the repairs and I have to pay for my deductible and the rental car. Then I have to figure out a way to get reimbursed from the other driver or his insurance company. I really don’t need the hassle, but I’ll bet that’s what they’re counting on. That may be a mistake on their part.

Tomorrow I will cooking baby back ribs on my grill. I’ve never cooked ribs on the grill before, so it could be scary. I’m making the sauce and glaze myself, but using a store-bought rub. I’ll enjoy watching Texas beat Ohio State tomorrow night as I’m eating ribs (or pizza if things go south).

So what do you want to talk about this weekend?

Posted in Uncategorized

17 thoughts on “Weekend Forum

  1. I’ve stopped messing with XL07 so I’m an idiot about it. Is it possible to have a BeforeClose in personal.xls that if the XL version was 12 would branch to a routine that saved it in pre-XL07 format, kind of like the branching routine to make a form modal if post-97 and modal if 97?

    I just checked, and I don’t actually use personal.xls anymore (seems like something I’d remember). I’ve got all my stuff in an addin that I created. Perhaps you could have two addins with the same content, one that’s called by XP, one by 07.

    I’m going to re-post a topic that I raised in the first of these weekend forums that got no response:

    The VBA project I just finished was written at home over hundreds of hours but I use it mostly at work. It’s affectionately called “FlyBar.” It manages all of my addins. When any workbook opens, FlyBar checks to see if the workbook has an addin or menu and, if so, it opens and/or creates them. It also captures all application-level events, checks to see if the workbook has such an event, and if so, runs the event (I thought this would slow things down, but it doesn’t seem to). Before FlyBar opens a workbook’s addin it checks a network location for an updated version (I think I got the idea for this from Charles Williams), which makes it fairly easy to update addins for my 5 or so users.

    I have 2 questions about this: 1) Does that description make sense to anybody? and 2) does anybody else use something like it?

  2. My answer would involve installing Virtual PC. What’s yours?

    Mine would involve forgetting about Personal.*. Just copy the macros to another workbook. Make it a hidden workbook, and put that file in the directory that Excel opens automatically. I forget what it’s called because I never use it. The startup folder?

  3. I made my personal.xls into an add-in, so it doesn’t ask to be saved if anything has changed. It’s not called “personal.xls” anymore. It’s not installed as an add-in, it’s just stored in the XLSTART directory. I never save recorded macros in the personal macro workbook, either, since 98% of the macros I record are to get a snippet for a project I’m working on.

    When Excel 2007 opens and closes, I get errors in the module that does the menus and commandbars. Microsoft is wrong when they say that 2007 puts all the custom commandbars and so forth onto the add-ins tab. Mostly that’s a big mess, and half the stuff doesn’t get there, and I usually hit End instead of Debug, because I have no patience to try to track down the errors. I’m done playing with the beta anyway until the TR comes out. They keep saying “soon”.

    A more rigorous approach would be to create two add-ins, store them in the library folder, and install one in each version of Excel.

  4. No pre-cooking here. I saw this guy on PBS cooking mint julep ribs last weekend. While that sounds disgusting it got me thinking. I like ribs and I like grilling, but I’ve never done them together. About 2 hours before grill time, I will be applying the rub to the meat and soaking the hickory in cider. I’ll preheat the grill to 450 and make smoker pouches – soaked hickory wrapped in aluminum foil. When the smoking starts, I’ll turn the grill down to 300 and just light the outside burners, leaving the two inside burners off. I’ll put the baby backs in a rib rack placed over the unlit burners and cook for about 50 minutes. At that point, I’ll brush on the glaze. Cook some more – about 30 minutes. When done, I’ll sprinkle some more rub on the meat and brush on another coat of glaze. Let sit for 10 minutes and serve with a side of homemade bbq sauce. I’ll post the recipe for the sauce when I’m done, but it will be a basic Kansas City style sauce with whatever kind of whiskey I have in the cabinet.

    Here’s the mint julep guy I saw (beware two seconds of annoying noise) Barbecue University

    I’m open for suggestions, but it has to be done 100% on the grill – a limitation I’ve imposed on myself.

  5. I’ll preheat the grill to 450… and I’ll turn the grill down to 300

    I think I’m in the wrong league, my pal. Where I come, we use charcoal, and temperature adjustment is a job for a 5-variable Excel spreadsheet model. Are you saying that you can just turn a knob, witout doing an F9 recalc? If so, let me know. I’ve got a good patent attorney for you.

    But seriously… I’m eagerly awaiting the follow-up. And I’ll challenge you to a BBQ rib-off any day (as long as it’s in Tucson). Charcoal vs Gas. If you want grilled fall-off-the-bone ribs that aren’t dried out, I can teach you a thing or two. But there’s a click-through EULA that you should read carefully.

  6. I just spent a couple of hours trying, and failing, to make a VBA IDE addin (DLL) using the template in VB6. When I open the VBE it stalls out on the line “Set VBInstance = Application” with a “type mismatch” error. A lot of people have had the same problem out there, but I couldn’t Google an answer. I tried changing the VBInstance declaration and implemented IDTExtensibility2 – whatever that is – but no luck. I tried to add a VB6 reference to VBA Extensibility 5.3 but it was in conflict with another reference. I’m using XL 2003. Has one of you smart people come across this error and its solution?

  7. Dick,

    Since MS VirtualPC now is free it’s a smooth approach:


    1) Yes, it makes sense.

    2) My latest contribution ExcelLoader target the same area although it’s a front loader for Excel where the add-ins can be loaded or unloaded:

    For about 2 years ago I made a solution where different add-ins was loaded depending on what kind of task that was choosen by the end users. It was controled with a plain textfile. There will always be a performance penalty but on the other hand it may be a cheap price to pay for the flexibility and ‘automation’.

    Kind regards,

  8. …I’ll preheat the grill to 450… and I’ll turn the grill down to 300…

    …I think I’m in the wrong league, my pal…

    My grill doesn’t have temperature controls. I don’t use an Excel model, either, to get the temperature right. I just pile the charcoals higher or spread them out.

    A friend of ours had this elaborate grill, must have cost a couple grand, 15 years ago. It was all gas, had flames on three sides (front, bottom, back) for the rotisserie, a side grill for cooking flat, and a couple burners to put a pot on. We called it Three Mile Island.

  9. I have 5 versions of Excel on my machine and since I want all of them to share the same Personal.xls I use the same “Alternate startup file location” in each (or whatever it’s called in 2003 and 2007).

    To not get in-use messages re Personal when two versions or instances of Excel are opened I call this in Personal’s Auto_Open:

    Sub SetRO()
    On Error Resume Next
    ThisWorkbook.Saved = True
    ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
    End Sub

    I don’t get any prompts/warnings, etc in Excel 2007 or any other version so I guess it’s helping with Doug’s issue (that I wasn’t even aware of).

  10. I believe I’ve had your ribs, John, and I would not aspire to do that well my first time. However, I don’t remeber you lighting any charcoals. Am I remembering wrong or did you not bring your A game for me?

    If you want grilled fall-off-the-bone ribs that aren’t dried out…

    I don’t want dried out ribs, but I don’t want them falling off the bone either. My ideal for tenderness is the ability to pull them apart with your hands, but not off the bone. That’s how I like them, but I don’t expect everyone is the same in that regard.

    My results were less than ideal, but here’s what I did:
    1:30 – I put 6 racks of baby back ribs in pans and marinated them in apple cider and fresh squeezed lemon juice. Enough cider to cover the ribs and one lemon per rack. The marinating ribs were covered and refrigerated.
    3:30 – I dried off the ribs and applied the rub. I didn’t make my own rub, but used Emeril’s Rib Rub – about 1 oz per rack. I covered the ribs and refrigerated. I put hickory chips in a bowl with apple cider to soak.
    4:30 – Made four smoker packs (soaked hickory chips wrapped in alum. foil). Turned all four burners to high. When the temp reached 450, put the smoker packs on the grill.
    5:10 – Turned the two middle burners off and reduced the outer burners to medium low.
    5:30 – Put the ribs in a rib rack and placed over the unlit middle burners. Every 15 minutes, I sprayed the ribs with apple cider.
    6:30 – Brushed the ribs with sauce.
    7:10 – Removed the ribs, brushed with sauce, let sit for 10 minutes.
    7:20 – Ate.

    The sauce was a traditiona KC style sweet sauce – or so I thought. The reciped called for ketchup, molasses, brown sugar, tobasco, worcestershire, liquid smoke, mustard, and that’s all I can remember. I substituted chipotle flavored mustard and added a cup and a half of Crown Royal. The sauce came out decidedly less sweet than I anticipated, but I still liked the flavor.

    The ribs tasted great, but they were not as tender as I wanted. There were a couple of problems, I think. First, my grill isn’t big enough for six racks of ribs. The ends of the ribs hung out over the lit burners and cooked faster than the middle. I think if I did four racks and turned them 90 degrees, then the meat would have cooked more consistently. Second, I don’t know how to control the heat on the grill. I turned the outside burners too low, then too high, and it took a while to get it stabilized at about 275.

    Everyone said they liked them. Six racks for 12 adults and there was only about 1/2 a rack left over, so maybe they weren’t just being nice. I won’t be hauling my grill to Tucson for a competition, but I’m excited to give it another go.

  11. Your ribs sound pretty good Dick. Anyhow sorry to hear ’bout the insurence/car situation. I had to deal with that about 2 years ago. Short story I asked them to pay for repairs they said shove it, I told them enjoy speaking with my attorney, they shelled out 3 times what I originally needed to pay for repairs, -1/3 to attorney I had about twice what I needed, bought a new car that was much better than the old one. The thing that pissed me off was that they wouldn’t give me a dime until the attorney came along. Oh well at least they ended up shoving it. Good luck.


  12. You know, I’ve been in a couple of auto accidents over the past few years, none of which I was cited for (not my fault), and have had no trouble getting service from my insurance co. When I called in the claim, I answered a bunch of questions, gave them the other party’s insurance info and that was pretty much it. I went to one of my insurance co’s approved body shops (I found one that waived the deductible) and the insurance co. paid the shop directly. My insurance co. then sought reimbursement from the other party’s insurance co. I might have been out of pocket some for the rental since some of it was also covered under my policy, but I don’t recall.

    If you weren’t cited, then your insurance company should just pay the bill and they should be the ones fighting the other guy’s insurance co., not you. Maybe you need another insurance co.?

  13. Mike: I didn’t have any trouble with my insurance company. Once the other insurer denied the claim, they jumped into action and started fixing my car. They would have acted sooner, but I told them not to. There was no way the other insurer was going to deny this claim. :) I’ve only been with Farmers for a short time, so I have no idea if they’re any good. So far, they’ve been very good. I’m trying to figure out how you got the deductible waived. Someone paid it.

  14. Dick,

    I agree with Mike. Once the other insurance company denied your claim, your insurance company should have immediately contacted them and forced the issue. And maybe that’s what they’re doing (although there would be no deductible for you if that’s the case). Otherwise, it’s a claim against your policy, you have to pay the deductible, and your premiums are at risk for increasing.

    I would talk to your agent again and find out if they are truly running this claim through your policy instead of going after the other insurance company. If they are, ask them why. All IMHO, of course…and maybe NE works differently than AZ when it comes to fault/no-fault/etc.

  15. Since I was in Vegas over the weekend, when I read ‘store bought rub’ I thought DK was referring to something totally different from grilling ribs :). I see you didn’t mention the outcome of that game you were watching; I was so close to putting some money on Ohio State, but alas, by Saturday morning, most of my cash was in the vaults of the Palms, Hard Rock and some dive called O’Sheas.

    Since there was no discussion of intestinal issues, you must have cooked them enough and it sounds like quite a bit of trouble for a lazy guy like you.

  16. I have a problem regarding excel 2007. The excel sheet generated have Missing/Extra Worksheet. Worksheets generated by Office 2007 contains Extra or Less worksheet when compared to the ones generated by Office 2000. How can I solve this bug?

    Your responses will be highly appreciated.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.