Can Excel components utilize the R1C1 reference style?

[bwilson-4]

Can Excel components utilize the R1C1 reference style? I currently have a vector of the default Excel column references (basically the alphabet) which I convert to string values, index, and concatenate to create my cell reference. This would be much cleaner if I could simply reference cells with the R1C1 reference style, but this does not seem to work. Is this functionality missing? Is there a plan for it in future Prime versions?

[tietjee]

I am not sure what you doing.  You may want to try the "lookup" functions. 

[VladimirN]

Can you provide an example file?

[RichardJ]

It's not clear what you are asking for. Why would "R5C5" be any easier to deal with than "E5"? Both require you to create strings to reference the cell.

[bwilson-4]

You are correct, they are both strings. However, with "R5C5" the "R" and "C" are constant in the concatenation and the "5" can be handle by converting a counter (i+1) to a string. With "E5", I can use a counter for the "5" but I have to use an array filled with letters ["A" "B" "C"...] and use a counter to reference elements of this array. It is an extra step and means I am limited by the size of my alphabet array.

In the attached file, I am using a REFPROP add-in with Excel to get gas properties (specifically viscosity and thermal conductivity for helium and argon). The inputs are range variables converted to vectors for temp and pressure ranges. The size of these vectors changes the size of the Excel range which should be returned as an output. The file works as is, but is not as flexible or clean as I'd like.

As a side note, if anyone has suggestions for how to fill only the utilized range in Excel with my REFPROP formula, that would be nifty. As it is, the Excel book gets filled with a lot of garbage.

[Werner_E · Accepted solution]

Unfortunately I can't read Prime 3.1 format, but you don't need a long string to do the job. While admittedly addressing cells using letters is a bit more work, its not that difficult.

The routine below will convert any integer from 0 up to its correct pendent in column letters. 0 corresponds to A, etc.

But AFAIK Excel is limited to 2^14=16384 columns (and 2^20=1048576 rows) anyway 😉 So XFD would be the last possible column.

[VladimirN]

Werner,

You can find a PDF copy of this Prime 3.1 worksheet in attachment.