CODEWITHSUNDEEP
arraysvbaexcel
Surya
Surya

Reputation: 45

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

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

Upvotes: 2

Views: 1463

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 2

Varadharajan
Varadharajan

Reputation: 88

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:

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

Upvotes: 0

EvR
EvR

Reputation: 3498

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

Upvotes: 2

Related Questions