Referencing Geometry1's nth row

[lythamlatic]

I'm sure this have been answered before, but I can't find it - perhaps I'm searching with the wrong terminology.

I want to reference the nth row x-coordinate in Geometry1.

If I wanted to reference row 7's x-coord, I use Geometry1.X7 - so how can I generalise that? If I wanted to reference the x-coord in the row whose number is returned by a formula in User.Formula1 - how would I do that?

[Yacine]

Whenever you miss a function start the macro recorder and modify the desired element. In your case, best would be to open the shapesheet and do the modification there.

[wapperdude]

...and then there's this link, which gives you the syntax for referencing cells, shapes, pages, etc:
https://msdn.microsoft.com/en-us/library/aa200961(office.10).aspx

Wapperdude

[lythamlatic]

Whenever you miss a function start the macro recorder and modify the desired element. In your case, best would be to open the shapesheet and do the modification there.

Sorry - perhaps I'm not being clear. I'm in the Shapesheet and trying to reference a cell in another shapesheet - so I could put Sheet.63!Geometry1.X7 to reference the 7th row in the geometry of Sheet63 - but I want to be able to generalise this so I could reference the nth row. I've got a formula in another cell which calculates the row I want to look at, so I want to use the result of that cell to query the appropriate line in the geometry of Sheet63.

I don't want to use VBA for this for various reasons.

[lythamlatic]

...and then there's this link, which gives you the syntax for referencing cells, shapes, pages, etc:
https://msdn.microsoft.com/en-us/library/aa200961(office.10).aspx

Wapperdude

Thanks for this, but the page doesn't seem to have what I'm looking for – the examples referred to on the page seem to be missing (I've tried looking using two different browsers)

[Yacine]

interesting ...
let's say you need: user.calculated = 2 x sheet.63!geometry1.x7
let the row be chosen by the user: prop.RowToChoose = 7
use an intermediate field to write the reference: user.action = setf(getref(user.RefCell), "=sheet.63!geometry1.x" & prop.RowToChoose
this will write the reference in a second intermediate field user.RefCell = sheet.63!geometry1.x7
and you would modify the initial formula by: user.calculated = 2 x user.RefCell

[lythamlatic]

That sounds just what I'm looking for - I'll give that a go.

Thanks 

[wapperdude]

@lythamlatic: 

the examples referred to on the page seem to be missing (I've tried looking using two different browsers)

the link works on both PC (IE) and Android. You have to scroll down the page to get to the info.  The heading links at the top of the page just jump to different locations on the page.  Those don't seem to work on Android...not at my PC.

The page is merely syntax reference.  Yacine has provided a method.

Wapperdude

[JuneTheSecond]

Please try next.

Code Select Expand


Sub test()
    Dim shp As Visio.Shape
    Dim MyFormula As String
    Dim MyRow As Long
    Set shp = ActivePage.Shapes(1)
    MyRow = 6
    MyFormula = shp.CellsSRC(visSectionFirstComponent, MyRow, 0).FormulaU
    Debug.Print MyFormula
End Sub