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

[anthonyQueen]

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.

[RichardJ · Accepted solution]

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.

[anthonyQueen]

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.

[anthonyQueen]

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.

[RichardJ]

I'm not sure I follow what you are saying. What are you going to test for in worksheet[2]? All you can do is check if var03=0. You could get that just by assigning var03:=0 in worksheet[1].

[RichardJ]

To update a referenced worksheet sleect it and hit F9 (Ctrl F9 will not work).

[anthonyQueen]

Thanks Richard for the tip.

Sorry for not being clear enough. I tested in v.11.

Let say that WorkSheet [2] (WS2 from now on) solves a set of equations on Velocity (or whatever), starting from some data.

Let say that WS1 is the main container that solves Space, Mass, Acceleration, Force and Velocity (exploiting in the latter case WS2).

Now to solve Velocity in WS2 I need Space as data. But WS2 accomplishes two tasks:

1. It is used as a reference in WS1, and in that case should take Space from there.
2. It should be used also as indipendent WS, if I want just to see some calculation on velocity and nothing else.

So, assuming that Space is called var03, in WS2 I put

var03:=in0

var03_default:= 10

Space:= var03_default if var03=0

var03 otherwise

In this way WS2 works also when it is opened indipendently (before it gave me error, because I could not trap the undefined var03). Instead if it is used as a linked reference in WS1 it will take the value of Space of WS1, where I will have:

Space:=20

...........

in0:=Space

>>Link reference with WS2

Moreover, if I forget to declare in0 in WS1, the file WS2 will not give me an error, even when it is referenced in WS1. It will give the Velocity with the Space declared in WS2 (which could be the default value).

It is a way to use Mathcad WS in modular form as well as independently.

[RichardJ]

OK. I see now. A nice trick, that will work as long as var03 can never actually take on the value of 0.

[anthonyQueen]

Yes, to some extend. There are in fact (given the constraints of mathcad on this point) some workarounds.

• If zero is the default value, no problem. It works as it is.
• If zero is not the default value, one workaround is to add an infinitesimal noise to zero. Other more elegant way, is to define in WS1 Space:="0", and then add another line in defining Space in WS2: 0 if var3="0". I thought it would work also by defining (in WS1) the variable Space with an error (like 0/0, just to discover that in mathcad it is not an error), and then trap the "value" zero in WS2 with: 0 on error var03. But I did find less easier.