cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Defining Variable

Highlighted
Newbie

Defining Variable

This sheet contains a scripted component that will generate a definition for a set of variables, with units. While originally motivated by the need to import data from self describing files (files that include variable names and unit descriptions as well as numeric values), it has a number of other uses.

It can be used to get around one of the SUC restrictions. While a function (or other expression) cannot return values with different units depending on the data, it can generate a set of dimensionless values and a set of unit descriptions (as strings) and then those values and units can be assigned to variables.

It can be used to break the normal order of definitions. The variables defined by this compononent can be anywhere on the sheet. This allows a variable to be defined with a value that is calculated later in the sheet. Thus one can put a summary of the results of the calculations at the beginning of the worksheet, prior to the supporting calculations.

While the script as posted puts the definition in the current worksheet, it can just as easily put it in another sheet, provided that sheet is open and can be found (i.e., the worksheet object is accessible). This provides an alternative to setvalue method. Using this method allows values to be set with units attached. And since it modifies the actual sheet, if the sheet is saved and then later reopened, even manually, it will open with the values assigned.

And it serves the original purpose, reading variable names and values from a file and dynamically creating variables. The attached AISC Steel Shapes file is the latest version. It shows how to use dynamic list boxes to allow the user to select a particular file, and then specific data from that file. A modified version of the define variables component is used to construct a dynamic definition of the variables found in the Excel file.
__________________
� � � � Tom Gutman
3 REPLIES 3
Highlighted

Defining Variable

I've been a bit busy recently, and only just got a chance to look at this (although not in as much detail as it deserves). Nice job! It looks like it was a lot of work.

Richard
Highlighted

Defining Variable

Thank you Tom,

I will spend some time to read and understand it

Frank
Highlighted

Defining Variable

Thank you Tom,

I will spend some time to read and understand it

Frank
Announcements