Broken Function in VBA

[lforster9999]

Hi All, new here.

I am doing some data organisations and munipulation in Excel. I have created serval arrays in VBA to hold different data. I thought i would create a Function to do this for efficiency and simplicity. The code runs well when not in a function but when in a function it doesnt work. Please see code below.

Any help would be appreciated, still trying to get my head round functions and arrays!


The error being shown is "Type mismatch" and errors on the Redim Preserve in function

Sub AddProcessStageToArray(SourceWorksheet, RawDataArray, LastrowData, WhatStage, ArrayOutput)

Dim o As Long

For i = 2 To UBound(RawDataArray)
    If RawDataArray(i, 13) = WhatStage And RawDataArray(i, 38) <> "NOK" Then
        o = o + 1

        'Dim ArrayName As Variant
        ReDim Preserve ArrayOutput(o)
        ArrayOutput(o) = Application.Index(SourceWorksheet.Range("A1:AO" & LastrowData), i, 0)
        
    End If
Next

End Sub

Sub AddITWToArray()

Dim DataWs As Worksheet: Set DataWs = ThisWorkbook.Sheets("DATA")
Dim PoolOfWeekWs As Worksheet: Set PoolOfWeekWs = ThisWorkbook.Sheets("Pool of the week")

Dim LastrowData As Long: LastrowData = DataWs.Range("A" & Rows.Count).End(xlUp).Row
Dim LastColData As Long: LastColData = DataWs.Cells(1 & DataWs.Columns.Count).End(xlToLeft).Column

Dim LastColDataString As String: LastColDataString = Split(Cells(1, LastColData).Address, "$")(1)

Dim DataRange As Range: Set DataRange = DataWs.Range("A1:" & LastColDataString & LastrowData)
Dim DataArr As Variant: DataArr = DataWs.Range("A1:AO" & LastrowData)

'Loop through Data array, if interview process = PQL, add to table. If interview proces = 1sTITW find postion and add data, if 2ndITW find postion and highlight, if 3rd find postion and highlight

Dim PoolofWeekTableLRow As Long: PoolofWeekTableLRow = PoolOfWeekWs.Range("A" & Rows.Count).End(xlUp).Row
'PoolOfWeekWs.Rows("3:" & PoolofWeekTableLRow).ClearContents

Dim i, o As Long
Dim RowNumberArr As Variant

i = 2
o = 0

'Create PQLArray

'Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Prequalification", PQLArray()
o = 0

'Create 1ITWArray

Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 1", FirstITWArray)
o = 0
'Create 2ITWArray

Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 2+", SecondITWArray)
o = 0
'Create PPLArray

Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 2*", PPLArray)

 

[DanteAmor]

Hi and welcome to MrExcel.

You must declare the variable FirstITWArray like this

Dim FirstITWArray() As Variant

I also suggest you put the counter o = o + 1 after it, so that the array stores the data starting at 0.

Sub AddProcessStageToArray(SourceWorksheet, RawDataArray, LastrowData, WhatStage, ArrayOutput)
  Dim o As Long, i As Long
  For i = 2 To UBound(RawDataArray)
    If RawDataArray(i, 13) = WhatStage And RawDataArray(i, 38) <> "NOK" Then
      'Dim ArrayName As Variant
      ReDim Preserve ArrayOutput(o)
      ArrayOutput(o) = Application.Index(SourceWorksheet.Range("A1:AO" & LastrowData), i, 0)
      o = o + 1
    End If
  Next
End Sub

 

[Norie]

Why not use a function?

Function AddProcessStageToArray(SourceWorksheet, RawDataArray, LastrowData, WhatStage) As Variant
Dim ArrayOutput()
Dim cnt As Long

    ReDim ArrayOutput(1 To UBound(RawDataArray))

    For i = 2 To UBound(RawDataArray)
        If RawDataArray(i, 13) = WhatStage And RawDataArray(i, 38) <> "NOK" Then
            cnt = cnt + 1

            ArrayOutput(cnt) = Application.Index(SourceWorksheet.Range("A1:AO" & LastrowData), i, 0)

        End If
    Next

    If cnt > 1 Then
        ReDim Preserve ArrayOutput(1 To cnt)
        AddProcessStageToArray = ArrayOutput
    End If
    
End Function

You can then use the function like this.

FirstITWArray = AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 1")

 

[lforster9999]

Hi guys, thanks both for your help! Very silly mistake!

One thing i have noticed is the code seems to create a 3D array i believe. In local window it displays as PQLArray(1)(1,1,), then so on to PQLArray(2)(1,1) and so on.
I am unsure why it is doing this as the RawDataArray is a 2D array displayed as (1,1),(1,2) then (2,1), (2,2) etc.

Any ideas? I believe this is stopping some of my code as it wont get the Ubound(PQLArray(2)) due to this i think

 

[lforster9999]

Hi guys, thanks both for your help! Very silly mistake!

One thing i have noticed is the code seems to create a 3D array i believe. In local window it displays as PQLArray(1)(1,1,), then so on to PQLArray(2)(1,1) and so on.
I am unsure why it is doing this as the RawDataArray is a 2D array displayed as (1,1),(1,2) then (2,1), (2,2) etc.

Any ideas? I believe this is stopping some of my code as it wont get the Ubound(PQLArray(2)) due to this i think

Included Code to Create RawDataArray, then use the code above to create specific arrays

Dim DataWs As Worksheet: Set DataWs = ThisWorkbook.Sheets("DATA")
Dim PoolOfWeekWs As Worksheet: Set PoolOfWeekWs = ThisWorkbook.Sheets("Pool of the week")

Dim LastrowData As Long: LastrowData = DataWs.Range("A" & Rows.Count).End(xlUp).Row
Dim LastColData As Long: LastColData = DataWs.Cells(1 & DataWs.Columns.Count).End(xlToLeft).Column

Dim LastColDataString As String: LastColDataString = Split(Cells(1, LastColData).Address, "$")(1)

Dim DataRange As Range: Set DataRange = DataWs.Range("A1:" & LastColDataString & LastrowData)
Dim DataArr As Variant: DataArr = DataWs.Range("A1:AO" & LastrowData)

 

[Norie]

Which code is greeting 3D arrays?

Is it only doing that for PQLArray?

 

[lforster9999]

This code.

Sub AddProcessStageToArray(SourceWorksheet, RawDataArray, LastrowData, WhatStage, ArrayOutput)

For i = 2 To UBound(RawDataArray, 1)
    If RawDataArray(i, 13) = WhatStage And RawDataArray(i, 38) <> "NOK" Then
        o = o + 1

        'Dim ArrayName() As Variant
        ReDim Preserve ArrayOutput(o)
        ArrayOutput(o) = Application.Index(SourceWorksheet.Range("A1:AO" & LastrowData), i, 0)
        
    End If
Next

End Sub