Announcement

Collapse
No announcement yet.

Excel Problem

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Problem

    I've written PB8 code to replace a VBA subroutine I wrote for Excel 2003. It involves passing a couple of large arrays to a PB DLL (one STRING and one LONG), crunching on them for awhile and passing them back to Excel.

    When I execute the Excel routine, it calls the PB DLL as required, everything works properly and I get a good result back.

    The problem is that if I call the DLL a second or third time it will cause Excel to crash with the error statment "Excel has encountered a problem and needs to close".

    So I've been picking at the problem. I observe that when I run the DLL, the Windows Task Manager shows the DLL routine come into being and run as expected. But when the DLL terminates and passes control back to Excel, Task Manager still shows the DLL as "Running". Hmm...

    I can make things ok, if every time I run the DLL I then tell Excel to open its VBE and click "Reset" to tell the VBA macro to stop running (even though it's already stopped properly and isn't running) and then close the VBE again. At that point the Task Manager says that the DLL is gone. And I can execute the DLL again from Excel if I wish. I can do this any number of times without Excel crashing.

    Yet if I don't go in and manually kill the VBA (that's already stopped), then the DLL stays in the Task Manager and running the DLL a second time kills Excel - or sometimes the second pass works but the third kills Excel.

    Does this sound familiar to anyone? Is there some way I can automate Excel to release the DLL so I can invoke it again?

    Thanks.

    Bill

  • #2
    Sounds familiar to me

    To prevent the problem, I use a Loading routine, and a unloading routine, that create or destroy all handles, threads, or anything other I can think of that may be still running or holding memory and not releasing.

    Another thing you can do is post code or if wanting to try to debug yourself, you can try my errorhandler in the sourcecode forum.
    RunTime Debug and Error Handling Part I - Find that elusive bug
    Engineer's Motto: If it aint broke take it apart and fix it

    "If at 1st you don't succeed... call it version 1.0"

    "Half of Programming is coding"....."The other 90% is DEBUGGING"

    "Document my code????" .... "WHYYY??? do you think they call it CODE? "

    Comment


    • #3
      Originally posted by Cliff Nichols View Post
      To prevent the problem, I use a Loading routine, and a unloading routine, that create or destroy all handles, threads, or anything other I can think of that may be still running or holding memory and not releasing.
      Do I understand you properly to mean that you include some sort of code in the Excel/VBA to terminate handles, threads, etc. after the DLL returns control? If that's the case, can you point me toward specifics. I've trolled through Walkenbach's book tonight, but don't see anthing pertinent to what you're suggesting.

      Thanks.

      Bill

      Comment


      • #4
        Well Kinda sorta..."Depending on what side of the fence you are looking at it from"

        It would help if you had code to post so we can see the problem.

        usually it turns out something not terminated, that was created... (and you can never rely on M$ to clean up after you)
        Engineer's Motto: If it aint broke take it apart and fix it

        "If at 1st you don't succeed... call it version 1.0"

        "Half of Programming is coding"....."The other 90% is DEBUGGING"

        "Document my code????" .... "WHYYY??? do you think they call it CODE? "

        Comment


        • #5
          William
          Been a long time since I wrote DLL's for VBA but I think you have a basic misunderstanding of how a DLL and VBA work.
          Assuming you have no specific load for the dll then VBA will automatically load the dll the first time you make a call to it. VBA will not unload the dll just because you made a call to a function in it, nor should it, you could have 20 functions in the dll all called by different macro's. In effect the dll becomes part of the VBA runtime and is not unloaded until the VBA session is unloaded, so merely passing control back to VBA wont close it as it expects you might need it again, so there should be no problem calling it multiple times.
          It sounds like the problem is that your dll function is not truelly rentrant, thus crashing and so VBA is giving the need to close message. There can be many reasons a function is not truely rentrant ie static or global variables that are not being correctly reset. In your case as this probably relates to your prior question you are probably trying to DIM an absolute array that has already been DIMed on the first call to the function, that will cause an error in PB. Change any DIM statements to REDIM and the problem will hopefully go away.
          John
          Woops, sorry wasn't your prior question but a very similar one by Mr German about DLL's for VB, specifically arrays
          Last edited by John Petty; 19 Oct 2008, 09:48 AM.

          Comment


          • #6
            Originally posted by Cliff Nichols View Post
            It would help if you had code to post so we can see the problem.
            Cliff: My code is too big/involved to wish on anyone else at the moment. I'll spend time paring it down to the minimum that still seems to cause trouble and post that later. If I can pare it down to a toy problem that still crashes that will help me as well as anyone trying to help.

            I built it up a step at a time and all seemed well. Once I was done is the first I tried using running it repeatedly and boom, crash, ...

            Originally posted by John Petty View Post
            It sounds like the problem is that your dll function is not truelly rentrant, thus crashing and so VBA is giving the need to close message. There can be many reasons a function is not truely rentrant ie static or global variables that are not being correctly reset. In your case as this probably relates to your prior question you are probably trying to DIM an absolute array that has already been DIMed on the first call to the function, that will cause an error in PB. Change any DIM statements to REDIM and the problem will hopefully go away.
            John: What you're describing may well be true. I gave absolutely no thought to the code being re-entrant as I *assumed* that when I exited the DLL it would all vanish into the ether and each invocation of it would be fresh. Sounds like that was not a good assumption.

            I'll stare at the code and ponder a bit from that perspective.

            Thanks to both of you. You've given me a path on which to lurch forward.

            Bill

            Comment


            • #7
              The re-entrant business appears to have been at the root of my problems. I changed the arrays from DIM to REDIM, along with a few other minor touches, and it seems to work now. Things are now of the form:
              DIM x() as Long
              vbX = vbArrayFirstElemen(x)
              REDIM x(Sht,Row,Col) as Long at vbX

              On the subject of re-entrance though, what about simple non-array variables I declare in PB using DIM or LOCAL or GLOBAL and which are not shared with Excel? Will they also need something special done to them to avoid re-entrance problems? I'm *assuming* not, but then that's what got me in trouble the first time!

              Thanks.

              Bill

              Comment


              • #8
                Local's are created on every call to the dll so rentrant. Globals remain at the same values you left them when you exit the function so unless your next call to the function puts in the values it needs you can have a problem. Small distinction with arrays GLOBAL x() as single does not actually create anything just tells the compiler the type when your function does redim x(y,z).
                Hope that makes sense.
                None of the variables are actually shared or directly accessable by VBA
                Last edited by John Petty; 19 Oct 2008, 01:07 PM.

                Comment

                Working...
                X