2

Firstly, I will point out that the question around redimensioning a multi-dimensional array has been discussed and answered here: Excel VBA - How to Redim a 2D array?.

My issue is that I am trying to apply this answer and it isn't quite going smoothly! The issue is with calling the Function. If I dimension the array before calling the function, Excel just tells me it can't assign to the array (presumably because I'm not telling it which element to assign to). If I don't dimension the array beforehand then the function falls apart when it is looking for the dimensions of the old array...because it doesn't have any, presumably.

I know I can do the below by reversing the way round the array builds and then transposing it but I have need further on to change both dimensions of the array so am trying to get it working here first.

I will admit I'm at 'losing the will to live' stage with this code as I have been battling it for weeks and am an amateur wannabe programmer, so I realise it might be a really simple answer but I can't see it at the moment. Any help gratefully received.

Here's my code (the Sub is called from another sub, where all other variables are defined)

Sub CalculateRank(row, coln, TempSums, TempProducts, Lead_count)
Dim Maj As Double
Dim CompareCount As Integer
Dim CompareArray(1, 1) '**I don't really want to dimension this array before the loop below.

Maj = WorksheetFunction.Round(Range("FJudges") / 2, 0)


For coln = 1 To Lead_count
    CompareCount = 0
    For row = 1 To Lead_count
        If TempSums(row, coln) >= Maj Then
            CompareCount = CompareCount + 1
            CompareArray = ReDimPreserve(CompareArray, CompareCount, 3) '**This is the line that is calling the function (copied directly from the bottom of the page linked above) and giving the error
            CompareArray(CompareCount, 1) = row 
            CompareArray(CompareCount, 2) = TempSums(row, coln) 
            CompareArray(CompareCount, 3) = TempProducts(row, coln) 
        End If
    Next row
Next coln
End Sub
EventDani
  • 23
  • 3

1 Answers1

2

You do need to make it a 2D array before calling that function, but with a Redim statement instead of Dim. The problem is not in the function ReDimPreserve you're using, because it takes an input array and returns another one from scratch. Your problem is in the assignment statement:

CompareArray = ...

VBA does not permit assigning to a static array, which is the case because you declared it as:

Dim CompareArray(1, 1)

You need instead to declare it as a dynamic array, like this:

Dim CompareArray()           ' <--- Optional declaration
ReDim CompareArray(0, 0)     ' <--- First initialization should be with ReDim

p.s.

  • The declaration Dim CompareArray() is optional, but is considered by many as good practice. Basically you can omit it and declare directly using ReDim (even with Option Explicit). Just make sure no other variable with the same name exists in the same scope (which the compiler would alert about if the Dim statement is there).

  • I started with (0, 0) because this is the minimal size, instead of (1, 1) (EDIT: unless you are using Option Base 1 as it appeared in the comment).

A.S.H
  • 29,101
  • 5
  • 23
  • 50