Creating a Dynamic Array from list that may change in size

[dean.brunne]

I want to allow the user to type in a list of cost centres and have
the code create an array from that list. The list may 1 record or it
may be 100. How do I create a dynamic array?

 

[NickHK]

Dean,
Adjust for your required data type:

Public Function SomeFunction(ArrayCount As Long)
Dim MyArray() As Long

ReDim MyArray(1 To ArrayCount)
'etc...

End Function

You will probably need to use LBound/Ubound also. May looking "Option Base"
also.

NickHK

 

[dean.brunne]

Dean,
Adjust for your required data type:

Public Function SomeFunction(ArrayCount As Long)
Dim MyArray() As Long

ReDim MyArray(1 To ArrayCount)
'etc...

End Function

You will probably need to use LBound/Ubound also. May looking "Option Base"
also.

NickHK






- Show quoted text -

Hi Nick Thanks for the help! How do I fill it after I have redim'd
the array? I have created the code below that correctly returns the
correct redim count of 5. How do I fill only the 5 values from the
range?


Function ArrayDimension()
Dim rng As Range
Dim MyArray() As Long

Set rng = Worksheets("TEST").Range("A2:A100")
ArrayCount = Excel.WorksheetFunction.CountA(rng)

ReDim MyArray(1 To ArrayCount)

MyArray = rng.Value

Debug.Print MyArray

I keep getting a type mismatch.

Please advise.

Thanks

 

[NickHK]

Dean,
If you are getting the values from a range, you can do it in a single call.
Note that you will always get a 2 dimensional array though, even if you only
have a single row or column.

Private Sub CommandButton2_Click()
Dim RangeValues As Variant
Dim i As Long
Dim j As Long

RangeValues = Range("A1:A3")

For i = LBound(RangeValues, 1) To UBound(RangeValues, 1)
For j = LBound(RangeValues, 2) To UBound(RangeValues, 2)
Debug.Print i & "," & j & " : " & RangeValues(i, j)
Next
Next

End Sub

NickHK

 

[dean.brunne]

Dean,
If you are getting the values from a range, you can do it in a single call.
Note that you will always get a 2 dimensional array though, even if you only
have a single row or column.

Private Sub CommandButton2_Click()
Dim RangeValues As Variant
Dim i As Long
Dim j As Long

RangeValues = Range("A1:A3")

For i = LBound(RangeValues, 1) To UBound(RangeValues, 1)
For j = LBound(RangeValues, 2) To UBound(RangeValues, 2)
Debug.Print i & "," & j & " : " & RangeValues(i, j)
Next
Next

End Sub

NickHK















- Show quoted text -

Ahh, that's great. Thanks Nick! I was having difficulty
understanding the UBound and LBound in this case. Now it is clear!

Cheers!

 

[dean.brunne]

Ahh, that's great. Thanks Nick! I was having difficulty
understanding the UBound and LBound in this case. Now it is clear!

Cheers!- Hide quoted text -

- Show quoted text -

Hi Nick,

I created the following on your advice and it works on the
debugprint. I wanted to use this array in a multiple of different
subs later. How do I call it in other subs? Example below. Always
gives me subscript out of range.

Function ArrayDimension()
Dim rng As Range
Dim RangeValues As Variant
Dim i As Long
Dim j As Long
Dim a As Long


Set rng = Worksheets("TEST").Range("A2:A100")
ArrayCount = Excel.WorksheetFunction.CountA(rng)

ReDim MyArray(1 To ArrayCount, 1)

RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1)

For i = LBound(RangeValues, 1) To UBound(RangeValues, 1)
For j = LBound(RangeValues, 2) To UBound(RangeValues, 2)
MyArray(i, j) = RangeValues(i, j)
Debug.Print MyArray(i, j)
Next
Next
' I assume here that MyArray is full of the new values. Want to call
the whole array later. In the following sub(small extract of).

End Function

Set DestSh = ThisWorkbook.Worksheets("CSV")
For Each sh In Sheets(MyArray()) "HERE IS WHERE I WANT TO CALL
IT. IT STORES THE SHEET NAMES REQ"D
Last = LastRow(DestSh)

With sh.Range("A6:Q213")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next

Thanks again. Sorry but am new to this.

Cheers,

Dean

 

[NickHK]

Dean,
You need to check of Scope of Variables.
As the array is defined in the sub, once the sub finishes, it falls out of
scope and hence cannot be referenced.

If you need to access its values across many subs, Dim the array in the
Declaration section the module or make it Public in a standard module.

NickHK

 

[dean.brunne]

Dean,
You need to check of Scope of Variables.
As the array is defined in the sub, once the sub finishes, it falls out of
scope and hence cannot be referenced.

If you need to access its values across many subs, Dim the array in the
Declaration section the module or make it Public in a standard module.

NickHK





text -












- Show quoted text -

Hi Nick,

Sorry to get to this so late but was seconded off for a while. I have
declared the array as a public variable in a module and still cannot
get the array called in a sub later. I have both the codes below.
Please advise as I think that it is probably a rookie error on the
syntax somewhere:

Public MyArray As Variant
Public Function CSV()
ArrayDimension
CreateCSV
End Function

Public Function ArrayDimension()
Dim rng As Range
Dim RangeValues As Variant
Dim i As Long
Dim j As Long
Dim a As Long


Set rng = Worksheets("TEST").Range("A2:A100")
ArrayCount = Excel.WorksheetFunction.CountA(rng)

ReDim MyArray(1 To ArrayCount, 1)

RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1)

For i = LBound(RangeValues, 1) To UBound(RangeValues, 1)
For j = LBound(RangeValues, 2) To UBound(RangeValues, 2)
MyArray(i, j) = RangeValues(i, j)
Debug.Print MyArray(i, j)
Next
Next


End Function

Public Sub CreateCSV()

Dim Sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim MyArray As Variant

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("CSV").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "CSV"
For Each Sh In Sheets(MyArray)
Last = LastRow(DestSh)

With Sh.Range("A6:Q281")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
Set DestSh = ThisWorkbook.Worksheets("CSV")
For Each Sh In Sheets(MyArray(i, j))
Last = LastRow(DestSh)

With Sh.Range("A6:Q213")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True

End If

End Sub

 

[NickHK]

Dean,
- Most of your "ArrayDimension" function does nothing but copy values from
one array to another. You can achieve the whole this with a single line.
- Again your problem with MyArray is one of scope. Whilst you have declared
it at the module level, you have also declared (but not populated in any
way) it with the sub, at local level. So this local, empty variable is used.
Remove this local variable.
- Not sure what your code in "CreateCSV" is trying to do. What should be the
values of MyArray at this stage ? It will be a 2-D array.

NickHK

Option Explicit

Public MyArray As Variant

Public Function CSV()
ArrayDimension
CreateCSV
End Function

'You don't really need this in a function now, as it is only a single line
Public Function ArrayDimension()
MyArray = Worksheets(1).Range(Range("A2"), Range("A100").End(xlUp))
End Function

Public Sub CreateCSV()
Dim DestSh As Worksheet

'Dim MyArray As Variant

'Try to reference the desired sheet
On Error Resume Next
Set DestSh = ThisWorkbook.Worksheets("CSV")
'Return to default error handling
On Error GoTo 0

If DestSh Is Nothing Then
'No such sheet..
Application.ScreenUpdating = False
'So create it
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "CSV"
'..Not sure what you are doing here
'..
Application.ScreenUpdating = True
Else
'..Again not sure what you are doing here
'but something a bit different to the If block above ?
'..
End If

End Sub

NickHK

----------------------- CUT ----------------------------------