1

Consider this code:

Dim array As Variant
array = Range("a1:a1000000").Value2

Knowing that the A column only contains boolean (1 or 0) numbers, what's the most efficient way to shrink the array converting its data type to boolean?

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
6diegodiego9
  • 503
  • 3
  • 14
  • 5
    Write a loop and fill a second array. – Tim Williams Jul 10 '19 at 16:57
  • 3
    There's nothing to gain to shrink the array. Each cell in your range (assuming they are all integers 0..1) will be stored in the array as a `Long` data type. [This excellent explanation](https://stackoverflow.com/a/26409520/4717755) describes how and why this happens. Additionally, the [Microsoft documentation for the `Boolean` type](https://learn.microsoft.com/en-us/previous-versions/office/developer/office2000/aa164500(v=office.10)) shows that it's stored as an integer anyway. – PeterT Jul 10 '19 at 16:58
  • 3
    Much more valuable to *actually* shrink your array to the size that's actually needed, i.e. if the sheet has 8721 rows and you still populate an array with a million rows, *that* is where the gain is. Don't worry about data type sizes, your computer has more than enough memory to handle it. – Mathieu Guindon Jul 10 '19 at 17:41
  • 1
    @PeterT: actually as I can see in the Locals window each cell is stored in Variant/Double type :( – 6diegodiego9 Jul 12 '19 at 10:33
  • Ultimately you are the one who can determine the value to your application on shrinking the array size. The advice given here, especially by @TimWilliams and MattieuGuindon, can guide you to shrink the array as much as you may need. In the bad old days of limited memory and baud rates (if you needed to transmit the data), I'd shrink it to a bit array, so I'm glad memory is much cheaper now :) – PeterT Jul 12 '19 at 12:35

0 Answers0