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?

Improve this question
3
  • What version of excel is it? Does it have 7000 columns? Also, doesn't your range have 7001 values? Try permittedCurve(7001) Commented Feb 14, 2016 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.. Commented Feb 15, 2016 at 0: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 Commented Feb 15, 2016 at 0:12

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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?
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 and UBound 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.
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
Improve this answer

2 Comments

I know this approach works well, but I really need to use the above approach as I am optimizing to the maximum.
Does it work on your end? It gives me 'Subscript out of range' error.
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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.