+ Reply to Thread
Results 1 to 5 of 5

NOT static variable problem

  1. #1
    Neal Zimm
    Guest

    NOT static variable problem

    In a module with some 40 procedures I have dim'd a lot of variables
    in the declarations section. I am developing this application on a home PC.
    It will execute in a networked environment.

    NONE of the vars are static. ajcdone is dim'd as string.

    in sub "A" I have: ajcdone="y" based on processing status

    in sub "B", called in the same 'overall' macro as A above I have:
    if ajcjdone <> "y" then.....

    In my testing I executed sub A. It ended with ajcdone = to "y" BUT,
    sub B was not executed due to other logic, and the 'overall' macro was
    exited.

    I diddled with the worksheets involved for a couple of minutes and then
    independantly executed sub B, NOT expecting ajcdone to have any value,
    but be null. Well, surprise surprise.

    ajcdone WAS = to "y". How come?
    Is this just bad luck with a memory location? or am I doing something wrong?

    I suppose I can stick a value in a cell in one of the involved worksheets
    and test it, but would rather not.

    Thanks for you help.
    Neal



    --
    Neal Z

  2. #2
    Rowan
    Guest

    RE: NOT static variable problem

    Neal

    Variables dim'd in the declarations section retain there value between
    macros. So if sub A is setting the variable to "y" the value will stay as "y"
    until you set it otherwise of close the file.

    For this reason it may be better to declare the variable in macro A and pass
    it to macro B eg:

    sub A()
    dim ajcjdone as string
    ajcjdone = "y"
    call B(ajcjdone)
    end sub

    sub B(ajcjdone as string)
    'code
    end sub

    Hope this helps
    Rowan

    "Neal Zimm" wrote:

    > In a module with some 40 procedures I have dim'd a lot of variables
    > in the declarations section. I am developing this application on a home PC.
    > It will execute in a networked environment.
    >
    > NONE of the vars are static. ajcdone is dim'd as string.
    >
    > in sub "A" I have: ajcdone="y" based on processing status
    >
    > in sub "B", called in the same 'overall' macro as A above I have:
    > if ajcjdone <> "y" then.....
    >
    > In my testing I executed sub A. It ended with ajcdone = to "y" BUT,
    > sub B was not executed due to other logic, and the 'overall' macro was
    > exited.
    >
    > I diddled with the worksheets involved for a couple of minutes and then
    > independantly executed sub B, NOT expecting ajcdone to have any value,
    > but be null. Well, surprise surprise.
    >
    > ajcdone WAS = to "y". How come?
    > Is this just bad luck with a memory location? or am I doing something wrong?
    >
    > I suppose I can stick a value in a cell in one of the involved worksheets
    > and test it, but would rather not.
    >
    > Thanks for you help.
    > Neal
    >
    >
    >
    > --
    > Neal Z


  3. #3
    Bob Phillips
    Guest

    Re: NOT static variable problem

    Static variables have procedure level scope, and are a way of retaining
    values in a procedure over subsequent calls (they are all initialised on
    entry otherwise). Module level variables are implicitly static, even though
    not explicitly declared as such, and thus retain their values between calls.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Neal Zimm" <[email protected]> wrote in message
    news:[email protected]...
    > In a module with some 40 procedures I have dim'd a lot of variables
    > in the declarations section. I am developing this application on a home

    PC.
    > It will execute in a networked environment.
    >
    > NONE of the vars are static. ajcdone is dim'd as string.
    >
    > in sub "A" I have: ajcdone="y" based on processing status
    >
    > in sub "B", called in the same 'overall' macro as A above I have:
    > if ajcjdone <> "y" then.....
    >
    > In my testing I executed sub A. It ended with ajcdone = to "y" BUT,
    > sub B was not executed due to other logic, and the 'overall' macro was
    > exited.
    >
    > I diddled with the worksheets involved for a couple of minutes and then
    > independantly executed sub B, NOT expecting ajcdone to have any value,
    > but be null. Well, surprise surprise.
    >
    > ajcdone WAS = to "y". How come?
    > Is this just bad luck with a memory location? or am I doing something

    wrong?
    >
    > I suppose I can stick a value in a cell in one of the involved worksheets
    > and test it, but would rather not.
    >
    > Thanks for you help.
    > Neal
    >
    >
    >
    > --
    > Neal Z




  4. #4
    Neal Zimm
    Guest

    Re: NOT static variable problem

    Thanks much Bob, pls see my response to Rowan, (above)
    Neal


    "Bob Phillips" wrote:

    > Static variables have procedure level scope, and are a way of retaining
    > values in a procedure over subsequent calls (they are all initialised on
    > entry otherwise). Module level variables are implicitly static, even though
    > not explicitly declared as such, and thus retain their values between calls.
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Neal Zimm" <[email protected]> wrote in message
    > news:[email protected]...
    > > In a module with some 40 procedures I have dim'd a lot of variables
    > > in the declarations section. I am developing this application on a home

    > PC.
    > > It will execute in a networked environment.
    > >
    > > NONE of the vars are static. ajcdone is dim'd as string.
    > >
    > > in sub "A" I have: ajcdone="y" based on processing status
    > >
    > > in sub "B", called in the same 'overall' macro as A above I have:
    > > if ajcjdone <> "y" then.....
    > >
    > > In my testing I executed sub A. It ended with ajcdone = to "y" BUT,
    > > sub B was not executed due to other logic, and the 'overall' macro was
    > > exited.
    > >
    > > I diddled with the worksheets involved for a couple of minutes and then
    > > independantly executed sub B, NOT expecting ajcdone to have any value,
    > > but be null. Well, surprise surprise.
    > >
    > > ajcdone WAS = to "y". How come?
    > > Is this just bad luck with a memory location? or am I doing something

    > wrong?
    > >
    > > I suppose I can stick a value in a cell in one of the involved worksheets
    > > and test it, but would rather not.
    > >
    > > Thanks for you help.
    > > Neal
    > >
    > >
    > >
    > > --
    > > Neal Z

    >
    >
    >


  5. #5
    Neal Zimm
    Guest

    RE: NOT static variable problem

    Thanks Rowan, your solution worked just fine.
    I went back to the Excel help re: Static, I read it twice and
    NOWHERE does it say anything about 'closing the workbook'.
    I had just assumed that when a macro ended, and you're back to
    working in the worksheets, that the 'memory' slate is wiped
    clean from the macros which ran. Silly me.
    Thanks,
    N



    "Rowan" wrote:

    > Neal
    >
    > Variables dim'd in the declarations section retain there value between
    > macros. So if sub A is setting the variable to "y" the value will stay as "y"
    > until you set it otherwise of close the file.
    >
    > For this reason it may be better to declare the variable in macro A and pass
    > it to macro B eg:
    >
    > sub A()
    > dim ajcjdone as string
    > ajcjdone = "y"
    > call B(ajcjdone)
    > end sub
    >
    > sub B(ajcjdone as string)
    > 'code
    > end sub
    >
    > Hope this helps
    > Rowan
    >
    > "Neal Zimm" wrote:
    >
    > > In a module with some 40 procedures I have dim'd a lot of variables
    > > in the declarations section. I am developing this application on a home PC.
    > > It will execute in a networked environment.
    > >
    > > NONE of the vars are static. ajcdone is dim'd as string.
    > >
    > > in sub "A" I have: ajcdone="y" based on processing status
    > >
    > > in sub "B", called in the same 'overall' macro as A above I have:
    > > if ajcjdone <> "y" then.....
    > >
    > > In my testing I executed sub A. It ended with ajcdone = to "y" BUT,
    > > sub B was not executed due to other logic, and the 'overall' macro was
    > > exited.
    > >
    > > I diddled with the worksheets involved for a couple of minutes and then
    > > independantly executed sub B, NOT expecting ajcdone to have any value,
    > > but be null. Well, surprise surprise.
    > >
    > > ajcdone WAS = to "y". How come?
    > > Is this just bad luck with a memory location? or am I doing something wrong?
    > >
    > > I suppose I can stick a value in a cell in one of the involved worksheets
    > > and test it, but would rather not.
    > >
    > > Thanks for you help.
    > > Neal
    > >
    > >
    > >
    > > --
    > > Neal Z


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1