Author Topic: Modify by code a cell that has a BOUND formula  (Read 331 times)

0 Members and 1 Guest are viewing this topic.

Yacine

  • Hero Member
  • *****
  • Posts: 2952
Modify by code a cell that has a BOUND formula
« on: May 05, 2022, 02:50:01 AM »
Say you have a cell whose value is constrained by a BOUND formula and you want to overwrite its value - NOT the formula - by code.


Instead of shp.Cells(your_cell_name).Formula = ... ,
write shp.Cells(your_cell_name).ResultIU = ...


Interesting enough, if you write a value outside the allowed range, the value gets written into the value parameter of the BOUND formula, but the value of the cell itself is still correctly within the boundaries.
So if you want to know the limits of the cell, you could instead of parsing the formula, set its values to very high and very low values and read the according values.


PS: I haven't yet tried other assignments thant ResultIU.
« Last Edit: May 06, 2022, 05:20:24 AM by Yacine »
Yacine

Yacine

  • Hero Member
  • *****
  • Posts: 2952
Re: Modify by code a cell that has a BOUND formula
« Reply #1 on: May 06, 2022, 02:30:44 AM »
Yeah! I need to share this:
If you write a bound formula in a prop cell it won't get overwritten when you type in values and even better, the values are restrained as it should.
Great feature, I would have liked to know before.
« Last Edit: May 07, 2022, 12:23:13 AM by Yacine »
Yacine

Visisthebest

  • Hero Member
  • *****
  • Posts: 637
Re: Modify by code a cell that has a BOUND formula
« Reply #2 on: May 06, 2022, 05:48:38 AM »
Thank you for sharing this insight Yacine!
Visio 2021 Professional

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4373
  • Ideas Visio-lized into solutions
Re: Modify by code a cell that has a BOUND formula
« Reply #3 on: May 06, 2022, 06:48:50 PM »
I can confirm the ResultIU() method.
I also get the same behavior with Formula.  Also, using SETATREF in Prop Data produces same behavior. 

My target cell in all cases was Width.
Obviously, FormulaForce clobbers the formula in the width cell and replaces it with the specified value.

Visio 2019 Pro

Yacine

  • Hero Member
  • *****
  • Posts: 2952
Re: Modify by code a cell that has a BOUND formula
« Reply #4 on: May 07, 2022, 12:41:17 AM »
SETATREF is certainly the better way to do it.
I just happen to struggle with all the SETATREF, SETATREFEXPR, ... functions.
Need to take the time to investigate on them, they are so useful.
Yacine

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4373
  • Ideas Visio-lized into solutions
Re: Modify by code a cell that has a BOUND formula
« Reply #5 on: May 07, 2022, 08:49:23 AM »
SETATREF is definitely challenging.  It takes hands-on to really get to feeling comfortable with that group of functions.  Don't know if you've looked at my post, "What's with SETATREF?" in the User Submitted Stuff, http://visguy.com/vgforum/index.php?topic=6383.msg26308#msg26308.  It might be helpful.  I had intended it to be kinda like a tutorial, so if of interest, start at the 1st post and progressively work thru it?  Hopefully it will shed some light on the topic.





Visio 2019 Pro