Consulting

Results 1 to 10 of 10

Thread: Declared public variables have ceased to pick up values

  1. #1

    Declared public variables have ceased to pick up values

    Good day, all.
    EDIT: I've just found a missing line of code which solved the problem, so ignore this post.
    Can anyone please give me a clue as to what is happening here.

    While re-organizing the variables in a complex macro I have done something it takes exception to and it now completely ignores the variables declarations I have set up in a dedicated module containing nothing but public and global variable declarations and a few constants. I have tried shifting the code to a new module leaving the original empty but to no avail. I tried moving the code to the general section of the Workbook_Open event handler but it marked all the Constants (e.g. Public Const constSubSpecMarkerColumn as Byte = 3) in nasty bad-tempered red and refused to read any of it.
    I am puzzled. (Emoji scratching head)

    Ken
    Last edited by KenWilson; 11-11-2019 at 04:58 AM. Reason: Solved.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Using Bytes as Row and Column counters is a bad, bad habit. Their indices are natively Longs and the Compiler/CPU must convert them from Bytes every time they are referenced. Not only that but not even xl97 could reference all Columns and Rows with Bytes, Bytes are just too limited.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Quote Originally Posted by SamT View Post
    Using Bytes as Row and Column counters is a bad, bad habit. Their indices are natively Longs and the Compiler/CPU must convert them from Bytes every time they are referenced. Not only that but not even xl97 could reference all Columns and Rows with Bytes, Bytes are just too limited.

  4. #4
    Hi, Sam.

    Thanks for the tip. I will de-byte my code forthwith. My table has about 150 columns and 1500 or so rows, my VBA code uses about a dozen row or column variables. What about using Integer variables? Or is Long really a Must?
    Regards, Ken

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Straight from the XL XP help files
    Byte variables are stored as single, unsigned, 8-bit (1-byte) numbers ranging in value from 0–255.

    The Byte data type is useful for containing binary data.

    Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767.

    Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

    From what I understand, Integers are being phased out of VBA, but I think we will always have Bytes, even if just for binary data, so even if you specify Integer, it will use a Long's worth of memory.

    All Row and Column indices are in Longs, (until you move to 64 bit machines.) If your code is ever run on a sheet with a table deeper than 32K and you use Integers, the maintainer will have fun trying to figure out why it is failing.

    I recommend forgetting Integers exist, their only real purpose, (in VBA,) is to save a Words' worth of memory. but YMMV.

    Isn't RAM nowadays addressed in DWord chunks?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6

    Data types

    From what I understand, Integers are being phased out of VBA, but I think we will always have Bytes, even if just for binary data, so even if you specify Integer, it will use a Long's worth of memory.

    That's what bothers me, Sam. Old habits from my Sinclair Spectrum days cause me to "eek-onomise" on RAM. I am simply not well enough informed to judge what "liberties" I can take with it, so I erred on what I thought was the safe side (Wrong again, Ken!). So thanks for putting me right on that score.

    As for Byte being useful for binary data: I take it that means for values of 1 or zero (True/False)?

    ......
    I recommend forgetting Integers exist, their only real purpose, (in VBA,) is to save a Words' worth of memory. but YMMV.

    At some point I stopped updating my versions of VBA and Office; each one was causing me adjustment challenges out of all proportion to their additional capabilities. But I guess I can shed Integer without shedding too many tears.
    My IT ignorance even extends as far as your "YMMV"; if I'm not getting too far "off-topic", what's that? and is there a useful list of such abbreviations somewhere?

    Yours

    Ken

  7. #7
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I Googled YMMV, it makes sense now!

    YMMV stands for: Your Mileage May Vary. YMMV is a slang acronym you may encounter in online messaging, text messaging, or on social media. It is used as a disclaimer that results/satisfaction level might be different for every person.
    Semper in excretia sumus; solum profundum variat.

  8. #8
    Thnx for the rapid reaction and the information, Paul.
    In excreta sum, sed profundum surrexit minor.
    Yours, look you!

    Ken

  9. #9
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Semper in excretia sumus; solum profundum variat.

  10. #10
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    For byte type variables, except for small range (0 – 255), there is another problem,
    even in the range of 0-255, there will be errors,
    for example like below, it cannot accept a negative step.

    dim i as byte
    for i=2 to 1 step -1
        ...

Tags for this Thread

Posting Permissions

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