3

I've got the following code extract, I'm trying to copy a range of values onto the declared array and it keeps on giving me the 'can't assign to array' error', 

Dim permittedCurve(0 To 7000) As Variant

permittedCurve = activeWorkbook.Worksheets("Origin").Range("AB6:AB7006").value

I've tried this too,but giving me the same error..

Dim permittedCurve(7000) As Variant

permittedCurve = application.transpose(activeWorkbook.Worksheets("Origin").Range("AB6:AB7006").value)

Could someone please help? I don't really see anything wrong with both approaches. :-(

=============================

Updated

I've tried the following, 

Dim permittedCurve(4) As Variant
Dim indicationCurve(4) As Variant

indicationCurve(0) = 1
indicationCurve(1) = 10
indicationCurve(2) = 100
indicationCurve(3) = 1000
indicationCurve(4) = 10000

'Copying the curves
permittedCurve = indicationCurve

This would still prodcue the same "Can't assign to array" Error... WHY?

Community
  • 1
  • 1
lukieleetronic
  • 623
  • 7
  • 10
  • 23
  • What version of excel is it? Does it have 7000 columns? Also, doesn't your range have 7001 values? Try permittedCurve(7001) – Maertin Feb 14 '16 at 23:57
  • when you declare an array (0 to 7000) the total elements in the array are 7001 which matched with the range I defined. (Row Array), I'm using Excel 2016 64 bit version. Anyway, I did try (7001) and doesn't work.. – lukieleetronic Feb 15 '16 at 00:00
  • @lukieleetronic The only example I saw online of using transpose to do this had them declare the variant (permittedCurve) without a min and max range, then assign an array to it - but apparently you can accomplish the same thing by ReDim'ing the variant - I made an edit to my response – Acantud Feb 15 '16 at 00:12

3 Answers3

1

When you bulk load from a worksheet's cells, you always get a two-dimensioned array. The first rank can be considered the 'rows' and hte second rank as the 'columns'.

dim permittedCurve As Variant
'the following is the same as redim permittedCurve(1 to 3, 1 to 6)
permittedCurve = Range("A1:F3").Value2
debug.print lbound(permittedCurve, 1) & ":" & ubound(permittedCurve, 1)
debug.print lbound(permittedCurve, 2) & ":" & ubound(permittedCurve, 2)
'results from the Immediate window:
1:3
1:6

Given the problems (and overhead) of working with the native TRANSPOSE function, stick with 2-D arrays if you plan to shovel values back and forth from the worksheet en masse.

The updated issue can be resolved with a change in variable declaration.

Dim permittedCurve As Variant  '<~~ just a variant, not specifically a variant array with 5 elements
Dim indicationCurve(4) As Variant

indicationCurve(0) = 1
indicationCurve(1) = 10
indicationCurve(2) = 100
indicationCurve(3) = 1000
indicationCurve(4) = 10000

'Copying the curves
permittedCurve = indicationCurve
'now it's a variant array with 5 elements
  • Oh.. it works. it saves the values as strings into two dimensional array format. is it quite complicated to convert this into a straight single array with double type? – lukieleetronic Feb 15 '16 at 00:38
  • A helper function can do it in a jiffy; you are working completely within memory so loops are not slowed down. The function would have to recognize whether something was 1×6 or 6×1 but the [LBound](https://msdn.microsoft.com/en-us/library/t9a7w1ac(v=vs.90).aspx) and [UBound](https://msdn.microsoft.com/en-us/library/office/gg278658.aspx) functions can determine that by comparing ranks. tbh, I played with this methodology a bit but since I typically work within an array for the main purpose of fast processing to return values to the worksheet, I ended up just staying with the 2-D arrays. –  Feb 15 '16 at 00:45
0

You can do it one cell at a time - It seems like a slow approach, but with only 7000 cells it should run in a fraction of a second

Dim rngTarget As Range
Set rngTarget = ActiveWorkbook.Worksheets("Origin").Range("AB6:AB7006")

Dim permittedCurve(0 To 7000) As Variant

Dim i As Long
For i = 0 To UBound(permittedCurve)
    permittedCurve(i) = rngTarget.Cells(i + 1, 1)
Next i

Edit 1: Learned something new - try:

Dim permittedCurve() As Variant
ReDim permittedCurve(1 To 7001)
permittedCurve = Application.Transpose(Range("AB6:AB7006"))

For i = 1 To UBound(permittedCurve)
    Debug.Print permittedCurve(i)
Next
Acantud
  • 488
  • 3
  • 12
0

I just had a similar error today in Excel 365 (which is why I found this thread), and it looks like it is due to some sort of weirdness with VBA objects. It appears that as long as your worksheet object is separately defined from the range, AND the worksheet is active, the conversion of a range to a variant will be accepted. 

Dim wkb As Workbook:    Set wkb = Workbooks("WorkbookName.xls")
Dim wks As Worksheet:   Set wks = wkb.Worksheets("WorksheetName")
Dim strRange As String: strRange = "A1:Z1"

' Setting a range for a worksheet that's not active gives an error
' Don't know why. Just need to do it.
wks.Activate
Dim rng As Range:       Set rng = wks.Range(strRange)

Dim varRange() As Variant
varRange = rng                                             ' Works
varRange = rng.Value                                       ' Works
varRange = wks.Range(strRange)                             ' Works
varRange = wkb.Worksheets("WorksheetName").Range(strRange) ' Does not work
Zlosk
  • 11
  • 2