VBA convert range to array and format date

Question

I am struggling with a conversion to array in VBA :

Dim list As Range
Set list = Range(series, series.End(xlDown))
list.Select
Dim table() As Variant
Set table = list.value

My understanding is that value returns an array right ? I don't get why VBA tells me that I "can't assign to array".

My list range looks like that in Excel at the Select, and i aim at having that as an array so that I can format my dates using something like Format(table.Rows(i).Columns(1).value, "yyyy-mm-dd"), looping on my table.

02-Sep-09   1.00
18-Sep-09   1.00
16-Oct-09   1.00
20-Nov-09   1.00
18-Dec-09   1.00
19-Mar-10   1.00
18-Jun-10   1.00

By the way, is it possible to modify the table in place ?

Thanks !

Answer 1

That error message is popping because you are using SET to fill the array. Drop the SET and it should load.

You can fil directly the array as follows:

Dim table() As Variant
table=Range(series, series.End(xlDown))

Omiting the Select step makes your code safer and faster.

Unfortunately, you can not load the values directly as dates. You will have to loop throu every item of the array and turn them into date.

Answer 2

You are going about this incorrectly. You should use the NumberFormat property of the range to specify the display format. Something like this:

Range("A:A").NumberFormat = "yyyy-mm-dd"

Answer 3

There are a number of problem here.

Problem 1

Set table = list.value

table is not an object so you cannot set it. Try:

table = list.value

Problem 2

series is a VBA keyword associated with charts. Please pick a name, such as MyWSTable, which means nothing to VBA.

Problem 3

A worksheet name is not itself a range. Try:

Dim Table() As Variant
Table = Names("MyWSTable").RefersToRange.Value

Note: you do not need variable list nor do you need to select the range.

Answer to formatting question

The following code will reformat your dates:

For inxrow = 1 To UBound(Table, 1)
  For inxcol = 1 To UBound(Table, 2)
    Table(inxrow, 1) = "ddd d mmm yyyyy"
    Table(inxrow, 2) = ""
  Next
Next

Names("MyWSTable").RefersToRange.NumberFormat = Table

Answer 4

You can remove your select: list.Select

To be sure you won't get errors when assigning a range to an array, you'd better declare your array as a variant:

Dim table As Variant
table = Range(series, series.End(xlDown)).Value