Visio Cell references does not work (error in formula)

Started by TomasL, January 10, 2011, 09:34:44 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

TomasL

Dear All.
I Don't know if this is the correct section, but anyway:

I am trying to put a formula in the "User" section, but Visio refuses to accept it

"=Pages[MT1_Main]!Sheet.15!PinX"

Now the page is named "MT1_Main" this is the same name I get from the Name-property in VBA

Sheet 15 does exist on this particular page.

When I do it programtically in VBA, I receive an overflow error, when I try to write or paste the formula directly into the shapesheet, I get an "Error in Formula" message.



According to http://msdn.microsoft.com/en-us/library/ms195648%28v=office.12%29.aspx this should be the correct syntax.

Using Visio 2007 currently
my sites:
www.ebaman.com www.enrad.se www.tlec.se

Jumpy

See in VBA what is the NameU property of the page. If you have renamed the page more than one time, Name and NameU may differ and in the ShapeSheet you have to use NameU.

The first renaming of a page renames NameU, too. Later renamings only rename Name, not NameU. To later rename NameU again you can use VBA.

TomasL

Ok, thanks that seem to resolve the problem working in the shapesheet, but when I do the same thing in VBA I get "Runtime error 6" - Overflow, I think the English description is.
When I try to run the line

ThisDocument.Pages(strToPage).Shapes("SystemBlock").Cells("User.QIn").Formula = strTemp

Where:
strFromPage = vsoOldPage.NameU
and
strToPage is the name of the page where the shape I try to modify is located
and finally
this is a test-statement
strTemp = "=PAGES[" & strFromPage & "]!" & strFromShape & "!PinX"

The correct line is actually " strTemp = "=PAGES[" & strFromPage & "]!" & strFromShape & "!User.QIn" "

Neither of them works
my sites:
www.ebaman.com www.enrad.se www.tlec.se

Jumpy

Don't see an obious error. Try without "=" inside the String strTemp:

strTemp = "Pages...

And/Or try to enclose the string in a string:
After filling the strTemp add the line:

strTemp = CHR(34) & strTemp & CHR(34)

hth

TomasL

Well, finally got it working.
BUT
This is a bloody mess.
In som cells you cant use the localized Name but have to use the universal name, despite what is written in the sdk-documentation, and in some cells it is the otherway around.
In some cells one cant use the universal name and must use the local name.

So, when referencing cells between pages I have to use the universal names, for both pages and shapes.

WHen setting the dubbelklick cell to jump between the pages i MUST use the local page-name.
my sites:
www.ebaman.com www.enrad.se www.tlec.se

Jumpy

Perhaps if you search the forum, there have been at least 2 threads (if I remember correctly), that had the topic of page-to-page-navigation, or something like an page index or page directory on the first page. Maybe you can learn sth. helpfull for you there? The Name, NameU and Pagenumber problem and the ShapeSheet-Functions for this, where a major topic there.

TomasL

Yeah, did a search, didnt get any usefull, guess I was a little bit frustrated, spent some 10 hours googling for the problem.
my sites:
www.ebaman.com www.enrad.se www.tlec.se

Jumpy

Wasn't meant as critics, but as tip. Found the thread I had in mind:

http://visguy.com/vgforum/index.php?topic=1673.msg7220#msg7220

Post No. 5 from Scott explains a bit the weird behavior you encountered.

Jumpy

TomasL

No offence taken, understood that.
Ok, can understand how it works, not necessary why though  ???.
However, why does not NameU work in the evntDblClick-cell, its a little bit odd, me thinks.
Anyway, a behaviour like this should be explained in the SDK documentation, where the cell-references is described, would save a lot of hassle.
my sites:
www.ebaman.com www.enrad.se www.tlec.se