Integer, Byte or Long for Columns?

[Paleo]

Hi,

considering excel can manage 65,536 rows and 256 columns, would you agree that a good approach is to use Long data type when refering to rows AND Byte data type when refering to columns (even knowing that our array will never hold all the columns but all less one, as Byte data type can hold only up to 255) because of performance?

[mdmackillop]

While it may be technically correct, it's hard enought to explain the code without the reasoning behind the data type. Having read the Microsoft article, I think I'll just go for "long". It's shorter than Integer anyway!

[Paleo]

Hi MD,

Ok, for rows I would go for "Long" too, my doubt is for columns. Integer, Long or Byte.

[Ken Puls]

I'm voting Long, (having not read the article,) just in case MS decides to add more columns one day!

[mdmackillop]

Given the speed of my typing V the speed of my computer, I think on balance the less characters I type the better. so Columns are Long too.

[Paleo]

Hi guys,

you know what. Lets make it a poll on columns. What do you think? I am creating it.

Well, so far we all have agreed on Long for rows.

[Jacob Hilderbrand]

Why 255?? Anyways Long should be fastest (not that speed matters anymore).

There is no other choice for Rows really. Long is the fastest for Columns as well.

[Paleo]

Hi guys,

well I proposed 255 columns to make it possible to use Byte and other that who uses that many columns?

I dont think Long is faster than Byte, as it is faster than Integer and Byte requires less memory (only one byte). So why not use it?

Check http://msdn.microsoft.com/library/de...rdatatypes.asp

[Paleo]

Are you sure its faster than Byte? Remember that byte costs less memory.

[johnske]

I always try to work with the KISS principle, it's easier to just put long for both rather than having to think - 'now, was it long for columns and byte for rows or was that the other way round?'

After reading the article & the other comments, I agree that any speed and/or memory saving is inconsequental with modern computers, so, Keep It Simple = "Long" for me from now on...

[Jacob Hilderbrand]

If you are writing code you should account for all posibilities. You assume that someone would not use the code for 256 columns so you use Byte, but Byte can only go up to 255 and in a loop only to 254 without error handling.

You could use Integer for rows and just assume nobody would ever use more than 32,768 rows, but why limit your self.

And even if Byte is technically faster, stop and think for a minute about what difference it actually makes. If you are looping 1000's of times we are still only talking about a small fraction of a second difference.

[Paleo]

Gee,ok Jake, you won again. You right, I choose Long too. Thanks guys.

Sorry, for insisting in it but this way I just have learned more. I have readen that article from MS and got in doubt, but now thanks for the support from all of you my doubt is gone. I understood Long is better.

Thanks guys.

[Ivan F Moala]

Hi guys,

well I proposed 255 columns to make it possible to use Byte and other that who uses that many columns?

I dont think Long is faster than Byte, as it is faster than Integer and Byte requires less memory (only one byte). So why not use it?

Check http://msdn.microsoft.com/library/de...rdatatypes.asp

Using the Byte data type is NOT faster then the Long.
Dermot balsome has a workbook displaying this here ....

http://www.webace.com.au/~balson/InsaneExcel/Other.htm

Speed of different variable types (new)

On tests I done long was faster then integer by around 12% and faster then Byte by a whopping 44%. I would have expected this as there is less conversion from Integer to long as from Byte to long. VBA Op codes are 32bit data types so the less conversion the faster the op.

[Paleo]

Hi Ivan,

well looks like I was VERY mistaken on that... Anyway now I have learned more things on it.

[Richie(UK)]

Hi Carlos,

I realise that this thread is marked as 'Solved' but I thought I'd add my 2p worth anyway

Just based upon experience I would have suggested that Longs would have been quicker (and this has been proved to be the case in the responses detailed above).

However, I think the key point here has been made by Jake - for the sake of a fraction of a second in most cases, performance isn't really an important factor. It's far more important to concentrate on the integrity and maintainability of your code - assume that users will do things that you have asked them not to. At the end of the day we should try to use whatever data type is most appropriate for the scenario, not necessarily which one will be the fastest.

[Paleo]

Hi Richie,

agreed on that with you. My point was Byte is less memory consuming not necessarily faster, so you could work with bigger workbooks on weaker computers, but I think you all right. Long is better by being faster and most appropriate for the scenario.

Thanks guys.

[Anne Troy]

assume that users will do things that you have asked them not to.

Now, come on!!
When has that EVER happened to anybody?? Users ALWAYS do exactly what you tell them!!

:rofl :rofl :rofl :rofl

[Paleo]

Now, come on!!
When has that EVER happened to anybody?? Users ALWAYS do exactly what you tell them!!

:rofl :rofl :rofl :rofl

Users, our nightmare :rofl

[Howard Kaikow]

Gee,ok Jake, you won again. You right, I choose Long too. Thanks guys.

Sorry, for insisting in it but this way I just have learned more. I have readen that article from MS and got in doubt, but now thanks for the support from all of you my doubt is gone. I understood Long is better.

Thanks guys.

long is faster than either integer or byte, not to mention that the word objects/properties use longs for column numbers, so why cause needless conversions.

[Paleo]

Hi Howard,

it was just a memory issue, but thats okay, I have learned I was wrong, even if we dont feel the performance improvement the impact on memory can be less dangerous than what a user can do...