VBA Excel Invalid ReDim/Expected Array with Array of Ranges Module

Question

I have a module that stores an array of Range objects that is called in other modules. While this module is functional, it's sloppy, and I would like the code to be easy to read/edit for future developers. Ideally this would not only be easy to read/edit but also be a range array (as opposed to variant array).

How the module is called(ideally would be 'As Range'):

Sub CallModule()
    '...
    Dim rangeArray As Variant
    '...
    rangeArray = RngArr()
    '...
    Call AnotherModule(rangeArray(count))
End Sub

Current Module:

Public Function RngArr() As Variant
    RngArr = Array(Range("'ActivityTracker'!B12"), Range("'ActivityTracker'!H12"), Range("'ActivityTracker'!B26"), Range("'ActivityTracker'!H26"), Range("'ActivityTracker'!B39"), Range("'ActivityTracker'!H39"), Range("'ActivityTracker'!B53"))
End Function

I am getting a couple of errors when I attempt to put it together,

Returns 'expected array':

 Public Function RngArr() As Range
     ReDim RngArr(0 To 6)   '<---Expected Array
     Set RngArr(0) = Range("'ActivityTracker'!B12")
     Set RngArr(1) = Range("'ActivityTracker'!H12")
     Set RngArr(2) = Range("'ActivityTracker'!B26")
     Set RngArr(3) = Range("'ActivityTracker'!H26")
     Set RngArr(4) = Range("'ActivityTracker'!B39")
     Set RngArr(5) = Range("'ActivityTracker'!H39")
     Set RngArr(6) = Range("'ActivityTracker'!B53")
 End Function

Returns 'Invalid ReDim':

Public Function RngArr() As Variant
   ReDim RngArr(0 To 6) As Range   '<---Invalid ReDim
   Set RngArr(0) = Range("'ActivityTracker'!B12")
   Set RngArr(1) = Range("'ActivityTracker'!H12")
   Set RngArr(2) = Range("'ActivityTracker'!B26")
   Set RngArr(3) = Range("'ActivityTracker'!H26")
   Set RngArr(4) = Range("'ActivityTracker'!B39")
   Set RngArr(5) = Range("'ActivityTracker'!H39")
   Set RngArr(6) = Range("'ActivityTracker'!B53")
End Function

I don't know VBA well enough to know exactly what's going on with these errors and I have a number of these modules that need to be fixed. So if someone could give a quick explanation of why I am getting these errors and how to fix them I would really appreciate it!

EDIT: The purpose of this module is to give global access to the locations of various tables in the worksheet so the locations themselves are what matter, not the values in the cells. But this array is used a few times in the workbook because other modules need access to the tables in order to be able to work properly. Also I know you can reference the tables directly but there are many cases in this particular program that would make referencing tables individually much harder than it needs to be.

it's sloppy, and I would like the code to be easy to read/edit for future developers. - get it to work as intended, and then post the module on Code Review. Good luck! — Mathieu Guindon
– Mathieu Guindon, Commented Jun 3, 2016 at 17:39
Are you ultimately trying to deal with the Range objects or the values from the Range objects? — user4039065
– user4039065, Commented Jun 3, 2016 at 18:21

Tim Williams · Accepted Answer · 2016-06-03 17:43:57Z

4

Public Function RngArr() As Range()
     Dim rv(0 To 6) As Range
     Set rv(0) = Range("'ActivityTracker'!B12")
     Set rv(1) = Range("'ActivityTracker'!H12")
     Set rv(2) = Range("'ActivityTracker'!B26")
     Set rv(3) = Range("'ActivityTracker'!H26")
     Set rv(4) = Range("'ActivityTracker'!B39")
     Set rv(5) = Range("'ActivityTracker'!H39")
     Set rv(6) = Range("'ActivityTracker'!B53")
     RngArr = rv
 End Function

Sub Tester()
    Debug.Print RngArr()(2).Address()
End Sub

answered Jun 3, 2016 at 17:43

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OpiesDad · Accepted Answer · 2016-06-03 17:48:54Z

0

It's not clear what you're trying to do here.

The following code works though:

Public Function testArr() As Variant
     Dim newArr() As Range
     ReDim newArr(1 To 5) As Range
     Set newArr(1) = Sheets("Sheet1").Range("A1")
     testArr = newArr
End Function

Public Sub test()
    Dim myArr As Variant
    myArr = testArr()

End Sub

myArr is still going to be a variant when it gets returned, not a range array if you do it this way, but this seems to match your intent.

answered Jun 3, 2016 at 17:48

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Collectives™ on Stack Overflow

VBA Excel Invalid ReDim/Expected Array with Array of Ranges Module

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related