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.
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.
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"
or
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,
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
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
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
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
Hi,
You could make it a little less ugly by renaming "MyVar" as just "v"
Appreciate your appreciation
Terry
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
@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
Hah! That I did, Luc! 😅
How apt that I recently changed my avatar to this ...
![scream - copy paste 01 [2].png](https://community.ptc.com/t5/image/serverpage/image-id/27266iEAA2E48D8FA62584/image-size/large?v=v2&px=999 "scream - copy paste 01 [2].png")
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
@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
Hi,
But, by chance, you are looking for something like this (it can be developed to make it more automatic):
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.
