Writing a Byte Array to a Range in Excel VBA without looping

Question

I am trying to dump an array to a worksheet directly without looping. This is possible for all data types, except a 'Byte' array. Why is this so?

I want to use Byte array, so as to save memory cost (data often exceeds the 2GB excel limit). While I have worked around this problem by dividing into chunks, I want to understand why a Byte array can't be dumped into a range directly.

edit 1:

The array value is either 0 or 1. And I want the output shown as it is. Using a Boolean array gives it as TRUE or FALSE, which I don't prefer. Any optimized(memory and speed) ways to do this?

Sub ArrayPasting()

    Dim byteArray(1 To 3) As Byte
    Dim intArray(1 To 3) As Integer

    For i = 1 To 3
        byteArray(i) = i
        intArray(i) = 2 * i
    Next i

    ActiveSheet.Range(Cells(1, 1), Cells(1, 3)).Value = intArray    'range populated with intArray
    ActiveSheet.Range(Cells(2, 1), Cells(2, 3)).Value = byteArray   'error

End Sub

Bytes arrays are typically for binary data which cannot be represented as printable text so cannot be treated like other types where there is always a readable representation. "so as to save memory cost" probably needs some clarification. — Alex K.
– Alex K., Commented Jul 17, 2018 at 11:48
@AlexK."so as to save memory cost" - Using a byte array would consume lesser memory space than an integer. this way I could reduce the number of chunks that has to be processed and dumped, thereby increasing speed. — Surya
– Surya, Commented Jul 17, 2018 at 12:02

Gary's Student · Accepted Answer · 2018-07-17 12:54:46Z

2

Say we have a REALLY big Boolean array that we want to:

dump into cells
convert to 1/0 (where TRUE is 1)

then something like:

Sub SayBoo()
    Dim Boo(1 To 2, 1 To 2) As Boolean
    Dim r As Range
    Set r = Range("A1:B2")

    Boo(1, 1) = True
    Boo(1, 2) = True
    Boo(2, 1) = False
    Boo(2, 2) = False
    r = Boo

    With r
        .Value = Evaluate("=IF(" & .Address & ",1,0)")
    End With

End Sub

will do it.

answered Jul 17, 2018 at 12:54

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Surya Over a year ago

That's a nice way of doing it. But it takes twice the time (dumping + re-writing).

Gary's Student Over a year ago

@Surya You are correct. The potential savings is the memory storage for the BIG array, not speed of execution.

EvR · Accepted Answer · 2018-07-17 11:48:04Z

2

You could use Application.transpose (in this case):

Sub ArrayPasting()

    Dim byteArray(1 To 3) As Byte
    Dim intArray(1 To 3) As Integer

    For i = 1 To 3
        byteArray(i) = i
        intArray(i) = 2 * i
    Next i

    ActiveSheet.Range(Cells(1, 1), Cells(1, 3)).Value = intArray    'range populated with intArray
    ActiveSheet.Range(Cells(2, 1), Cells(2, 3)).Value = Application.Transpose(Application.Transpose(byteArray))

End Sub

answered Jul 17, 2018 at 11:48

EvR

3,5082 gold badges17 silver badges25 bronze badges

Comments

Varadharajan · Accepted Answer · 2018-07-17 11:53:07Z

0

You cannot store binary array values direclty in Excel. You have to convert them to the format acceptable by Excel. Encode array values as base-64 string. Then you can store it in Excel cell.

Well, if you really need to store binary data in Excel (it's a little bit abusing of Excel. Excel wasn't created for binary data storage) you need to:

Convert binary data to text (you are already doing it by base-64 encoding)
Split text into smaller chunks and store each piece in separate cell. This way one image would be stored in several cells.

answered Jul 17, 2018 at 11:53

Varadharajan

881 silver badge8 bronze badges

1 Comment

Maciej Los Over a year ago

Split text into smaller chunks and store each piece in separate cell - this involves looping, but OP requirement is to NOT use loop.

Collectives™ on Stack Overflow

Writing a Byte Array to a Range in Excel VBA without looping

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related