How to input a string variable intro FormulaU?

Question

I have some shapes in a Visio drawing which contain lists as Shape Data. All the data is defined in Data Set, which I apply to all my stencils. From time to time, I will need to add more stencil and update the Data Set (insert new items in the lists). Because every time I update the lists, Visio is deleting the current list and creating a new one, I lose data from all the shapes that use that Data Set.

Trying to solve this, I wrote some VBA code that will create a temporary storage and save the list item corresponding to each shape, while I update my Data Set.

Below is what I wrote.

Sub AddTemp()
    Dim vPage As Visio.Page
    Dim vShape As Shape
    Dim vRowInt As Integer
    Dim vCell As Cell
    Dim MyList As Variant
    Dim vValue As String
    Dim vLabel As String

    'Shape Data defined as Fixed/Variable List:
    MyList = Array("List1", "List2")

    'Loop through each page of the document
    For Each vPage In ThisDocument.Pages
    'Loop through each shape of each page of the document
        For Each vShape In vPage.Shapes
    'If ShapeData exists, do your thing
            If vShape.SectionExists(visSectionProp, 0) Then
    'Iterate through each element of the list
                For Each element In MyList
    'If Temp container does not exist, make one
                    If Not vShape.CellExistsU("Prop." + element + "Temp", 1) Then
                        vRowInt = vShape.AddRow(visSectionProp, visRowLast, visTagDefault)
                        vShape.Section(visSectionProp).Row(vRowInt).NameU = element + "Temp"
                        vLabel = "=" + element + "Temp"
                        'MsgBox vLabel
                        vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsLabel).FormulaU = "=vLabel"
                        vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsType).FormulaU = 0
                        vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsFormat).FormulaU = ""
                        If vShape.CellExistsU("Prop." + element, 1) Then
                            vValue = "=Prop." + element + ".Value"
                            'MsgBox Value
                            Set vCell = vShape.CellsU("Prop." + element + "Temp.Value")
                            vCell.FormulaU = vValue
                        End If
                    End If
                Next
            End If
        Next
    Next
    MsgBox "Temporary Storage Created"

End Sub

The problem I currently have is with the following statement:

vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsLabel).FormulaU = vLabel

I would like to set the Label column of the row I create in function of the elements in MyList, but it doesn't seem to work, no matter what I try to use:

vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsLabel).FormulaU = element

or

vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsLabel).FormulaU = `element`

or

vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsLabel).FormulaU = "element"

or

vShape.CellsSRC(visSectionProp, vRowInt, visCustPropsLabel).FormulaU = "=element"

etc.

The code below works fine, though:

vValue = "=Prop." + element + ".Value"
vCell.FormulaU = vValue

I would have expected FormulaU to accept the string elements of the MyList array, instead I get

Run-Time error '-2032466907 (86db0425)': #Name?

How can I use the elements of the array in setting the Label of each Row I add?

Paul Herber Paul Herber · Accepted Answer · 2019-05-17T11:30:14

You need to put Chr(34) around your text. 34 is the character code for a "

So you need

= Chr(34) & expression & Chr(34)

How to input a string variable intro FormulaU?

1 Answers