Hello folks,
this is about a basic issue hence no example sheet.
For a lengthy calculation I'd like to read in parameters from excel. As this excel sheet is lenghty as well I want to check first whether the parameters in the Excel sheet match the parameters in my MatchCad sheet (naming and order of parameters). I'm thinking along the lines of making a one-to-one comparison of variable names (not the variable values!) in order to verify a match.
Does anybody has an idea how to conduct such a comparison?
Thanks in advance for any idea
Raiko
Solved! Go to Solution.
Raiko Milanovic wrote:
😞
Just an idea I got: might it be possible to read in the mathcad parameters into a textbox and do the comparison via VB? I'm thinking of a property called "GetText" but I'm unsure on how to apply it.
Raiko
I am still unsure if that will work with subscripts. I think the approach I mentioned above, and Stuart's detailed explanation is the best way to go.
Mike
Could you first call the parameters you want to check into Mathcad, not the whole sheet, and then perform the check?
Mike
Hello Mike,
that's exactly what I want to do. Call the parameters - then compare them to the MC parameters. However, the MC parameters are variables. The content of the variables I could convert to a string and compare to some other string but not the variables themselves.
Any ideas?
Raiko
Probably best posting an replica problem if you can. I am sure what you are describing can be achieved.
Mike,
I've attached an example to further illustrate what I'm up to.
Raiko
Let me get this right.
For example, you would want to check the following:
Mike
Right!
The variable name; e.g. RmM, can easily extracted from the first column of Coeff. However, how do I check if this variable name from Excel matches a variable in mathcad?
Raiko
The only way I can envisage this being possible, is to define your variables as strings names at the top of your worksheet and do the check.
I do not think it will be possible if you use subscripts in Mathcad though.
😞
Just an idea I got: might it be possible to read in the mathcad parameters into a textbox and do the comparison via VB? I'm thinking of a property called "GetText" but I'm unsure on how to apply it.
Raiko
Raiko Milanovic wrote:
😞
Just an idea I got: might it be possible to read in the mathcad parameters into a textbox and do the comparison via VB? I'm thinking of a property called "GetText" but I'm unsure on how to apply it.
Raiko
I am still unsure if that will work with subscripts. I think the approach I mentioned above, and Stuart's detailed explanation is the best way to go.
Mike
Thank you Mike. Everbody seems to agree upon this, i.e. that it is not possible with MC functionalities, hence I will check if Visual basic has something up the sleeve.
Raiko
Raiko Milanovic wrote:
Thank you Mike. Everbody seems to agree upon this, i.e. that it is not possible with MC functionalities, hence I will check if Visual basic has something up the sleeve.
Raiko
I don't think I said that it wasn't possible. If you could constrain the problem a little more (ie, give assurances about the presence or not of other variables) then it might be possible. If for example, the VBScript ran through the Excel list of variables and checked that the given variables existed and were present in the correct order that would be partial solution; if you could guarantee that the list was complete, then it would be a complete solution.
One other thing ... what is the relationship between the Excel variables and their Mathcad counterparts (eg, do you read in the Excel sheet as a whole, then extract the values from the resulting matrix or do you read each variable directly)?
Stuart
Stuart,
my fault that I didn't read carefully enough. 😉
I'm reading in the Excel sheet (see the attached files in previous post) and pass the entire contect to a three column matrix. The variable name is a string I can extract from the leftmost column. The value of this variable is stored in the centre column and extracted by submatrix functionality. The unit of the variable is in the right column and passed as a string.
So how do I check if all variables in my mathcad sheet have a counterpart in the Excel sheet. Number of variables and order of read-in should suffice for accepting or rejecting the excel sheet I think.
Raiko
Raiko Milanovic wrote:
Stuart,
my fault that I didn't read carefully enough. 😉
I'm reading in the Excel sheet (see the attached files in previous post) and pass the entire contect to a three column matrix. The variable name is a string I can extract from the leftmost column. The value of this variable is stored in the centre column and extracted by submatrix functionality. The unit of the variable is in the right column and passed as a string.
So how do I check if all variables in my mathcad sheet have a counterpart in the Excel sheet. Number of variables and order of read-in should suffice for accepting or rejecting the excel sheet I think.
Oh. You mean something along the lines of the attached (noting that the variable name usually forms part of the first column rather than being all of it)?
The Text Box assumes that the expression defining the variables is tagged with the word "Excel". If it finds such a region, it gets the regions's xml string, extracts the names, compares them in order of occurence against the Excel-derived names, and returns a string array with each string giving the result of the comparison, the Mathcad variable name and the Excel-derived variable name - this should help pinpoint any discrepancies. There is an additional Mathcad function that gives a simple match/no match (1/0) for the entire list of variable names.
If it's of any use, you can play around with it to add error checking to cover the cases where there is no "Excel" tagged region and where the number of Excel variables doesn't match the number of Mathcad variables.
Stuart
Stuart, that's brilliant!
Thank you very much for the code. I'll use it.
Raiko
Raiko Milanovic wrote:
Hello folks,
this is about a basic issue hence no example sheet.
For a lengthy calculation I'd like to read in parameters from excel. As this excel sheet is lenghty as well I want to check first whether the parameters in the Excel sheet match the parameters in my MatchCad sheet (naming and order of parameters). I'm thinking along the lines of making a one-to-one comparison of variable names (not the variable values!) in order to verify a match.
Does anybody has an idea how to conduct such a comparison?
How about using an array with the variable names in and iterating through that? You could hide it in a collapsed Area once you've got it debugged and just set a variable to give you the result of the check (eg, a simple "OK" if everything's OK or a list of variables that aren't playing nicely). That is, something along the lines of:
Stuart
Thank you Stuart.
Neat idea, but then I do need to define the parameters twice. Once as a variable and once as a string for performing a check as you proposed it. This is error prone therefore I'd like to use only the variable names. Perhaps some nifty VB code does the trick?
Raiko
Raiko Milanovic wrote:
Thank you Stuart.
Neat idea, but then I do need to define the parameters twice. Once as a variable and once as a string for performing a check as you proposed it. This is error prone therefore I'd like to use only the variable names. Perhaps some nifty VB code does the trick?
Raiko
Errare Humane Est. The most obvious way to do that would seem to be to get the Excel array of variable names and pass it to the VBScript. Then iterate through the Math regions until you find variable names you want (I don't know how you guarantee that there are no other variables (such as index variables) or how you would identify them (Tagging them is an option, but then you've got an error-prone maintenance task if you need to change the structure of the worksheet). An alternative, but vary much along the same lines, is to use a file read function to hunt for the corresponding variable definition xml strings in the worksheet's source code.
Of course, there could be a mistake in any code that misses certain cases!
Stuart
Maybe it won't work for what you need, but consider an alternate approach: reference Mathcad from within Excel instead of referencing Excel from within Mathcad.
- Add a column in Excel with the Mathcad variable names (R.mM, R.p02, D, ...)
- Within Excel add VB routine to SetValue for each of these variables, calculate the worksheet, then GetValue.