PDA

View Full Version : Declared public variables have ceased to pick up values



KenWilson
11-11-2019, 04:46 AM
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

SamT
11-11-2019, 12:44 PM
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.

KenWilson
11-11-2019, 10:30 PM
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.

KenWilson
11-11-2019, 10:40 PM
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

SamT
11-12-2019, 02:48 AM
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?

KenWilson
11-13-2019, 03:39 AM
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

paulked
11-13-2019, 04:02 AM
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.

KenWilson
11-13-2019, 04:28 AM
Thnx for the rapid reaction and the information, Paul.
In excreta sum, sed profundum surrexit minor.
Yours, look you!

Ken

paulked
11-13-2019, 05:03 AM
:):thumb

大灰狼1976
11-14-2019, 01:09 AM
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
...