Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Assigning a variable a permant value till it is reassigned

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can this be done without a const??

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    declare the variable, instead of using:

    Dim varname as integer

    use:

    Public varname as integer

    This will assign it as a public variable and it will hold its value until it is changed.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have got public at the moment which is fine while in a proceedure but when the proceedure ends the value does to.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    works for me, i assume you are not hoping it will hold its value after the workbook is closed?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no I only want it to hold till the workbook is closed and the value is introduced at the start under workbook open.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i see what you mean, obviously doesn't like declaring publics in workbook or worksheet modules. if you put the public declaration line into a seperate module it works though.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So when the work book opens it assigns the value eg wkbname = car

    and then any proceedure you run wkbname will = car public just doesn't seem to hold the value past any proceedure.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes i have tested this you are right, if the public declaration is in the workbook module with the workbook_open code.

    However, leave this workbook_open code where it is and put the public declaration in a new module (right click the workbook in the project browser of the VBA gui and select "insert > module" to get one of these)... i have tested and it works this way.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    But public declaration is only so it can be shared and that works fine but when one macro is run and that assigns a variable a value it stays until that macro ends and then the value is empty!

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cut and paste the code so i can see, or send me the workbook: david@monnetqos.com

    Thanks.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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