VBA reads cella value ok, but assigns a point value as 0

[nando88]

I am trying to loop through a number of cells with vba, and return each cell's value. I used the watch windows, and saw that even though the cell().value returned a number different than 0, the a point array of double was assigned a value of 0.

This is the code that I got from a site and edited to my needs:

Sub Draw3DPolyline(r1 As Long, r2 As Long)

    Dim acadApp                 As Object
    Dim acadDoc                 As Object
    Dim LastRow                 As Long
    Dim acad3DPol               As Object
    Dim dblCoordinates()        As Double
    Dim i                       As Long
    Dim j                       As Long
    Dim k                       As Long
    Dim objCircle(0 To 0)       As Object
    Dim CircleCenter(0 To 2)    As Double
    Dim CircleRadius            As Double
    Dim RotPoint1(2)            As Double
    Dim RotPoint2(2)            As Double
    Dim Regions                 As Variant
    Dim objSolidPol             As Object
    'Dim point1(0 To 2) As Double
    'Dim FinalPosition(0 To 2)   As Double
    'Dim FinalPosition2(0 To 2)   As Double
    Dim Sheet1 As Worksheet
    
    'Activate the coordinates sheet and find the last row.
    Set Sheet1 = Sheets("Coordinates")
    
    LastRow = r2
    
        
    'Check if there are at least two points.
    'If LastRow < 3 Then
    '    MsgBox "There are not enough points to draw the 3D polyline!", vbCritical, "Points Error"
    '    Exit Sub
    'End If
    
    'Check if AutoCAD application is open. If not, create a new instance and make it visible.
    On Error Resume Next
    Set acadApp = GetObject(, "AutoCAD.Application")
    If acadApp Is Nothing Then
        Set acadApp = CreateObject("AutoCAD.Application")
        acadApp.Visible = True
    End If
    
    'Check if there is an AutoCAD object.
    If acadApp Is Nothing Then
        MsgBox "Sorry, it was impossible to start AutoCAD!", vbCritical, "AutoCAD Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Check if there is an active drawing. If no active drawing is found, create a new one.
    On Error Resume Next
    Set acadDoc = acadApp.ActiveDocument
    If acadDoc Is Nothing Then
        Set acadDoc = acadApp.Documents.Add
    End If
    On Error GoTo 0
        
    'Get the one dimensional array size (= 3 * number of coordinates (x,y,z)).
    ReDim dblCoordinates(1 To (3 * LastRow))
    
    'Pass the coordinates to the one dimensional array.
    k = 1
    For i = r1 To r2
        For j = 1 To 3
            If j = 4 Then
                Exit For
            Else
                dblCoordinates(k) = Sheet1.Cells(i, j).Value
                k = k + 1
            End If
        Next j
    Next i
    
    'Check if the active space is paper space and change it to model space.
    If acadDoc.ActiveSpace = 0 Then '0 = acPaperSpace in early binding
        acadDoc.ActiveSpace = 1 '1 = acModelSpace in early binding
    End If
    
    'Draw the 3D polyline at model space.
    Set acad3DPol = acadDoc.ModelSpace.Add3DPoly(dblCoordinates)
    
    'Leave the 3D polyline open (the last point is not connected to the first one).
    'Set the next line to True if you need to close the polyline.
    acad3DPol.Closed = False
    acad3DPol.Update
acadApp.ZoomExtents
    
    'Release the objects.
    Set objCircle(0) = Nothing
    Set objSolidPol = Nothing
    Set acad3DPol = Nothing
    Set acadDoc = Nothing
    Set acadApp = Nothing
    
    'Inform the user that the 3D polyline was created.
    'MsgBox "The 3D polyline was successfully created in AutoCAD!", vbInformation, "Finished"


End Sub

 

[6StringJazzer]

This will be very difficult to diagnose without being able to run it with your data.

even though the cell().value returned a number different than 0, the a point array of double was assigned a value of 0.

Can you be more specific? What was the value of i, j when this occurred, and what was the cell's value? How were you determining what was assigned to the array element at this point?

Here are a few things I've noticed:

The For loop goes from r1 to r2, but r2 is used as the number of points to dimension the array. The number of points is actually r2 - r1 + 1. This should not cause the problem you described but the array will be dimensioned larger than necessary, and you may be looking at the wrong elements to come to your conclusion.

The inner For loop has this:

        For j = 1 To 3
            If j = 4 Then
                Exit For
            Else
                dblCoordinates(k) = Sheet1.Cells(i, j).Value
                k = k + 1
            End If

The If is unnecessary because it is not possible for j to ever be larger than 3. This is not causing your problem but is not good coding practice. This is all you need:

        For j = 1 To 3
             dblCoordinates(k) = Sheet1.Cells(i, j).Value
             k = k + 1