Passing two arrays as arguments VBA

Question

I am trying to read a CSV file and split strings into columns of sheet.

So the program reads the CSV, then reads the string (getting the string position and length) and then output the results to the columns.

I did make a macro that does this, but I now want to do this using arrays, which I was not using before, but cant seem to pass the arrays as arguments to the SplitColumns function.

So the string spec is like "start pos, end pos, format", but for now I am not using the format.

Could you help me? I think this might a super simple issue, i am just stuck on this for the last hour.

    Option Explicit

    Public Sub ConvertPanel()

        Dim convertFile As Long, i As Long, y As Long
        Dim specString As String

        Dim a As Variant
        Dim b As Variant
        Dim c As Range

        Set c = Worksheets(1).Range(Cells(1, 1), Cells(1, 1))

        specString = "1,10,@|11,2,@|15,1,@|16,4,@|20,2,@|23,1,@|31,1,@|35,1,@|39,1,@|41,1,@|160,1,@|161,2,@|163,1,@|165,1,@|25,2,@|29,2,@|34,1"

        a = QuickRead("file.txt")
        b = ConvertSpecString(specString)


    End Sub

    Private Function ConvertSpecString(ByVal specString As String) As String()

            Dim fieldsInfo() As String
            Dim inputString As String

            inputString = Replace(specString, Space(1), vbNullString)
            fieldsInfo = Split(inputString, "|")
            ConvertSpecString = fieldsInfo

    End Function

    Private Function QuickRead(ByVal fileName As String) As String()

        Dim fileNumber As Long
        Dim stringRes As String
        Dim fileSize As Long
        Dim v As Variant

        fileNumber = FreeFile
        fileSize = FileLen(fileName)
        stringRes = Space(fileSize)

        Open fileName For Binary Access Read As #fileNumber
            Get #fileNumber, , stringRes
        Close fileNumber

        QuickRead = Split(stringRes, vbCrLf)

    End Function

    Private Function SplitColumns(ByRef lineArray() As Variant, ByRef fieldsInfo() As Variant, ByVal StartCell As Range) As Variant

        Dim indexLine As Long
        Dim indexCount As Long
        Dim stringRange As String
        Dim stringColumn As Long
        Dim fileInfo As String

        stringRange = StartCell
        stringColumn = startCell.Column

            For indexCount = LBound(lineArray) To UBound(lineArray)

                stringColumn = stringRange.Column
                fileInfo = Split(fieldsInfo(indexCount), ",")
                stringRange.EntireRow.Cells(1, stringColumn).Value = Mid(lineArray(indexCount), CLng(fileInfo(0)), CLng(fileInfo(1)))
                stringColumn = stringColumn + 1

            Next indexCount


    End Function

The problem is saying: lineArray() As Variant, you need to pass a variant: lineArray As Variant (which will be a 2D array) or an array: lineArray() As string/long/double — Preston
– Preston, Commented Feb 9, 2017 at 16:36
Thank you, that actually solved the issue! You should submit an answer so I can guive you the points you deserve! @tompreston — svacx
– svacx, Commented Feb 9, 2017 at 17:48
I wasn't sure this would fully answer, but glad it helped, I've written it up below, the linked site is quite helpful for this too. — Preston
– Preston, Commented Feb 9, 2017 at 17:54

Community · Accepted Answer · 2018-05-08 21:49:10Z

1

There's a decent tutorial about this here: http://www.cpearson.com/excel/vbaarrays.htm

Essentially with a Variant if you want to pass a variant to a function you should use the syntax:

Private Function SplitColumns(ByRef lineArray As Variant, ByRef fieldsInfo As Variant, ByVal StartCell As Range) As Variant

To Pass an array you can use the syntax:

Private Function SplitColumns(ByRef lineArray() As String, ByRef fieldsInfo() As Double, ByVal StartCell As Range) As Variant

But you can't pass a Variable like this:

ByRef lineArray() As Variant

Good luck with the project.

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Feb 9, 2017 at 17:53

Preston

8,26511 gold badges64 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing two arrays as arguments VBA

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