VBA and array structure

[Captain Sensible]

Trying to learn a bit of VBA by fiddling with some of the examples on the forum but my only coding knowledge is with php where I can easily print the array contents to screen to check my multidimensional arrays are formatted as I want them

Is there any way I can dump the vba array structures to the screen to see how they're formatted

[dgs2001]

Captain it would probably be easier to dump your array to a spare blank sheet.

Don't do this when you go live as writing to sheets is a relatively slow process in terms of excel.

If you prefer to see it on the screen then the immediate window in the vba editor is where you need to be .

Duncan

[osknows]

Hello,

If data is meant to be written back to Excel it's generally better to set up the array as 2 dimensional with base 1, eg dim ArrayX(1 to row, 1 to column). It makes it much easier to manipulation.

Having said that most 1 dimensional arrays can be written to a range either directly or using Application.Transpose(ArrayX) first.

A last resort is using a FOR loop. This link is quite useful http://www.dailydoseofexcel.com/archive ... using-vba/

[negapo]

You can do a debug.print Array(i,n) and it will show the value on the imediate window. You can also drag the array (while running the code) to the watch windows and then check all the values inside the array.

[Captain Sensible]

Thanks for the pointers, I'm so used to my arrays being in formats like

Code: Select all

Array
(
    [0] =stdClass Object
        (
            [asianLineId] =0
            [betCategoryType] =E
            [betPersistenceType] None
            [betType] =B
            [bSPLiability] =
            [marketId] =100379764
            [price] =50
            [selectionId] =5760451
            [size] =4.76
        )

    [1] =stdClass Object
        (
            [asianLineId] =0
            [betCategoryType] =E
            [betPersistenceType] None
            [betType] =B
            [bSPLiability] =
            [marketId] =100379764
            [price] =60
            [selectionId] =5228518
            [size] =4.53
        )



)


Etc

I just haven't used VBA arrays so no idea how they're formatted or even stored I just need to see how they're stored so I can get a better idea of how to access them or relevant parts later. Dumping to a text file always makes those things seem much more readable rather than having some assumption of how the dat's stored to me

[Captain Sensible]

My only knowledge of programming is using php but for me as soon as I'd learnt how arrays were structured to either add to or access the info the rest was pretty much a pice of cake to do what i needed. But seeing them echoed to screen makes life alot easier to see you're on the right track when adding or pulling out the info you need , the maths etc is never the hardest part.

[osknows]

The example you've given 'datawise' could be constructed many ways using VBA (and other languages)

eg

i) Variant Array
ii) Array of Arrays
iii) Range
iv) Collection / Dictionary / List

Often it depends what you intend to do with the data in the array that determines the best method to use. Eg do you need to search the array, process the whole array, slice the array etc

[dgs2001]

Speaking personally I find Variant Arrays the most usefull as a range can be written to a variant array in one read of the page and also written back to a page in one write.

Duncan

[dgs2001]

Code: Select all

 Sub From_sheet_make_array()
      Dim myarray As Variant
      myarray = Range("a5:p30").Value

      'Looping structure to look at array.
      For i = LBound(myarray) To UBound(myarray)
         MsgBox myarray(i, 1)
      Next
   End Sub


if you have gruss linked to cell A1 of a sheet this will show you the names and prices etc from myarray

Duncan

[Captain Sensible]

Thanks Duncan. I'll look at them later today after racing's finished