topic Re: Alignment.StationOffset() Function in Excel VBA in Civil 3D Customization Forum

Alignment.StationOffset() Function in Excel VBA

csandersonGKMZ9 — Mon, 24 Feb 2020 18:52:55 GMT

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

Re: Alignment.StationOffset() Function in Excel VBA

Jeff_M — Mon, 24 Feb 2020 20:10:54 GMT

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.

Re: Alignment.StationOffset() Function in Excel VBA

csandersonGKMZ9 — Tue, 25 Feb 2020 15:26:36 GMT

~~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

Re: Alignment.StationOffset() Function in Excel VBA

norman.yuan — Tue, 25 Feb 2020 15:30:04 GMT

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:

Re: Alignment.StationOffset() Function in Excel VBA

csandersonGKMZ9 — Tue, 25 Feb 2020 16:00:17 GMT

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.

Re: Alignment.StationOffset() Function in Excel VBA

Jeff_M — Tue, 25 Feb 2020 17:25:40 GMT

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.

Re: Alignment.StationOffset() Function in Excel VBA

csandersonGKMZ9 — Tue, 25 Feb 2020 17:45:58 GMT

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.

Re: Alignment.StationOffset() Function in Excel VBA

norman.yuan — Tue, 25 Feb 2020 17:49:58 GMT

@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

Re: Alignment.StationOffset() Function in Excel VBA

csandersonGKMZ9 — Tue, 25 Feb 2020 17:55:09 GMT

That worked perfectly, thank you both for your help