Skip to main content
anthonyQueen11-Garnet
11-Garnet
November 24, 2012
Solved

give a default value when a variable is not defined or "on error"

  • November 24, 2012
  • 8 replies
  • 4867 views

Probably my is a trivial question. I started using reference files to take advantage of repetitive calculations. So the scenario could be like the following.

  • Main Worksheet [1]: defines var1; var2, then it references file .mcd Worksheet [2].
  • Linked Worksheet [2]: expects var3 and gives outputVar1.

I would like to put in [2] a line of programming that if no var3 is defined, then var3:=0. Yet I tried "on error" function, but I realized that it does not work in this case

.

Is there a way to assign a value to a variable when it is "on error" AND when it is "not defined" above (in my case in the main worksheet)?

Thanks for any tip.

Best answer by RichardJ

It's not a trivial question. If an expression contains an undefined variable then the expression is not evaluated. Therefore there is no Mathcad expression that can trap an undefined variable and assign a default value. There is a way to do this, but it requires a kludge.

8 replies

RichardJ19-TanzaniteAnswer
19-Tanzanite
November 24, 2012

It's not a trivial question. If an expression contains an undefined variable then the expression is not evaluated. Therefore there is no Mathcad expression that can trap an undefined variable and assign a default value. There is a way to do this, but it requires a kludge.

    anthonyQueen11-GarnetAuthor
    11-Garnet
    November 24, 2012

    Thanks Richard,

    amazing job. I did not know it was "no a trivial" question at all. As I admire your skills, I fear that for the time being I will not rely on Visual Basic.

    The trick that I meanwhile envisage is to create at the very beginning a linked worksheet initializing all possible variables with a default value, so to leave below the possibility of changing or not declaring them any more. In the above scenario:

    • Main Worksheet [1]: ...wait before defining
        • Linked Worksheet [0]: declare all possible variables with a default value.
      • proceed in [1] to define (again) var1, var2, etc. as you like.
        • Linked Worksheet [2]: expects var3 and gives outputVar1. (Now it is ok because of Worksheet [0]).
      • retake outputVar1 in the main worksheet.

    Worksheet [0] becomes so to speak a Contents of variables.

    Update.....Oops but it did not work. I realize that I am not able to pass on the definitions from worksheets that are referenced. That's a pity. I hope to be wrong! And I was wrong: there is no update with reference worksheets, so changes are evaluated (and accepted) only after a restart.

    anthonyQueen11-GarnetAuthor
    11-Garnet
    November 25, 2012

    Richard,

    I saw that you gave an answer at a post in 2007,

    http://communities.ptc.com/message/85673#85673

    that it was asking more or less a similar question.

    You gave the following link for further reference:

    http://collab.mathsoft.com/read?80805,63e#80805

    which is now not reachable. Was it a shorter version of the VBasic that you posted here?

    I tried to reduce your code to my needs, but I am a very novice in VB and I would like a bare and bones example.

    The workflow I gave above has a drawback: the linked file [2] cannot be used as a standalone worksheet because of the undefined variables. So I am back to the original question.

    A solution without Visual Basic coding

    At first a workaround I thought was to make a manual "switch" to hide or show in the linked worksheet [2] the assignment of variables (according if they are defined already or not in the container worksheet [1]). But I did not find an elegant way to accomplish it.

    Then I come up with the following solution. It relies on the build-in "inN" assignment, with N--> 0..9.

    So if one assigns in file [2]

    var03:=in0,

    it does not give an error. But it leaves Var03=0 if "inN" is not assigned above. From it one can build a conditional line, to pick up the value in worksheet [2], if variables in0, in1, etc. are not assigned in the worksheet n.[1].

    Richard, it would work also with your example, if dataC was assigned the name DataC:=in0. I hope the trick is helpful to someone.

    Terms & ConditionsAccessibility statement