Der Weg über eine Hilfsvariable und auch
If Not IsArray ( resultArray ) Then ReDim resultArray( rowArrayIndex ) Else ReDim Preserve resultArray ( rowArrayIndex )
bringen leider weiterhin den Type missmatch.
Abär:
Da ich sonst schon des öfteren mit Arrays von Objekten gearbeitet habe, habe ich mir für den die Excel-Row eine eigene, simple Klasse erstellt:
| Class ExcelRowData |
| Public columnValues As Variant |
| End Class |
| |
Die nutze ich dann wie folgt:
| Function getData( xlSheet As Variant, columnCount As Integer ) As Variant |
| If columnCount = 1 Then |
| Print "Abbruch, Spaltenanzahl = 0" |
| Exit Function |
| End If |
| |
| On Error GoTo fehler |
| |
| Dim i As Integer |
| Dim currentRow As Integer |
| currentRow = m_titleRowNumber + 1 |
| Dim currentColumn As Integer |
| currentColumn = 1 |
| |
| Dim rowArrayIndex As Integer |
| rowArrayIndex = 0 |
| Dim columnArrayIndex As Integer |
| columnArrayIndex = 0 |
| |
| Dim resultArray() As Variant |
| |
| Dim cellValue As String |
| cellValue = xlSheet.cells( currentRow, m_cancelColumnNumber ).value |
| Do While cellValue <> "" |
| ReDim Preserve resultArray( rowArrayIndex ) |
| 'hier weise ich dem Array Element das ExcelRowData Objekt zu |
| Set resultArray( rowArrayIndex) = me.getColumnDataAsObject(xlSheet, Currentrow, Columncount) |
| rowArrayIndex = rowArrayIndex + 1 |
| currentRow = currentRow + 1 |
| cellValue = xlSheet.cells( currentRow, m_cancelColumnNumber ).value |
| Loop |
| |
| For i = 0 To UBound( resultArray ) |
| Dim rowDataObject As New ExcelRowData |
| Set rowDataObject = resultArray(i) |
| Print i & " = " & Join( rowDataObject.columnValues , "~" ) |
| Next |
| Stop |
| |
| getData = resultArray |
| |
| weiter: |
| Exit Function |
| fehler: |
| MsgBox Error & Chr(13) & "Zeile: " & Erl & Chr(13) & "Nr.: " & Err & Chr(13) & "Modul: " & "getData", 16, "Fehler" |
| Resume weiter |
| End Function |
| Function getColumnDataAsObject( xlSheet As Variant, currentRow As Integer, columnCount As Integer ) As excelRowData |
| On Error GoTo fehler |
| |
| Dim colDataObject As New ExcelRowData |
| colDataObject.columnValues = getColumnDataArray( xlSheet, currentRow, columnCount ) |
| |
| Set getColumnDataAsObject = colDataObject |
| |
| weiter: |
| Exit Function |
| fehler: |
| MsgBox Error & Chr(13) & "Zeile: " & Erl & Chr(13) & "Nr.: " & Err & Chr(13) & "Modul: " & "getColumnDataArray", 16, "Fehler" |
| Resume weiter |
| End Function |
| Function getColumnDataArray( xlSheet As Variant, currentRow As Integer, columnCount As Integer ) As Variant |
| On Error GoTo fehler |
| |
| Dim columnValuesArray() As String |
| Dim columnArrayIndex As Integer |
| Dim i As Integer |
| 'Print "columnCount = " & columnCount |
| ReDim columnValuesArray( 0 To columnCount -1 ) |
| columnArrayIndex = 0 |
| For i = 0 To columnCount -1 |
| columnValuesArray(i) = xlSheet.cells( currentRow, i+1 ).value |
| Next |
| |
| getColumnDataArray = columnValuesArray |
| |
| weiter: |
| Exit Function |
| fehler: |
| MsgBox Error & Chr(13) & "Zeile: " & Erl & Chr(13) & "Nr.: " & Err & Chr(13) & "Modul: " & "getColumnDataArray", 16, "Fehler" |
| Resume weiter |
| End Function |
Also folgere ich: Array von Arrays geht nicht als Rückgabewert einer Funktion bzw. Methode, Array von (eigenen) Objekten funktioniert. Oder ziehe ich die falschen Schlüsse?
Danke für die Antworten!
Gruß
Hubert