recently i needed to write a macro that processes an MS  Visio 2007 page and and does some actions based on the contents of a custom defined stencil shapes, where this contents are saved as a Shape Data field in the shape

the problem was i wanted to access the Shape Data of each shape

there is a data type Shape, which has a data field names “Name” which specifies it’s Class

i mean if u r using the following shape set

and dropped an object from the Triangle Class, that object will have the data field “Name” holding the string “Triangle.xx” where the xx is a unique number for that object in the current document

so to make sure that it’s of the class “Triangle” u can use the function “Mid” in VBA that is a substring function, like

IF mid(shpObj.Name,1,8) = "Triangle" Then
'say i am a drunk square
End IF

back to the main topic

i needed to access the Shape Data for the dropped objects, trying to use  the intellesnce that existed in the VBA studio that is already in the office

i found nothing called “ShapeData” or anything named data that can help

so of course the second step would be google,

after a little search i found that most if the shape data are saved in a 3D table, this table is composed of Cells that hold somthing

according to all the results i’ve found, i could access the value of the Shape Data i need using the property shpObj.Cells()

which is the table cells indexed by the cell’s name

so assuming that we are using the shape in the first figure, and want to access the Shape Data “Cost”

i could write

shpObj.Cells("Prop.Cost").Format

where “Format” holds the value held by this cell

but running this code produced an exception which is equal to “Object reference not set to an instance of an object” .Net Exception

which meant that this cell has not been found returning a NULL which have no “Format” to be accessed

i was sure of the name, and tried some other combinations on “Prop” and “Cost”, and always the same result

so, back to google, i found nearly the same ways to access the data

so, the next step, MSDN… it was a typical waste of time with no results, u can search for the information in the MSDN in google better that using the MSDN search itself, just add “msdn” before ur query

so, back again to google😀

i found another field in the shape object called CellsSRC(), that is the 3D data table holding the data

it takes three indexes for a particular cell (Section index,Row index,Column index)

so i moved to the debug mode, added a watch “shpObj.CellsSRC(1,1,1)”, and noticed the values, changed the section index till the data was in the same context i needed, then change the column index and the row index, (u can use the constant VisSectionProp to advance directly to the Shape Data section e.g. shpObj.CellsSRC(VisSectionProp,1,1) )

and at last i’ve found the data item i need, and the cell had a weird name…

it is supposed to be “Prop.Cost”  -if we are to follow the first figure example- but i found it “Prop.xxx_Cost” where the xxx were some other letters i don’t remember -as i was pretty mad at this moment :D-

so i “copy-paste”ed this Name and wrote

shpObj.Cells("Prop.xxx_Cost").Format

in the watch and found the result i wanted, but take care the value in the “Format” field is/must be surrounded by extra double quotes

as if the value u want to add is “x”, you should add it as ” “x” ” (spaces between double quotes are for demonstration)

and at last…. it worked😀

i had some other problems but that was the major one

one last thing…

i could have used the .NET development kit for office, but i just wanted to try VBA macros, now i doubt that i took the right decision😀