VBA code does not load a string into a Shape's custom-property

[johnnysocco]

Hi there

I am trying to set a shape's custom-properties by code. I can set
numerical fields OK but not text fields - I get a 'type mismatch' when
trying to set a custom-property field which has Type=0 (text) and
format="" (no format).

the following code snippet shows my exact code (which was modified
from tht given in "http://www.design-drawing.com/visio/custprop2.htm"


Dim selObj As Visio.Selection 'Shapes selection collection
Dim shpObj As Visio.Shape 'A shape instance

Set selObj = Visio.ActiveWindow.Selection
For i = 1 To selObj.Count

Set shpObj = selObj(i)

shpObj.Cells("Prop.Width") = 4.5 'works OK
shpObj.Cells("Prop.Height") = 1.5 'works OK
shpObj.Cells("Prop.Text") = "Inserted by Code" 'gives error: Type
Mismatch

next i

What am i doing wrongly? I am putting a string into a string field
but getting a type mismatch.

thanx in advance
Socco

 

[John Goldsmith \(Visio MVP\)]

Hello Johnny,

Visio ShapeSheet cells can be set using either their respective Result
property or Formula property. I would guess, although I wasn't aware of
this, that one is the default property and so it lets you omit it from the
code as you have below.

Generally (and I don't mean always) you would use the Formula property to
set cells and the Result property to retrieve the calculated value.

Now, in terms of your problem below, you need to set a string in the formula
and so need to include a set of quotes to surround it. A double set of
quotes is evaluated as a single quote if that makes any sense, so you have
to wrap up what you want in another set as follows:

shpObj.Cells("Prop.Text").Formula = "=""" & "Inserted by Code"""

This can get a bit confusing sometimes and you're not alone in struggling
with this. An alternative can be to use the Chr(34) function, which returns
a double quote, and use this instead. For example:

shpObj.Cells("Prop.Text").Formula = "=" & Chr(34) & "Inserted by Code" &
Chr(34)

Have a look over here for a bit more background, but hopefully this will
solve your current problem.

Best regards

John


John Goldsmith (Visio MVP)
www.visualSignals.typepad.co.uk
www.visualSignals.co.uk

 

[John Goldsmith \(Visio MVP\)]

.....and here's the link!

http://msdn.microsoft.com/en-us/library/aa201764(office.10).aspx

John

 

[johnnysocco]

Thank You John G

The problem was that i needed both a) .Formula to set the contents of
the text cell (but strangely not for numeric cells) and b) the equals
char as part of the string along with the logical but unintuitive set
of quotes.

I had tried 3 of the 4 permutations of a and b but not both together!
As a VB6 programmer but brand new to visio object model, I was
expecting .value to be the default property - and not .formula.

Thanx also for the link into msdn.

Maybe I should install the visio sdk now (slight loss of face here...)

thanx again - now i can strive forward

Socco