[V8i VBA] Creating multiple text elements from 2D array

Unknown said:

I can place a single piece of text but its location appears to be offset and placed at the end of the loop

That's what you've told MicroStation to do.  If we scrape away the useful stuff to see what your TextFromArray is doing...

Dim oText                                As Element
Set oText = TextFromArray(Point)

That statement can only ever create one piece of text.  Your method iterates a loop before returning that text.  Each time it loops, it creates a text element that is subsequently discarded except for the loop's final iteration...

Private Function TextFromArray(Point As Point3d) As Element
    ... 
    For i = 1 To lNumRows
        Set el = CreateTextElement1(Nothing, CStr(ArrayData(1, i)), Point, Matrix3dIdentity)
        Set TextFromArray = el
    Next i
End Function

Rather than return the text element, simply add it to the active model inside your loop.

Once you have all that working, read this article about transactions in MicroStation VBA.

FlexiTable™

You don't have to write code!  Use FlexiTable, which already knows how to read an Excel worksheet and turn it into a table in MicroStation.

Unknown said:

I can place a single piece of text but its location appears to be offset and placed at the end of the loop

That's what you've told MicroStation to do.  If we scrape away the useful stuff to see what your TextFromArray is doing...

Dim oText                                As Element
Set oText = TextFromArray(Point)

That statement can only ever create one piece of text.  Your method iterates a loop before returning that text.  Each time it loops, it creates a text element that is subsequently discarded except for the loop's final iteration...

Private Function TextFromArray(Point As Point3d) As Element
    ... 
    For i = 1 To lNumRows
        Set el = CreateTextElement1(Nothing, CStr(ArrayData(1, i)), Point, Matrix3dIdentity)
        Set TextFromArray = el
    Next i
End Function

Rather than return the text element, simply add it to the active model inside your loop.

Once you have all that working, read this article about transactions in MicroStation VBA.

FlexiTable™

You don't have to write code!  Use FlexiTable, which already knows how to read an Excel worksheet and turn it into a table in MicroStation.

Thanks Jon,

I wasn't sure if multiple elements could be created in memory and then all added to the activemodel at the end of the loop. I've added your suggestion and the text creation now works as I was hoping.

I'm now in the process of amending my TextFromArray function so that I'm not just supplying the datapoint from IPrimitiveCommandEvents_DataPoint event, I also want to supply an array and a point3d offset string from the datapoint to make the function reusable for all the arrays and offsets I will be working with. I'm able to create the text elements from the first array however the data from next array is not creating, it does contain data so I'm unsure if its due to not being able to call the TextFromArray function multiple times in the _DataPoint event. It appears that the function exists after the first array completes its loop instead of returning back to the _DataPoint event as I thought it would.

Private Function TextFromArray(Point As Point3d, ByRef data() As Variant, ByVal sDataPointOffset As String) As Element
    Dim C                                    As Long
    Dim i                                    As Integer
    Dim el                                   As Element
    Dim DataPoint                            As Point3d
    Dim R                                    As Long
    Dim sHorizOffset                         As Point3d
    Dim sOffsetArray()                       As String

    DataPoint = Point    'remember the original data point coordinates
    i = 1
    sHorizOffset = Point3dFromXYZ(5, 0, 0)
    sOffsetArray() = Split(sDataPointOffset, ",", , vbTextCompare)

    Point = Point3dAdd(Point, Point3dFromXYZ(sOffsetArray(0), sOffsetArray(1), sOffsetArray(2)))  'set the offset from the data point for the chainage column

    For i = 1 To lNumRows

        Debug.Print "i=" & i

        Set el = CreateTextElement1(Nothing, CStr(data(i, 1)), Point, Matrix3dIdentity)
        Set TextFromArray = el
        el.Redraw msdDrawingModeNormal
        ActiveModelReference.AddElement el

        Point = Point3dAdd(Point, sHorizOffset)
    Next i
    Debug.Print "Completed first Array"
    Point = DataPoint    ' Reset Point back to original data point location

End Function

Private Sub IPrimitiveCommandEvents_DataPoint(Point As Point3d, ByVal View As View)

    Dim oText_Chainage                       As Element
    Dim oText_Ex_Levels                      As Element

    Set oText_Chainage = TextFromArray(Point, ArrayChainage, "13.5,52.5,0")
    Set oText_Ex_Levels = TextFromArray(Point, ArrayUpTrack, "13.5,49.75,0")

End Sub

Never mind - schoolboy error! The error was the upper boundary for the array using lNumRows which returns the last row in the spreadsheet not the upper boundary in the array. Changing it to For i = 1 To UBound(data, 1) has it working as it should.