What happened when I asked Copilot X chat to write some Visio VBA

[Visisthebest]

Even though Visual Studio is not for VBA, I asked Copilot X Chat in Visual Studio to write some VBA:

"Please write a Visio VBA routine to copy all shape data rows by rowname from one Visio shape to another Visio shape."

Code Select Expand

Public Sub CopyShapeDataRowsByName(fromShape As Visio.Shape, toShape As Visio.Shape)
    Dim rowCount As Integer
    Dim rowName As String
    rowCount = fromShape.RowCount(visSectionProp)

    For i = 0 To rowCount - 1
        rowName = fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).FormulaU
        toShape.CellsSRC(visSectionProp, i, visCustPropsValue).FormulaU = fromShape.CellsSRC(visSectionProp, i, visCustPropsValue).FormulaU
    Next i
End Sub


"Improve the routine to copy all cells on each row to the other shape."

Code Select Expand

Public Sub CopyShapeDataRowsByName(fromShape As Visio.Shape, toShape As Visio.Shape)
    Dim rowCount As Integer
    Dim rowName As String
    Dim colCount As Integer
    Dim cellValue As Variant
    rowCount = fromShape.RowCount(visSectionProp)

    For i = 0 To rowCount - 1
        rowName = fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).FormulaU
        colCount = fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).RowNCellCount
        toShape.AddRow visSectionProp, i, visTagDefault

        For j = 0 To colCount - 1
            cellValue = fromShape.CellsSRC(visSectionProp, i, j + visCustPropsValue).FormulaU
            toShape.CellsSRC(visSectionProp, i, j + visCustPropsValue).FormulaU = cellValue
        Next j
    Next i
End Sub


Do you have a prompt for some VBA code you want me to try? Post it here and I'll post the code Copilot X Chat suggests here!

[Visisthebest]

"improve the routine to make sure it checks at the beginning if a rowname in the fromShape already exists in the toShape, and add a row with this rowname if it does not exist in the toShape."

(it went back to C# even though the context was VBA code)

Code Select Expand

public void CopyShapeDataRowsByName(Visio.Shape fromShape, Visio.Shape toShape)
{
    int rowCount = fromShape.RowCount[Visio.VisRowTags.visTagCustProps];
    int colCount = fromShape.CellsSRC[Visio.VisSectionIndices.visSectionProp, 0, Visio.VisCellIndices.visCustPropsLabel].RowNCellCount;
    Visio.Cell rowNameCell;
    string rowName;

    for (int i = 0; i < rowCount; i++)
    {
        rowNameCell = toShape.CellsSRC[Visio.VisSectionIndices.visSectionProp, i, Visio.VisCellIndices.visCustPropsLabel];
        rowName = fromShape.CellsSRC[Visio.VisSectionIndices.visSectionProp, i, Visio.VisCellIndices.visCustPropsLabel].FormulaU;

        if (rowNameCell.ResultStr[Visio.VisUnitCodes.visNoCast] != rowName)
        {
            toShape.AddRow(Visio.VisSectionIndices.visSectionProp, i, Visio.VisRowTags.visTagCustProps);
            rowNameCell = toShape.CellsSRC[Visio.VisSectionIndices.visSectionProp, i, Visio.VisCellIndices.visCustPropsLabel];
            rowNameCell.FormulaU = rowName;
        }

        for (int j = 0; j < colCount; j++)
        {
            Visio.Cell fromCell = fromShape.CellsSRC[Visio.VisSectionIndices.visSectionProp, i, j + Visio.VisCellIndices.visCustPropsValue];
            Visio.Cell toCell = toShape.CellsSRC[Visio.VisSectionIndices.visSectionProp, i, j + Visio.VisCellIndices.visCustPropsValue];
            toCell.FormulaU = fromCell.FormulaU;
        }
    }
}


[Visisthebest]

"In the routine, do not use the i variable in the copy rows loop, but first build a Collection of all the rownames in the fromShape and then loop through this collection instead."

"please write it in VBA"

Code Select Expand

Public Sub CopyShapeDataRowsByName(fromShape As Visio.Shape, toShape As Visio.Shape)
    Dim rownames As New Collection
    Dim rowCount As Integer, colCount As Integer
    Dim rowName As String
    Dim i As Integer, j As Integer, rowIdx As Integer
   
    rowCount = fromShape.RowCount(visSectionProp)
    colCount = fromShape.CellExistsU("Width", visExistsAnywhere)

    For i = 0 To rowCount - 1
        rowNames.Add fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).FormulaU, fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).FormulaU
    Next i
   
    rowIdx = 0
    For Each rowName In rownames
        Dim existingCell As Visio.Cell
       
        'Verify if the row already exists, create it if not
        If Not toShape.CellExistsU(rowName, visExistsAnywhere) Then
            toShape.AddRow visSectionProp, rowIdx, visTagDefault
            toShape.CellsSRC(visSectionProp, rowIdx, visCustPropsLabel).FormulaU = rowName
            rowIdx = rowIdx + 1
        Else
            Set existingCell = toShape.CellsSRC(visSectionProp, rowIdx, visCustPropsLabel)
            'If row exists, move to the next row
            If existingCell.Result(visExists) Then
                rowIdx = rowIdx + 1
            End If
        End If
       
        For j = 0 To colCount - 1
            Dim fromCell As Visio.Cell, toCell As Visio.Cell
            Set fromCell = fromShape.CellsSRC(visSectionProp, rownames.IndexOf(rowName), j + visCustPropsValue)
            Set toCell = toShape.CellsSRC(visSectionProp, rowIdx - 1, j + visCustPropsValue)
            toCell.FormulaU = fromCell.Result(visString)
        Next j
    Next rowName
