Broken Function in VBA

lforster9999

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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




VBA Code:
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



VBA Code:
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)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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.
Rich (BB code):
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Why not use a function?
VBA Code:
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.
VBA Code:
FirstITWArray = AddProcessStageToArray(DataWs, DataArr, LastrowData, "Candidate Interview 1")
 

lforster9999

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Which code is greeting 3D arrays?

Is it only doing that for PQLArray?
 

lforster9999

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
This code.

VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,285
Messages
5,635,324
Members
416,854
Latest member
jaywrye

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top