Redim an Array

[Blobajob88]

Hi, can someone please explain to me in simple terms why you have to ReDim an array. For example

Here, I need to first declare:

Dim TopClients () As Variant
Dim Dimension1 As Long, Dimension2 As Long

Dimension1 = Range("A3", Range("A2").end(xldown)).cells.count -1
Dimension2 = Range("A2",Range("A2").End(xltoright)).cells.count -1

ReDim TopClients(0 to Dimension1, 0 to Dimension2)

Why couldn't I just go:
Dimension1 = Range("A3", Range("A2").end(xldown)).cells.count -1
Dimension2 = Range("A2",Range("A2").End(xltoright)).cells.count -1

Dim TopClients (0 to Dimension1, 0 to Dimension2) As Variant

 

[Eric W]

The short answer is that the Dim statement requires you to use constants, and the ReDim statement allows variables. But on the other hand, you don't need to use Dim first (although I'd recommend it). See here about 2 thirds of the way down:

VBA Dim - A Complete Guide - Excel Macro Mastery

This post provides a comprehensive guide to using the VBA Dim statement. The quick guide table shows you the format of the DIm statement and all the ways it can be used.

excelmacromastery.com

You could also do something like this:

Sub test1()
Dim TopClients As Variant

    TopClients = Range("B3").Resize(Cells(Rows.Count, "A").End(xlUp).Row - 2, Cells(2, Columns.Count).End(xlToLeft).Column - 1).Value
End Sub

Where you define the array, and load it with data all in one line. This is a 1-based array though.

 

[Blobajob88]

Thanks Eric. Seems a much simpler solution