Alignment.StationOffset() Function in Excel VBA

[csandersonGKMZ9]

I'm trying to use the .StationOffset VBA function to find out the station and offset of a point in relation to an alignment, all within Excel VBA editor. 

I know within the AutoCAD VBA manager you would simply write out:

Align.StationOffset x, y, station, offset

But the syntax doesn't seem to work within Excel's editor. The problem seems to be that the function gives two outputs and I'm not sure how to code for that within Excel.

Thanks in advance for any help

[Jeff_M]

Can you post your Excel file with the VBA? It's been years since I've worked with that so would take too long to set it up.

[csandersonGKMZ9]

Hi Jeff,

File is attached below, it assumes you have an active drawing with a Pipe Network in it, the spreadsheet is intended to deal with those in the end. I can write something quick dealing only with the alignments if needed.

Thanks for looking into this

Jeff,  

Having problems attaching the xlsm file, I'll post the code below unless there's a way to attach the macro enabled file.

Sub Access_AutoCad()


Dim MyApp As Object
Dim MyDwg As AcadDocument


Set MyApp = GetObject(, "Autocad.Application")
Set MyDwg = MyApp.ActiveDocument


Dim oApp As AcadApplication
Set oApp = MyDwg.Application
Dim sAppName As String

'sAppName will need to change with 2020 to "AeccXUiPipe.AeccPipeApplication.XX.0" the XX being whichever
'version number 2020 uses.
sAppName = "AeccXUiPipe.AeccPipeApplication.12.0"
Dim oPipeApplication As AeccPipeApplication
Set oPipeApplication = oApp.GetInterfaceObject(sAppName)

 'Get a reference to the currently active document.
Dim oPipeDocument As AeccPipeDocument
Set oPipeDocument = oPipeApplication.ActiveDocument

'Creates set of Pipe networks Within the Drawing
Dim oPipeNetworks As AeccPipeNetworks
Set oPipeNetworks = oPipeDocument.PipeNetworks

'Establishes the Working Network
Dim WNet As AeccPipeNetwork
Dim K As Integer

    For P = 0 To oPipeNetworks.Count - 1
        If oPipeNetworks(P).Name = Sheet1.Cells(1, 23).Value Then
        K = P
        End If
    Next P
    
           
Set WNet = oPipeNetworks.Item(K)

Dim WPipes As AeccPipes
Dim WStructures As AeccStructures
Set WPipes = WNet.Pipes
Set WStructures = WNet.Structures
Dim PipeLim As Long
PipeLim = CInt(WPipes.Count) - 1
Dim StrucLim As Long
StrucLim = CInt(WStructures.Count) - 1


Dim stn As Double
Dim Offset As Double

Dim Align As AeccAlignment
Dim Wstruc As AeccStructure
Dim WPipe As AeccPipe
Dim WStrucX As Double
Dim WStrucY As Double


Dim PipeArray(0 To 400, 0 To 8)  As Variant
Dim StrucArray(0 To 400, 0 To 7)  As Variant

Worksheets("Sheet1").Range("A1:V400").ClearContents


        For j = 0 To PipeLim
            
             Set WPipe = WPipes.Item(j)

             PipeArray(j, 0) = WPipe.Description
             PipeArray(j, 1) = WPipe.Style.Name
             PipeArray(j, 2) = WPipe.InnerDiameterOrWidth * 12
             PipeArray(j, 3) = WPipe.StartStructure.Name
             PipeArray(j, 4) = WPipe.EndStructure.Name
             PipeArray(j, 5) = WPipe.StartPoint.Z - (WPipe.InnerDiameterOrWidth / 2)
             PipeArray(j, 6) = WPipe.EndPoint.Z - (WPipe.InnerDiameterOrWidth / 2)
             PipeArray(j, 7) = WPipe.Length2D
             PipeArray(j, 8) = WPipe.Length2D - WPipe.StartStructure.StructureInnerDiameterOrWidth / 2 - WPipe.EndStructure.StructureInnerDiameterOrWidth / 2
             
        Next j



        For w = 0 To StrucLim

             Set Wstruc = WStructures.Item(w)
             Set Align = Wstruc.Alignment
             WStrucX = Wstruc.Position.X
             WStrucY = Wstruc.Position.y
             'Set stn, offset = Align.StationOffset(WstrucX,WStrucY,stn,offset)



             StrucArray(w, 0) = Wstruc.Name
             StrucArray(w, 1) = Wstruc.Style.Name
             'StrucArray(w, 2) = stn
             'StrucArray(w, 3) = offset
             StrucArray(w, 4) = WStrucY
             StrucArray(w, 5) = WStrucX
             StrucArray(w, 6) = Wstruc.RimElevation
        
        Next w


Sheet1.Range("A1:G400").Value = StrucArray
Sheet1.Range("M1:U400").Value = PipeArray


End Sub

[norman.yuan]

In your Excel VBA editor, have you added references to necessary Civil3D COM libraries (AeccXLandLib/AeccXUiLandLib, ... )? If you did, you should have been able to see in Object Browser, like:

[csandersonGKMZ9]

I have them referenced, the error that pops up when I try to run the function is "Compile Error: Expected function or variable". This is making me think its a difference in the syntax between the VBA in excel and Autocad. The StationOffset function gives two outputs and I'm not sure how to set two variable values at once in the Excel VBA.

[Jeff_M]

If you ZIP up a file you should be able to post the ZIP file.

I'm assuming this is the line giving you problems:

'Set stn, offset = Align.StationOffset(WstrucX,WStrucY,stn,offset)

This should work:

Dim stn As double
Dim offset As Double
Align.StationOffset(WstrucX,WStrucY,stn,offset)

Then the 2 variables will contain the values obtained from the alignment. 

[csandersonGKMZ9]

I tried that solution and get a Syntax Error when I try to compile.  

I attached the zip file below, there might just be a simple syntax fix that I am missing but I haven't been able to assign both variables values at once.

[norman.yuan · Accepted solution]

---

@Jeff_M wrote:

If you ZIP up a file you should be able to post the ZIP file.

I'm assuming this is the line giving you problems:

'Set stn, offset = Align.StationOffset(WstrucX,WStrucY,stn,offset)

This should work:

Dim stn As double
Dim offset As Double
Align.StationOffset(WstrucX,WStrucY,stn,offset)

Then the 2 variables will contain the values obtained from the alignment. 

---

The red line should be (no bracket, we have left VBA long enough to be not used to that kind of strange syntax):

Align.StationOffset WstrucX, WStrucY, stn, offset 

[csandersonGKMZ9]

That worked perfectly, thank you both for your help