Putting lots of values into an array quickly

[bluto32]

I have a bunch of 50 bytes I would like to write to a file in VBA.

One way of doing this is to enter them in separate cells on a worksheet (say A1 to A50), and write a loop in VBA to write each one to the file.

However, I would rather not have the data on a worksheet in the first place. My plan is to put these 50 bytes into an array using VBA directly, and then write a loop to write each byte to the file.

Something like:

Dim myarray(50) As Byte
Dim n As Integer
 
[COLOR=red]myarray = (1, 4 ,29, 35, 246, [I]...more bytes in here...[/I], 8)[/COLOR]
 
Open "file" For Binary As #1
For n = 1 to 50
     Put #1 , , myarray(n)
Next n

The red line is the one which I can't fix. Is there a way of populating an array quickly i.e. in one or two lines? (I could of course populate the array from the worksheet, but then there's no point using an array any more... )

I used to have a ZX Spectrum (those were the days!!!), and there I would type in:

Data 1, 4, 29, 35, ...

and use the Read statement to pick out the numbers one by one.

Any ideas, apart from using a string instead, and messing about with delimeters?

Thanks,
Bluto

 

[ChrisOswald]

AFAIK, VBA doesn't allow you create an array exactly like that. However, using the Split function and a Variant variable, you can get pretty close to what you want..

Sub FillArray()
    Dim myarray                         As Variant
    Dim n                               As Integer
    myarray = Split("1, 4 ,29, 35, 246, 8", ",")
    Open "file" For Binary As #1
    For n = LBound(myarray) To UBound(myarray)
        'need to convert from string to byte...
        Put #1, , cbyte(myarray(n))
    Next n
End Sub

of course, the other option would be to populate the array 'one at a time'

myarray(0) = 1
myarray(1) = 4
etc

and create those statements with a copy paste from formulas in a worksheet.

Excel Workbook
	A	B
1	1	Myarray(0) = 1
2	4	Myarray(1) = 4
3	29	Myarray(2) = 29
4	55	Myarray(3) = 55
Sheet3

Excel 2007

Cell Formulas
Range	Formula
B1	="Myarray(" & ROW()-1 & ") = " & A1

 

[bluto32]

Thank you, Chris - that's very informative. I shall investigate the Split function - haven't used it yet - and remember to put the CByte in next time.

Your second method is really sneaky - using the worksheet only temporarily to aid entering the VBA code quickly!

Much appreciated,
Bluto

 

[gregtx81]

Unless I'm missing something, the array function should suit your purposes.

myarray = Array(1, 4 ,29, 35, 246, ...more bytes in here..., 8)

 

[bluto32]

Thanks, Greg. This has done the trick after a bit of experimenting.
I couldn't get this to work initially:

Sub test()
  Dim myarray(5) as Integer
  [COLOR=red]myarray = Array(1, 3, 5, 7, 9)       [/COLOR][COLOR=magenta]'gives error "Can't assign to array"[/COLOR]
  Range("A1") = myarray(4)
End Sub

After a bit of googling, I found that it does work if myarray is declared as a variant (but not an array with variant elements):

Sub test()
  [COLOR=green]Dim myarray As Variant[/COLOR]
  myarray = Array(1, 3, 5, 7, 9)
  Range("A1") = myarray(4)
End Sub

A side effect of this is that the value 9 is put into "A1", not 7!!!

It seems that the positions of the elements are numbered 0 to 4 rather than the 1 to 5 I was expecting.

VBA is terrific fun, but sure is confusing sometimes!

Many thanks for helping me find a solution.
Bluto