Author Topic: Visio Cell references does not work (error in formula)  (Read 9889 times)

0 Members and 1 Guest are viewing this topic.

TomasL

  • Jr. Member
  • **
  • Posts: 21
    • ENRAD AB
Visio Cell references does not work (error in formula)
« on: January 10, 2011, 04:34:44 AM »
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

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Visio Cell references does not work (error in formula)
« Reply #1 on: January 10, 2011, 06:59:36 AM »
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

  • Jr. Member
  • **
  • Posts: 21
    • ENRAD AB
Re: Visio Cell references does not work (error in formula)
« Reply #2 on: January 10, 2011, 08:30:47 AM »
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

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Visio Cell references does not work (error in formula)
« Reply #3 on: January 10, 2011, 09:52:49 AM »
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

  • Jr. Member
  • **
  • Posts: 21
    • ENRAD AB
Re: Visio Cell references does not work (error in formula)
« Reply #4 on: January 10, 2011, 10:25:20 AM »
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.

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Visio Cell references does not work (error in formula)
« Reply #5 on: January 10, 2011, 04:06:18 PM »
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

  • Jr. Member
  • **
  • Posts: 21
    • ENRAD AB
Re: Visio Cell references does not work (error in formula)
« Reply #6 on: January 10, 2011, 04:40:41 PM »
Yeah, did a search, didnt get any usefull, guess I was a little bit frustrated, spent some 10 hours googling for the problem.

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Visio Cell references does not work (error in formula)
« Reply #7 on: January 10, 2011, 05:34:01 PM »
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

  • Jr. Member
  • **
  • Posts: 21
    • ENRAD AB
Re: Visio Cell references does not work (error in formula)
« Reply #8 on: January 10, 2011, 05:44:06 PM »
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.