Export data from an array to a table using VBA

Question

In the Sheet1 i have a table only with the Header of each column but empty values, I run a VBA that creates an Array that has the same number of columns of the table. Is there any way to export all the values of this array to the Excel table at once using the VBA code?

I use the code below to export to Sheet1, wanted to do the same function but export directly into a table in the Sheet1.

 Private Sub Workbook_Open()

 Dim Filename As String, line As String
 Dim i As Integer
 DB_Bovespa_Option = Application.Run("Funcao", Range("DATA"), 1, 0, "Bovespa")
 PrintArray DB_Bovespa_Option, ActiveWorkbook.Worksheets("Sheet1").[A2]
 End Sub

 Sub PrintArray(Data As Variant, Cl As Range)
     Cl.Resize(UBound(Data, 1), UBound(Data, 2)) = Data
 End Sub

Answer 1

You can walk through the array items using the FOR command of the VBA. Just always remember that the array starts at position 0 and not at position 1.

Sub test()

    Dim vetor(2) As String
    vetor(0) = "teste1"
    vetor(1) = "teste2"
    vetor(2) = "teste3"

    For i = 0 To 2
        MsgBox vetor(i)
    Next i

End Sub

In the above example I created an array with 3 positions and had Excel display a message box with the array value.

Instead of using the "print" code on the array position screen you can make the cell save the result. Example:

Thisworkbook.activesheet.range("A" & i).value = vetor(i)

If it is not clear post your spreadsheet because with it I can give you a targeted help.

at.

Answer 2

If you want to use a function that returns an array, use return 'Variant', scale the array internally and call on the sheet using "C+S+Enter".

Function respvetor() As Variant

    '   Um vetor qualquer.
    Dim x(1 To 5, 1 To 1) As Single

    '   Uma variável qualquer.
    Dim v As Variant

    '   Valores do vetor.
    x(1, 1) = 5
    x(2, 1) = 4
    x(3, 1) = 3
    x(4, 1) = 2
    x(5, 1) = 1

    '   Saída da função na forma de um arranjo de dados na planilha.
    '   Deve ser feito com Ctrl+Shift+Enter, como qualquer matriz.
    respvetor = x

End Function

Good luck.