0

I am working in MS Project using a 2d array. I need to be able to add to the length of the array (mentally in the Y direction). I know that this isn't possible to do and preserve the contents by using redim preserve (array). If this was excel I would use the transpose function to swap it arround so that I could add to the now "x" direction and then transpose it back. However project doesn't have the worksheet transpose fucntion available. So I thought that I would be able to make a function which did the same thing and then call it if the array was running out of space. So I created the below.

    Public Function RedDimPreserveAdd(arr, IncreaseX As Integer, IncreaseY As Integer)

'Dim arr() As String
Dim Temp_Arr() As String

Inc_X = IncreaseX
Inc_Y = IncreaseY


ReDim Temp_Arr(UBound(arr, 2), UBound(arr, 1))
For x = 0 To UBound(arr, 2)
    For y = 0 To UBound(arr, 1)
        Temp_Arr(x, y) = arr(y, x)
    Next y
Next x
ReDim Preserve Temp_Arr(UBound(arr, 2), UBound(arr, 1) + Inc_Y)
ReDim arr(UBound(arr, 1) + Inc_Y, UBound(arr, 2) + Inc_X)
For x = 0 To UBound(Temp_Arr, 2)
    For y = 0 To UBound(Temp_Arr, 1)
        arr(x, y) = Temp_Arr(y, x)
    Next y
Next x
Debug.Print "OK"
End Function

This is called into use by this:

    For r = 0 To UBound(test)
    If r = UBound(test) Then test = ReDimPreserveadd(test, 1, 0)
    If test(r, 0) = "" Then Exit For
Next r

Howver...all seems to work OK in the function when I step through it however it then errors out when returning to the place it was called from. With Run-time error '13' type mismatch. I have tried Public Function RedDimPreserveAdd(arr As Variant, IncreaseX As Integer, IncreaseY As Integer) however this also throws the same erorr.

I am using MS Project 2016 and am fairly new to VBA.

What am I doing wrong?

Edit to add. Thank you for the link however when I use something similar (below) which seems to set the array to the result (as per the linked article and @BigBen in the comments) it fails at the line Arr=newarr. I think this is because the two arrays are of different dimensions. Thus I can't draw the line from the useful learnings in the linked article and my problem :(

Public Function ReDimPreserve(ByRef arr, ByVal idx1 As Integer, ByVal idx2 As Integer)
'this fails on the arr=newarr line.

Dim newArr()
Dim x As Integer
Dim y As Integer

ReDim newArr(idx1, idx2)

For x = 0 To UBound(arr, 1)
    For y = 0 To UBound(arr, 2)
        newArr(x, y) = arr(x, y)
     Next
Next
'ReDim arr(idx1, idx2)
arr = newArr

End Function

Miles
  • 43
  • 7
  • `ReDimPreserveadd` doesn't return the array. When you have `test = ReDimPreserveadd(test, 1, 0)`, `test` is just `Empty` then. Your current setup would work if you changed `test = ReDimPreserveadd(test, 1, 0)` to just `ReDimPreserveadd test, 1, 0`, but if you do that, I'd make `ReDimPreserveadd` a `Sub` instead of a `Function`. – BigBen Sep 08 '22 at 13:38
  • May I ask what you are trying to do in Project that requires a variable 2 dimensional array? – john-project Sep 08 '22 at 20:00
  • @john-project I am building a macro which reviews the dependencies between different workstreams in a project. Unfortunatly the client is currently unwilling to re-order the project into workstream sections. Thus I am reviewing the dependencies of each task to identify if Text20 (Workstream) is different. Having build a list of workstream the 2D array is then totalling up the number of predecesssors for each workstream. This is part of an investigation into the idea of sub plans for each workstream and how many dependencies would be needed. – Miles Sep 09 '22 at 07:31
  • @bigben, sorry for the double post however I wasn't fast enough to edit. To continue my education why would you make it a sub rather than a function? – Miles Sep 09 '22 at 08:03
  • It depends on whether you care about the *return value* of the function. If you do, then your edit should be `ReDimPreserve = newArr`, not `arr =newArr`. If you don't care about the return value, then make it a `Sub`, which doesn't have a return value. – BigBen Sep 09 '22 at 12:47
  • 1
    Miles, thanks for providing insight on what you are trying to do. With regard to your question about modifying the size of an array this is what I suggest. I've written many Project macros that use arrays. A simple way I found to guarantee an array never hits the "out of range" is to do a little analysis of the file to determine the maximum possible array elements. For example, let's say I want to create an array of filtered level one summary lines meeting a specific criteria. I set the array size to be the total number of level one summary lines. Done and done. – john-project Sep 09 '22 at 15:03

0 Answers0