Export data from an array to a table using VBA

Question

Export data from an array to a table using VBA

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

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

I use the code below to export to Sheet1 , I wanted to do the same function but export directly into a table in 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

array excel vba

asked by anonymous 10.10.2014 / 23:17

2 answers

Improve file_get_contents performance in loops Computer clock

score 1 · Answer 1

You can walk through the array items using the VBA FOR command. Just 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 a 3-position array and had Excel display a message box with the value of the array.

Instead of using the "print" code on the screen the position of the array you can make the cell record 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 targeted help.

at.

score 0 · Answer 2

If you want to use a function that returns an array, use variant return, size the vector internally, and call the worksheet 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.