topic Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)? in Mathcad

Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Tom_VanHeuklon — Thu, 14 May 2020 23:13:01 GMT

Hello,

I wonder if anybody can suggest a means to programmatically create variable names and assign values to them, using data from an Excel spreadsheet? I have attached a very simple example to illustrate what I want to do. I have electronic component identifiers (Reference Designators) in column A, and the corresponding values for those components in Column B. I would like the values in column A to become variable names in Mathcad, and the values in column B to become the values of these variables. In the example below, I have manually created the variables in Mathcad, but I would like to automate that. Seems pretty simple, but I haven't figured out how to do it. Any suggestions would be greatly appreciated! Thank you.

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 15 May 2020 03:10:24 GMT

As far as I'm aware, Tom, there is no way of programmatically creating names in a straightforward fashion.

I've asked for such a feature several times over the years, but it's not appeared yet. There have been other user requests for this feature, eg.

https://community.ptc.com/t5/PTC-Mathcad/Create-variable-names-automatically/m-p/574404

Coincidentally, I recently (Apr 2020) followed up an Alfasoft article on Unicode character creation by asking Alfasoft about this capability. They said that they had had similar requests from other customers, didn't know whether it was on PTC's development path for Mathcad, but would forward my request to PTC.

https://support.alfasoft.com/hc/en-us/articles/360003722078-How-to-add-things-on-top-of-characters-used-as-variable-names

With a createName type function, it would be straightforward to vectorize it over a list of names imported from Excel.

Stuart

From my letter to Alfasoft:

Thank you for posting the referenced article - it was interesting and solves a particular problem that several have had over the years. However (there’s always a ‘however’, isn’t there?), the process of adding a constructed character or name by copy & paste is somewhat mandraulic, not to mention “copy&paste” errors being a somewhat notorious and common phenomenon. On the face of it, the process would seem to be better suited to automation.

Could a feature request please be raised to add a function to Mathcad Prime 6 that takes a string and creates a new name (variable/function) on the left-hand side of a definition and which is then available for use 'later on' in the worksheet?

Eg, make a name ...

newName:=concat("var",num2str(1))
newName = "var1"

newName := stack(97,0x0333)
newName = "a̅0̳"

followed by creating the name,

createName(newName) := <expression>

This would have the effect of manually typing/pasting a name and be fully equivalent to var1 := <expression>.

Used on the rhs, or as a function parameter, createName would not create a name but would stand in place of the name var1.

Other options might be to prefix 'newName' with an operator (eg, @ or 🄋 (or even🗃or 🗄!) - available via the ribbon) to identify a name indirection, eg

( 3 + 🄋newName == 3 _ var1)

(Suspicions that I might be subtly asking for a macro capability are, I am sure, completely unwarranted. Indeed, the thought has never so much as passed through my head …)

From a subsequent worksheet I was playing around with to create complex names, and a candidate for a function like createName,

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

terryhendicott — Fri, 15 May 2020 11:21:31 GMT

Hi,

You can sort of do it like this:

Cheers

Terry

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

terryhendicott — Fri, 15 May 2020 11:37:03 GMT

Hi,

Forgot to show you can get myVariables direct from an Excel component.

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 15 May 2020 13:04:16 GMT

A similar solution using a lookup function variant - I've used this type of thing before when dealing with spreadsheet or other tabular data.

Cheers,

Stuart

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 15 May 2020 13:59:47 GMT

Ah, apologies ,.. that wasn't the version I intended to post. Forgot to copy across another couple of functions Mea culpa.

This version is slightly more general.

Stuart

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 15 May 2020 16:56:59 GMT

