Type / With

[Lars-Ove Andersson]

Skall det inte gå att indexera variablerna i nedanstående kod så att man får en snygg loop i With-uttrycket istället?

 

Type typeNode

Property_1 As String

Property_2 As String

Property_3 As String

Property_4 As String

Property_5 As String

Property_6 As String

Property_7 As String

Property_8 As String

Property_9 As String

End Type

 

With gusrNodeArray(intX)

.strDocName = strFileName

.strFilNamn = objOccurrence.Name

On Error Resume Next

bMissingDocument = True

bMissingDocument = objOccurrence.OccurrenceDocument Is Nothing

 

If Not bMissingDocument Then

Set .objDocument = objOccurrence.OccurrenceDocument

.Property_1 = .objDocument.Properties.Item(ExcelRubrik(1)).Value

.Property_2 = .objDocument.Properties.Item(ExcelRubrik(2)).Value

.Property_3 = .objDocument.Properties.Item(ExcelRubrik(3)).Value

.Property_4 = .objDocument.Properties.Item(ExcelRubrik(4)).Value

.Property_5 = .objDocument.Properties.Item(ExcelRubrik(5)).Value

.Property_6 = .objDocument.Properties.Item(ExcelRubrik(6)).Value

.Property_7 = .objDocument.Properties.Item(ExcelRubrik(7)).Value

.Property_8 = .objDocument.Properties.Item(ExcelRubrik(8)).Value

.Property_9 = .objDocument.Properties.Item(ExcelRubrik(9)).Value

Else

.Property_1 = "Not available"

.Property_2 = "Not available"

.Property_3 = "Not available"

.Property_4 = "Not available"

.Property_5 = "Not available"

.Property_6 = "Not available"

.Property_7 = "Not available"

.Property_8 = "Not available"

.Property_9 = "Not available"

End If

.intLevel = intLevel

.intCount = 1

End With

 

For x = 1 To gintNodeCount

With gusrNodeArray(x)

objExcel.Application.cells(x, 1).Value = .Property_1

objExcel.Application.cells(x, 2).Value = .Property_2

objExcel.Application.cells(x, 3).Value = .Property_3

objExcel.Application.cells(x, 4).Value = .Property_4

objExcel.Application.cells(x, 5).Value = .Property_5

objExcel.Application.cells(x, 6).Value = .Property_6

objExcel.Application.cells(x, 7).Value = .Property_7

objExcel.Application.cells(x, 8).Value = .Property_8

objExcel.Application.cells(x, 9).Value = .Property_9

End With

Next x

 

 

Något liknande detta:

 

Type typeNode

Property(10) As String

End Type

 

If Not bMissingDocument Then

Set .objDocument = objOccurrence.OccurrenceDocument

For x = 1 To 9

.Property(x) = .objDocument.Properties.Item(ExcelRubrik(x)).Value

Next x

Else

For x = 1 To 9

.Property(x) = "Not available"

Next x

End If

 

For x = 1 To gintNodeCount

With gusrNodeArray(x)

For y = 1 To 9

objExcel.Application.cells(x, y).Value = .Property(y)

Next y

End With

Next x