I want to use variables in cells (). formula-CodePudding

Shape.Cells ("something"). Formula = XXX I want to enter a variable on VBA with

In Cells.Formula which is different from the file that starts VBA I want to enter the value of a variable defined in VBA (here, the string "1.0.0")

Code only for the relevant part

Sub Input_Version_Information(ByVal VSSXpath As String)
    version_str =  "1.0.0"        
    For Each vssxMaster In vssxMasters
        vssxMaster.Shapes.Item(1).Cells("Prop.Version").Formula = version_str                
    Next
End Sub

All code

Sub Input_Version_Information(ByVal VSSXpath As String)
    Dim vsoApp As Visio.Application    
    Dim vssxDoc As Visio.Document
    Dim vssxMasters As Visio.Masters
    Dim vssxMaster As Visio.Master
    Dim FileName As String
    Dim version_str As String        
    Set vsoApp = CreateObject("Visio.Application")
    Call vsoApp.Documents.OpenEx(VSSXpath, visOpenRW)
    Set vssxDoc = vsoApp.Documents.Item(1)
    Set vssxMasters = vssxDoc.Masters    
    FileName = Dir(VSSXpath)
    FileName = Replace(FileName, ".vssx", "")    
    version_str = Mid(FileName, InStr(FileName, "v")   1, Len(FileName))
        
    For Each vssxMaster In vssxMasters
        vssxMaster.Shapes.Item(1).Cells("Prop.Version").Formula = version_str                
    Next
    
    vssxDoc.Save    
    vssxDoc.Close
    vsoApp.Quit

    End Sub

Sub test_update()
    test_vssx_file = ”Flowchart_v1.0.0.vssx"
    Call Input_Version_Information(test_vssx_file)

End Sub

CodePudding user response：

If you need use cell value as string you must use syntax

vssxMaster.Shapes.Item(1).Cells("Prop.Version").Formula = chr(34) & version_str & chr(34)

Or triple quotes

vssxMaster.Shapes.Item(1).Cells("Prop.Version").Formula = """7.40"""

PS Topic with same question, sorry i cant find today hyperlinks from M$FT official resources…