I suppose, for completeness sake, that I'd better add that if all you're interested in is the name in the leading column or row, then hlookup and vlookup are sufficient if multicolumn tables are the target (and easily specialized for a 2-column table.

Stuart

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Tom_VanHeuklon — Thu, 21 May 2020 14:01:54 GMT

Thank you Terry. Yes, this approach "sort" of does what I want. Unfortunately, when I then go to use these variables in subsequent calculations, it gets a bit "ugly". Consider a very simple example of a voltage divider using my two resistors, what I want to do would look like this:

Whereas your approach would look like this:

Although not exactly what I was looking for, your suggestion is much appreciated none-the-less.

Thanks again,

Tom

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

terryhendicott — Thu, 21 May 2020 15:12:23 GMT

Hi,

You could make it a little less ugly by renaming "MyVar" as just "v"

Appreciate your appreciation

Terry

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

LucMeekes — Thu, 21 May 2020 15:29:03 GMT

Apart from the fact that it is desirable to have a function that can define a worksheet variable, in order to drive that variable name from e.g. Excel, you must have a reference. At some point you need to decide which one of the many variable names that are defined in the Excel table will be used at which position in a Mathcad expression. So why not let the row index do that....

Then it might look like this:

Also bear in mind that it's almost as difficult to bring units over from Excel to Mathcad

Success!

Luc

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

-MFra- — Thu, 21 May 2020 18:25:36 GMT

Hi,

But, by chance, you are looking for something like this (it can be developed to make it more automatic):

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 22 May 2020 00:18:31 GMT

@LucMeekes wrote:

Apart from the fact that it is desirable to have a function that can define a worksheet variable, in order to drive that variable name from e.g. Excel, you must have a reference. At some point you need to decide which one of the many variable names that are defined in the Excel table will be used at which position in a Mathcad expression. So why not let the row index do that....

I may have grasped the wrong end of the stick here, Luc, but I was under the impression that the absolute row number of a component value might change, or a different component could be selected at a different time. In which case, a lookup-type solution is possibly a better way to go.

In the example below, I've created a tailored version of one of my lookup functions to make it easier to use.

Also bear in mind that it's almost as difficult to bring units over from Excel to Mathcad

How true. I'm surprised that no maintenance subscriber has asked for a modified version of str2num that converts quantified numeric strings (eg, "3.14159 mp" or 2.7Fb" (where mp = millipotrzebie and Fb = furschlugginer blintz )), so that we'd have:

str2num("3.14159 mp") = 7.111 μm

str2num("2.71828mp") = 99.014 t

I suppose some enterprising individual could probably write one ... (I think I've may have one hidden deep in my M15 archive)

In the meantime, here's one way of tackling the problem:

Cheers,

Stuart

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

LucMeekes — Fri, 22 May 2020 08:49:04 GMT

I guess instead of

str2num("2.71828mp") = 99.014 t

you meant

str2num("2.71828 Fb") = 99.014 t

Luc

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 22 May 2020 09:24:30 GMT

Hah! That I did, Luc! 😅

How apt that I recently changed my avatar to this ...

By way of contrition, I present a "simplified from a user perspective" use of my lookup-by-name function. I've put all the lookup functions into an Area and the data generation into another Area (as it's not needed by the general user).

This, hopefully, shows how easy it could be to use the functions:

copy and paste (Argggghhh!) the first Area into a worksheet,
load and convert the Excel data,
create a tailored read function (if the user wants to, otherwise use one of the lookup functions directly or create a shorter alias for the needed one)
lookup the required variables

Cheers,

Stuart

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

LucMeekes — Fri, 22 May 2020 10:43:27 GMT

"(Argggghhh!)"

Yes, apart from the crazy situation that mean() is a Premium function, you unfortunately cannot link to reference sheets in Prime Express.

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

StuartBruff — Fri, 22 May 2020 11:56:55 GMT

@LucMeekes wrote:

"(Argggghhh!)"

Yes, apart from the crazy situation that mean() is a Premium function,

That's just ... mean.

you unfortunately cannot link to reference sheets in Prime Express.

A nuisance, to be sure, but in M15 I used to include the (named) Areas in my template. That way, I wouldn't worry about changes to my Reference worksheets changing values in an already existing worksheet. I know I should have a better config control system for keeping versions separate, but I'm not that organized.

Got bored whilst working on something else, so threw together a val2num function in Mathcad Express. It converts quantified strings to standard numeric quantities (for quantities that it knows about). It's quite moody, though, and sulks if the user doesn't leave a space between the numeric value and the unit.

I'm working on making the function Potrzebie-aware. Apparently, Proxima Centauri is 17739573709665 furshlugginer potrzebie away (plus or minus a page or two).

Cheers,

Stuart

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Tom_VanHeuklon — Tue, 26 May 2020 21:14:09 GMT

Thank you all again for your excellent suggestions. Stuart, you interpreted correctly that in my case, the order of variables, and the names themselves may change, when a design is updated, or a new one created, so your search functions will be very helpful (after I port them to Mathcad 15). Still one step short of what I was hoping for, but way better than where I started. You also correctly anticipated my next problem.... that of unit handling. Thank you again! I had planned to manage units outside Mathcad, but the functions you provided will certainly make me re-think that.

Finally, I want to share that I have had some success changing variable names outside Mathcad in a text editor. I have done that manually so far, but I think that if I can do it manually in a text editor, I should be able to automate that with something like Python. Unfortunately, my attempts to create a new variable in a text editor have failed.

Thanks again,

Tom

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

Tom_VanHeuklon — Mon, 08 Jun 2020 18:50:34 GMT

I think I found a solution to the second half of my problem... that of programmatically CREATING a variable in Mathcad: AutoHotKey (https://www.autohotkey.com/). The simple example below shows a program in AutoHotKey that creates 4 variable in Mathcad when Ctrl-K is pressed. I still have a lot to figure out with regard to opening a file and parsing it in AutoHotKey, but I am confident that it can be done. This might not be the most elegant solution but I think it will accomplish my objective. Thanks again for the suggestions.

Re: Programmatically create variable names and assign values from Excel spreadsheet (MCD15)?

terryhendicott — Mon, 08 Jun 2020 20:57:05 GMT

Hi,

Good bit of research.

Visual Basic in Excel had a similar SendKeys method.

Cheers

Terry