Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xla running deleted vba code (excel 2003 SP2)

    I could give more details later but basically the problem is described as following (new version of the spreadsheet runs "old" xla code although it is linked to the "new" xla. The "old" xla is nowehere on my computer, temp directory or the network. ). However, before reading it, please keep in mind that it is important we differentiate between the spreadsheet and the xla.

    1. I deleted some vba code in the XLA linked to my spreadsheet
    2. Checked the xla into version control system. Let us call the spreadsheet at this time version 1.
    3. In about version 5 -6 of the spreadsheet (i.e. after checking it in with different changes a few more times), the xla code from version 1 is apparently run
    4. Did some google searching but there is no definite solution. Among the possible culprits I found was that spreadsheet stores the XLA code and just hitting recompile will store the "new" xla code in the spreadsheet. This solution seemed promising but did not work as in version 11 of the spreadsheet ( we recompiled in version 7 of the spreadsheet) the "old" xla code corresponding to version 1 was run again.

    Was wondering if anyone can shed some light on this matter and give us a definite answer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    Welcome to Woody's Lounge!

    Excel never stores code from an add-in (.xla) in a workbook that uses the add-in. If you are absolutely sure that you haven't put the old code in the workbook by accident, I would suspect that the version control system is the culprit - the old version of the add-in is probably in there.

  3. #3
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    You raise a very interesting point. What do you mean when you say "the old version of the add-in is probably in there". In where? the source control just put it back in there somehow ? we are storing in binaries and not actual text ....

    First of all we wrote both the spreadsheet and the xla. Secondly, the xla was just changed once and stored while the spreadsheet changed and stored multiple times (please not xla has just 2 versions, "old" and "new"). Thirdly, the spreadsheet and not the xla worked fine for a few subsequent checkins and suddenly started "refering" to old code despite the reference being to the new xla (both "new" and "old" xla have the same name, but we can tell which one is which by looking at the code) .

    Now, basically saying "the source control systemi s the culprit" is a pretty big statement. This system is very very widely known and used, is in a mature state for many years and now we are basically saying it cant store binaries without currupting them. It also does not know the difference between binaries. Lastly, the biggest loop in the theory is that it DID store just 2 xla versions fine because subsequent versions of the spreadsheet worked while referring to the "new" version. Then how come suddenly the spreadsheet started running old code when all this time the "new" xla is sitting on the filesystem and the source control ?????

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    I have no conclusive evidence, obviously, but the workbook must get the old code from somewhere. I know for certain that a workbook doesn't store or cache code from an add-in, so it must get the code from what it thinks is the add-in.

  5. #5
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    And this is my wholepoint, from where ? (did not mean to sound aggressive). Are you saying the "new" xla has both versions of the code and a very later version of the spreadsheet just brings out this problem .
    I even put a breakpoint in the xla function we changed and in the "broken" version of the spreadsheet did not hit that function at all ?

    In the meantime I was doing some more researching (in addition to countless hours spent before) and found the following thread which is saying similar things (read the last post especially)

    http://www.themssforum.com/ExcelProgrammin...e-Excel-807203/

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    Is there any chance that you can test the workbook and add-in for a while in an environment without the version control system?

  7. #7
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    Again I am lost by that line :-(

    The procedure is that the spreadsheet and the xla are taken from the source control system onto the filesystem and then opened (There is no source control involved when the spreadsheet is running).

    I do however have 1 xla and 2 spreadsheets, the first spreadsheet which shows the "new" code run and the other which shows the "old" code run (both linked to the same exact xla). Can I determine the problem using there three files ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    If you open the workbook that runs the old code, and activate the Visual Basic Editor, can you view the code in the xla or is it protected? If you can view the code, do you see the new or the old code?

  9. #9
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    Ofcourse I see the new code. But like I said before, setting a breakpoint in the "new" code (in the XLA) is not actually hit and the results in the spreadsheet is shown of the "old" code.

    Now the twist is, setting the breakpoint in the spreadsheet before the changed function is called in the xla, make the "new" version of the code run.

    Again, the distinction to be made here is where the breakpoint is set: in the changed function in the XLA or before calling the changed function from the spreadsheet. One more point to note is that the breakpoint is set "before" calling the changed function in the XLA and not after.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    I'd search the code in the workbook itself to see if there are procedures or functions with the same name as those in the add-in.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    I have the feeling your xla is corrupt.

    What I would do to rule that out:

    - Extract the new xla from your source control
    - Export all code modules, userforms, class modules
    - Copy worksheet event code and thisworkbook event code to a Word doc
    - Open a fresh workbook
    - Import all code modules/forms/classes
    - Paste the TWB and sheet code back from Word into the proper event code windows
    - Save workbook
    - Save-as xla.

    BTW: I never work in xla's directly, I always develop in xls, then when done save-as xla, AFTER running Bovey's code cleaner.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    nope. ruled that possibility out. I think what I am trying to convey is that we have done the usual debugging and frankly put in a lot of answers in online research without coming up with a solution. Infact, read the following thread (exact same problem we have but in 2004. Their code cleaner solution does not work for us)

    http://www.tech-archive.net/Archive/Excel/...04-03/0470.html

  13. #13
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    I might try it, but what does it prove ? Isnt all the code written in text supposed to run and not random deleted code ? Basically, if this works, are we saying that we dont trust how Excel cant keep the text code and the binary in sync ? Isnt it supposed to or am I missing something ?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    I don't think Pieterse was out to prove anything, but you've obviously got an unusual problem on your hands - it's not one of those for which we've got a standard reply in stock. So we're suggesting various things to try in the hope that one of them will get rid of the problem.

  15. #15
    New Lounger
    Join Date
    Feb 2008
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla running deleted vba code (excel 2003 SP2)

    again, did not want to sound aggressive and definitely appreciate all the help I got from here (really do) and I think the solution will work too. However, what I was pointing out in my earlier post was that given that his suggestion does work, basically expects end users of excel to do flaky debugging and code cleaning when excel really should be keeping the text code we type and the binary code it generates in sync ?

    Secondly, even the bigger problem is how come spreadsheet A breaks while linking to a corrupt xla while spreadsheet B does not (linking to the exact same corrupt xla) ? If the xla is *really* corrupt then both the spreadsheets so break *all the time* and not sporadically ? This behaviour (although might go away with his suggestion) is very interesting and is again suggestive of some little use case in excel which the excel developers overlooked somehow .....

    Thoughts ?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •