Changing a string into a function

[phillip_141]

Hey Guys,
         
       I've been working for a while on a project where I want a connector to adopt some properties to the object it connects to once it has connected. I was never able to get anything along that pathway to work, so I settled for using a double click event to trigger my macro instead. It's a little less elegant, but still effective. I've gotten it to work fairly well, but the issue at the end now is that the final result I want to put into the cell is going as a string rather than a function which can be evaluated.
Here's the code:

Sub Test()
    Set myshape = ActiveWindow.Selection(1)
    If myshape.Cells("Prop.SpecifierSystemName").Formula = """""" Then 'checks if the cells is blank
        ShapeName = myshape.CellsU("BeginX").Formula 'ultimately returns the name of the connected shape
        ShapeName = Left(ShapeName, 25)
        ShapeName = Right(ShapeName, 17)
        If Right(ShapeName, 1) = "C" Then
            ShapeName = Left(ShapeName, 16)
        End If
        Joe = "SETATREF(" & ShapeName & "Prop.SpecifierSystemName)" 'creates a concatenated string for the function
       
        myshape.Cells("Prop.SpecifierSystemName").Formula = """" & Joe & """" 'inputs data into cell as a string
    End If
End Sub

I realize that the last useful line is set up to put the value of "Joe" in the cell as a string, but I could not get the macro to run unless I used the four quotation marks on either side. Now I either need some way to put the value of "Joe" into the cell as function to begin with, or replace the string with the function after it has been put into the cell.

Any help you guys could provide would be invaluable.

Thanks,
Phillip

[Surrogate]

1. try use chr(34) instead """"
2. IMHO better use that condition   

Code Select Expand

If Len(myshape.Cells("Prop.SpecifierSystemName").Formula) = 2 Then 'checks if the cells contain only two commas
3. then you use just variable Joe without commas or chr(34), you create circular reference

Code Select Expand

myshape.Cells("Prop.SpecifierSystemName").Formula = Joe

[Yacine]

Hi Surrogate,


The Len... = 2 Formula is risky, as the field may already contain a string.


What about trying to put first the formula without quotes, catch the error by a "resume next", then compare the actual result with the expected one and if they differ, then and only then write the formula as string (eg with chr(34) ).

[phillip_141]

So I tried to use chr(34) just for kicks, but I kept getting an error that said "Expected: end of statement." Also, I don't really see it being particularly useful. Len, also might be cleaner or look better, but all i'm really trying to do is see if the cell is empty, so I don't see the point in using Len. (side not here, shouldn't the Len(cell)=0 if the cell is blank, i.e. =""?), also I need to use the quotes when creating "Joe" in order to concatenate the statement. I tried to do this without quotes just for fun, and it kept shooting off compile errors.

[wapperdude]

Any chance you can provide simple, Visio file so we can see exactly what you're doing?  VSD format preferred.

Wapperdude

[phillip_141]

Here's a simplified version of what I'm attempting to do.

[Surrogate]

Code Select Expand

Sub Test()
    Set myshape = ActiveWindow.Selection(1)
    If myshape.Cells("Prop.SpecifierSystemName").Formula = """""" Then
        ShapeName = myshape.CellsU("BeginX").Formula
        ShapeName = Left(ShapeName, 19)
        ShapeName = Right(ShapeName, 11)
       
        st = "SETATREF(" & ShapeName & "Prop.SpecifierSystemName)"
        MsgBox "The formula I want to go into the cell is:" & vbNewLine & st
        myshape.Cells("Prop.SpecifierSystemName").Formula = st
    End If
    If myshape.Cells("Prop.ComplierSystemName").Formula = """""" Then
        ShapeName = myshape.CellsU("EndX").Formula
        ShapeName = Left(ShapeName, 19)
        ShapeName = Right(ShapeName, 11)
         fi = "SETATREF(" & ShapeName & "Prop.ComplierSystemName)"
        MsgBox "The formula I want to go into the cell is:" & vbNewLine & fi
        myshape.Cells("Prop.ComplierSystemName").Formula = fi
    End If
End Sub

[phillip_141]

I got it to work by using a shapesheet work around. Suppose that Cell1 is where I wanted the SETATREF formula. Instead, I put the output of the code into Cell2 (rememer it is a string at this point.) Then I used SETF(GETREF(Cell1),""&Cell2&"") in a third cell. This takes the string value of Cell2 and creates the actual SETATREF function in Cell1! Thanks for all your help guys!