End Sub

[Visisthebest]

"Please explain the last CopyShapeDataRowsByName sub code you wrote."

The CopyShapeDataRowsByName sub code loops through a Visio Shape object's rows ('Visio.Row' objects) and copies the custom properties (Visio.Cell objects) of one shape to another shape. In the original code, this loop used an integer variable i to iterate through each row and then use it as the index to retrieve data from each cell. However, the developer requested a change to first build a collection of all the row names in the 'fromShape' and then loop through this collection instead of the original row iteration.
To implement this change, a List<string> collection called 'rownames' was created to store row names retrieved from the cells in the first loop. The original loop was then replaced with a foreach loop that iterated through each row name in the 'rownames' collection. Within this loop, cells for the current row could be accessed using the rownames.IndexOf(rowname) method to retrieve the correct index location within the Visio.Shape object.
The resulting code is then able to copy each row by row name rather than by index. When copying each row by row name, the CellsSRC method is used to retrieve each cell's value and the AddRow method is used to add each row that is not already in the receiving shape.
Here is the code re-written in VBA:

Code Select Expand

Public Sub CopyShapeDataRowsByName(fromShape As Visio.Shape, toShape As Visio.Shape)
    Dim rownames As New Collection
    Dim rowCount As Integer, colCount As Integer
    Dim rowName As String, rowIdx As Integer
   
    rowCount = fromShape.RowCount(visSectionProp)
    colCount = fromShape.CellExistsU("Width", visExistsAnywhere)

    For i = 0 To rowCount - 1
        rowNames.Add fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).FormulaU, fromShape.CellsSRC(visSectionProp, i, visCustPropsLabel).FormulaU
    Next i
   
    rowIdx = 0
    For Each rowName In rownames
        Dim existingCell As Visio.Cell
       
        'Verify if the row already exists, create it if not
        If Not toShape.CellExistsU(rowName, visExistsAnywhere) Then
            toShape.AddRow visSectionProp, rowIdx, visTagDefault
            toShape.CellsSRC(visSectionProp, rowIdx, visCustPropsLabel).FormulaU = rowName
            rowIdx = rowIdx + 1
        Else
            Set existingCell = toShape.CellsSRC(visSectionProp, rowIdx, visCustPropsLabel)
            'If row exists, move to the next row
            If existingCell.Result(visExists) Then
                rowIdx = rowIdx + 1
            End If
        End If
       
        For j = 0 To colCount - 1
            Dim fromCell As Visio.Cell, toCell As Visio.Cell
            Set fromCell = fromShape.CellsSRC(visSectionProp, rownames.IndexOf(rowName), j + visCustPropsValue)
            Set toCell = toShape.CellsSRC(visSectionProp, rowIdx - 1, j + visCustPropsValue)
            toCell.FormulaU = fromCell.Result(visString)
        Next j
    Next rowName
End Sub


[wapperdude]

Hallelujah!  Totally awesome.  I can retire from the forum.  Even better, have time to pursue my favorite hobby...laziness.

[Visisthebest]

I think Copilot/ChatGPT is still far, far removed from your level of competency and understanding.

My use case for it is to write a bunch of boilerplate code, but the time needed to check if the code is correct and makes sense for the use case is more than just writing the boilerplate code myself.

Copilot X Chat reads the code you have open in Visual Studio (Code), so it should understand the context and write better code, but for Visio add-ins this does not work very well.

(By the way, for writing TypeScript/Javascript/React boilerplate code Copilot X Chat is amazing and a huge timesaver. I love it for this use case and I really, really dislike React so the less work I have to do with React myself the better.)

This forum and your help is irreplacable!