VBA will not pass array to sub

Question

I am trying to pass 3 values to the 2nd sub, the name of the "channel" every 3 columns are a different data set with a different name. "Test" which is the names of the test file to extract the channels from that data and create graphs from it.

Sub PickTests()

Dim Channame() As String
Dim Amplitude() As String
Dim Integration() As String
Dim LvlCross() As String
Dim MaxAnal() As String
Dim Chans As Double
Dim x As Long
Dim TestList() As Double
Dim i As Long


Worksheets("Channel_List.csv").Activate

For i = 1 To (Cells(i + 2, 1).Value = 0)
    Channame(i) = Cells(i + 1, 1)
    Amplitude(i) = Cells(i + 1, 2)
    Integration(i) = Cells(i + 1, 3)
    LvlCross(i) = Cells(i + 1, 4)
    MaxAnal(i) = Cells(i + 1, 5)
    Chans = i + 1
Next i

Worksheets("HomeSheet").Activate
  ' Set numrows = number of rows of data.
    NumRows = Range("A14", Range("A14").End(xlDown)).Rows.Count
  ' Select cell a1.
    Range("A4").Select
  ' Establish "For" loop to loop "numrows" number of times.
    For x = 1 To NumRows
     TestList(x) = Cells(x, 1).Value
     ActiveCell.Offset(1, 0).Select

Next x

    For i = 1 To Chans
    If Amplitude(i) = y Then
    AmplitudeDistribution(Channame(i), TestList)
    End If

Next i






End Sub


Sub AmplitudeDistribution(Channame As String, Test() As Long)

Dim i, j, y, x As Long
Dim wsname As String
wsname = (Channame & "_Amp_Dist")
x = UBound(Test, 1) - LBound(Test, 1) + 1

Worksheets.Add.Name = wsname
Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.Location Where:=wsname, Name:=Channame


For i = 1 To x
    ActiveChart.SeriesCollection.NewSeries
    Sheets(Test(i) & "_out_Amp_Dist.csv").Activate
    With ActiveSheet
        Set FindColumn = .Range("1:1").Find(What:=Channame, LookIn:=xlValues)
    End With
    y = FindColumn.Row

    For j = 1 To (Cells(y, j + 1).Value = 0)
        NumRow(i) = j
    Next j

    ActiveChart.SeriesCollection(i).Values = ActiveSheet.Range(Cells(y, 1), Cells(y + 1, j))
    ActiveChart.SeriesCollection(i).Name = Channame
Next i



End Sub

I get an error type mismatch error trying to pass TestList to the 2nd sub. How can I get arround this?

Edit: Thank you everyone for your help, I come from a c++ background so didn't realise how dumb dynamic arrays are in VBA! All works perfectly now! (Well not all, but it passes everything to the 2nd sub, no i am just wrestling with charts....)

You dim “TestList() As Double” in calling Sub but then “Test() As Long” in called Sub: either change Double to Long or the opposite — DisplayName
– DisplayName, Commented Aug 16, 2018 at 15:01
Just pass it as a Variant, and use a plural parameter name, i.e. ByRef tests As Variant — Mathieu Guindon
– Mathieu Guindon, Commented Aug 16, 2018 at 15:14
FWIW Dim i, j, y, x As Long is declaring x as a Long, and everything else as an implicit Variant. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 16, 2018 at 15:16
Also, “TestList()” is declared but never sized. You should insert ”ReDim TestList(1 To NumRows)” before “For x = 1 To NumRows” loop — DisplayName
– DisplayName, Commented Aug 16, 2018 at 15:21
I come from a c++ background so didn't realise how dumb dynamic arrays are in VBA! - please don't blame the language. Accessing unitialized C array index is undefined behavior, and For i = 1 To (Cells(i + 2, 1).Value = 0) doesn't iterate once given any value of i, even if the VBA assignment operator returned the assigned value (it doesn't) and was different than the comparison operator (it isn't). We all write bugs and bad code, in any language. And I don't do C++ but if it lets you downcast an array of Double into an array of Long like that, it's C++ that's dumb. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 17, 2018 at 14:08

Mathieu Guindon · Accepted Answer · 2018-08-16 15:40:06Z

DisplayName saw it quick:

The array is declared here:

Dim TestList() As Double

And passed into here:

Sub AmplitudeDistribution(Channame As String, Test() As Long)

You're getting a type mismatch error, because the types.. mismatch. Change the parameter to an array of Double, or declare TestList() as an array of Long.

Or, just pass the array as a Variant:

Sub AmplitudeDistribution(Channame As String, Tests As Variant)

I'd use a plural name regardless of the type here, and since a Variant can wrap literally anything you might want to add a debugging safety net:

    Debug.Assert IsArray(Tests) ' will break here if Tests isn't an array

You're declaring arrays, but these arrays are dynamically-sized, and unless you removed that code for posting, the arrays are never initialized, which means once you get the array into that parameter, the next error will be "index out of bounds" when you try to write TestList(x), or to read Test(i).

Use the ReDim statement to size your arrays:

ReDim TestList(1 To NumRows)

You'll need to do that for all arrays.

This is suspicious:

For i = 1 To (Cells(i + 2, 1).Value = 0)

The expression (Cells(i + 2, 1).Value = 0) is a Boolean expression, so you're looping i from 1 to 0 (False) or -1 (True), which means you're never entering that loop.

Collectives™ on Stack Overflow

VBA will not pass array to sub

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related