Problem AFTER returning from a called Function in an Add-In (XXX.XLA) file

[JWBygrave]

Hi everyone.
I'm newly registered on the forum but it has been the source of much help in the past year. I've searched for posts dealing with this issue but had no luck finding one, yet.
I'm stumped on a problem that has appeared as I am in the process of creating a fairly extensive Excel/VBA- based program to select compressors and calculate their performance for my company. I'm an engineer but not a programmer. Plenty of Excel background but hadn't taken the step into VBA until a year ago.

Problem:
A Sub I have written to take the already input operating point data (pressures, temperature and flow), convert the values to the equivalent ones whenever the system of units (metric, English, mks, cgs, etc. - many different customers have different preferences) being used is changed, and then replace the input values with the converted ones, has the following partial code (without the +++ and === separators):

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If FlowType = "Discharge Mass" Then

For inti = 1 To NumOpPts
InletPressure(inti, 1) = InletPressure(inti, 1) * ConvFactors2(16, 1) / ConvFactors1(16, 1)
InletTemperature(inti, 1) = (InletTemperature(inti, 1) + TempConv1(13, 1)) / ConvFactors1(15, 1) * ConvFactors2(15, 1) - TempConv2(13, 1)
DischargePressure(inti, 1) = DischargePressure(inti, 1) * ConvFactors2(16, 1) / ConvFactors1(16, 1)
Flow(inti, 1) = Flow(inti, 1) * Conversion
Sheets("Operating Point Input Module").Cells(7 + inti, 5) = InletPressure(inti, 1)
Sheets("Operating Point Input Module").Cells(7 + inti, 6) = InletTemperature(inti, 1)
Sheets("Operating Point Input Module").Cells(7 + inti, 7) = DischargePressure(inti, 1)
Sheets("Operating Point Input Module").Cells(7 + inti, 10) = Flow(inti, 1)
Next inti

End If
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

=========================================================================================================================

If FlowType = "Inlet Normal" Then

For inti = 1 To NumOpPts
InletPressure(inti, 1) = InletPressure(inti, 1) * ConvFactors2(16, 1) / ConvFactors1(16, 1)
InletTemperature(inti, 1) = (InletTemperature(inti, 1) + TempConv1(13, 1)) / ConvFactors1(15, 1) * ConvFactors2(15, 1) - TempConv2(13, 1)
DischargePressure(inti, 1) = DischargePressure(inti, 1) * ConvFactors2(16, 1) / ConvFactors1(16, 1)
Flow(inti, 1) = Flow(inti, 1) * Conversion * (Application.Run("'REFPROP.XLA'!Density", InputGasMix(inti, 1), "PT", "SI", 0.101325, TempConv2(1, 1)) / Application.Run("'REFPROP.XLA'!Density", InputGasMix(inti, 1), "PT", "SI", 0.101325, TempConv1(1, 1))
MsgBox (InletPressure(inti, 1))
Sheets("Operating Point Input Module").Cells(7 + inti, 5) = InletPressure(inti, 1)
Sheets("Operating Point Input Module").Cells(7 + inti, 6) = InletTemperature(inti, 1)
Sheets("Operating Point Input Module").Cells(7 + inti, 7) = DischargePressure(inti, 1)
Sheets("Operating Point Input Module").Cells(7 + inti, 9) = Flow(inti, 1)
Next inti

End If

==========================================================================================================================
Note that some of the code lines will word-wrap on your screen dependent on the width of your window when you view this. They are not wrapped in the actual code.

The code between the rows of +++ signs works well. The code between the === signs stops at the bold line and a pop-up window tells me there is a subscript out of range (Run time error 9). When I hit "Debug" it takes me to the bold line of code and highlights it in yellow. Not doing anything but hitting the F5 key to continue, allows the code to continue calculating and do the rest of the code until it reaches this line again. If I re-arrange things to put the "InletTemperature" line above the "InletPressure" line, then it does the same thing for the "InletTemperature" line, but not the "In;etPressure" line.

I had added the MsgBox line to make sure that InletPressure(inti, 1) was valid and the MsgBox outputs the correct value. It does.

I tried making a different subscript (jinti=inti) and then changing the offending line to Sheets("Operating Point Input Module").Cells(7 + jinti, 5) = InletPressure(jinti, 1) but this didn't work, either. I did declare the jinti variable as an integer.

I commented out the call to the Density Function in the REFPROP.XLA add-in (Bold Italics below):
Flow(inti, 1) = Flow(inti, 1) * Conversion '* (Application.Run("'REFPROP.XLA'!Density", InputGasMix(inti, 1), "PT", "SI", 0.101325, TempConv2(1, 1)) / Application.Run("'REFPROP.XLA'!Density", InputGasMix(inti, 1), "PT", "SI", 0.101325, TempConv1(1, 1)))
and this got it to work, but I obviously don't get the correct conversion factor for the normal flow if the base temperature [TempConv(2,1)] changes between the normal flow units (MMSCFD is at 60F, normal m^3/hr is at 0C, etc. for those interested).

I have checked the code in the Density Function and within the whole of REFPROP.XLA and the variable "inti" does not appear anywhere, so it shouldn't be getting re-assigned a value. In fact, I had started with "i" and changed it to "inti" in case it was clashing with a variable called "i" within the Density Function. Please note the "REFPROP.XLA" add-in is part of a suite of software we bought from the US National Institute of Standards and Technology (NIST) to calculate thermodynamic properties of gas mixtures. Much of it is protected and invisible to me. However, if the problem were being generated inside the add-in, why is the procedure able to continue and function properly and completely when all I have done is hit the F5 key to get it to continue?

I have tried moving the calls to the Density Function to elsewhere in the Sub procedure and this does change things slightly. However, whenever the next Sheets("Operating Point Input Module").Cells(7 + inti, 5) = XXXXXXXXXXX(inti, 1) is encountered, I get the same error - even in the section of code above between the +++ signs! I also get the error showing up for the first time any other Sheets or Worksheets or Workbook function is encountered. Wherever one of these functions is encountered first following the call to the Density Function, the error appears. It therefore seems to be associated with going to the add-in, coming back with an answer, and then being re-directed to a different worksheet in the workbook.

Sorry for the long-winded explanation, but I wanted to give you all the information I have. Do you have any ideas? I've run out and any help would be really appreciated.

Thanks in advance again,
Jon

 

[shg]

1. If you aren't using Option Explicit, then inti may reference a public variable declared elsewhere. Put Option Explicit at the top of every code module and declare variables appropriate to usage until you get a clean compile.

2. I would do all calculations in cgs or mks and then do unit conversions at the very, very, very end.

 

[shg]

... at the very, very, very end.

... in columns reserved for that purpose.

 

[JWBygrave]

Many thanks for the tip, shg.
1) I have so far gone through all the modules and added "Option Explicit" to the top of all of them (some were not written by me and I may have inherited some other bugs). No change in behavior, yet, but I will go through them all to verify all the variables are declared and update the post with any findings.

2) I appreciate the suggestion. I intend to set up all the calculations to be done in one of the unit systems only (REFPROP's and my company's default is SI) but I need to be able to modify input values from many different combinations of units to a single system for calculation first. I also want to be able to input customer data from enquiry documents in the customer's units (in order to avoid the user having to do all the conversions manually before input) and then convert the visible input/output into whatever the user prefers to work in. This means changing the unit system after the operating point requirements have been input. The input/output data therefore needs to be converted, too. This was possible in my previous company's selection software and I would like to replicate it in what I am creating (not copying!!!) here. I have the outline of a scheme to do this and it seems to be working well - except for this darned error when I need to access the Function from the Add-In!!!

Thanks again for the quick reply!

 

[shg]

I would similarly reserve separate columns for customer input data. At the top of each, I would have a drop-down to select input units, and use that dropdown on another sheet (your input sheet) to select the constant that converts customer units to your units.

 

[shg]

In other words, do the unit conversions via formula, not code -- it makes it more transparent to both the user and you.

Also, it the customer uses one completely consistent set of units (or, and course, you do also), you only need one drop-down -- the one that picks his. All of the other conversion will flow from that single selection.

 

[JWBygrave]

Thank-you all once again, if you're still puzzling over this one.

Going back to the original problem:
It looks like I have found a solution, though it still beats me why it is doing this.
The solution seems to be that I need to add the following line immediately after the line with the call to the Density Function in the REFPROP.XLA:

Workbooks("Filename.xlsm").Worksheets("SheetName").Activate

I can only think that the code "thinks" it's still in the REFPROP.XLA when I am trying to input something in the Cells in the "Operating Point Input Module" worksheet, can't find what "inti" is or can't find the worksheet at that moment and gets lost. I suspect it is more to do with not finding the worksheet because of some other trials I did with no appearance of a subscript to an array in them. Why it would still call out Runtime error 9 and why it recovers by just pressing the F5 key is beyond me. If someone would like to explain so that it doesn't keep me wondering, I'd be very grateful. In the meantime, it looks like I will have to add this line of code after every time I go for values to the REFPROP.XLA - tedious but apparently necessary.

By the way, I just had a thought: Should I be calling a REFPROP.XLAM add-in instead of REFPROP.XLA as I am using Excel 2007 with a .xlsm file extension? If so, any ideas on how to convert it? I'm pretty sure NIST does not offer a .XLAM version.

Anyway, please all keep up the good work and I look forward to contributing if I can, once I become more seasoned in VBA.

 

[shg]

The solution seems to be that I need to add the following line immediately after the line with the call to the Density Function in the REFPROP.XLA:

Workbooks("Filename.xlsm").Worksheets("SheetName").Activate

That's because some code is making an unqualified reference to a range, and the default is to the active worksheet. So it only works wen the proper worksheet is active.

Bad code.

can't find what "inti" is

Right-click and select Definition. Or do a Find: click Match Case, Whole Word, and Entire Project.

 

[shg]

By the way, I just had a thought: Should I be calling a REFPROP.XLAM add-in instead of REFPROP.XLA as I am using Excel 2007 with a .xlsm file extension? If so, any ideas on how to convert it? I'm pretty sure NIST does not offer a .XLAM version.

You should reference whichever version you're using. According to their website (Answers to Frequently Asked Questions), they have both versions.

Also, you don't need

Application.Run("'REFPROP.XLA'!Density", ...")

just

myResult = Density(arg1, arg2, ...)

or

Call Density(arg1, arg2, ...)

or

Density arg1, arg2, ...

if you don't need the return value.

 

[JWBygrave]

First of all, thank-you for your time, shg. I appreciate your patience with a relative newbie to VBA.
A few answers/comments to your replies above:

1) I understand what you are saying is the reason for the hiccup regarding an unqualified range, but I am struggling to find the location of the problem within the code. Searching for "inti" throughout the whole project in which I am coding as well as the REFPROP.XLA (and now REFPROP.XLAM - see below) and throughout all the other projects that are part of the overall VBA project trees yielded only the locations within the one module I was working in. The REFPROP add-in doesn't use it unless it is buried invisibly (to me) within the .DLLs that REFPROP calls up. I have no way of addressing that. The issue occurs the first time any instruction where a worksheet is being specified is called up after supposedly returning from the REFPROP add-in - even if there is no reference to "inti" or any other variable in the line of code specifying the worksheet. I've taken up enough of your time on this so I will move on, bad code and all, and use the work-around I mentioned yesterday. This is all temporary code I am developing in the language I am somewhat familiar with so I can get all the logic and functionality correct before it is taken over by proper programmers in head office. I'm afraid I will have to let them battle it out.

2) REFPROP.XLAM is created per the instructions on the NIST website and my project is now converted over to using it.

3) The reason I was having to use the Application.Run("REFPROP.XLA"!Density(...)) code was because even though the REFPROP.XLA add-in was available, it wasn't referenced by my project and so the Density Function couldn't be found. An issue with the name of my project that also started with "REFPROP" as well and the Editor would not allow the two to be linked. I have re-named my project and the add-in is now referenced. I have removed all the statements with Application.Run("REFPROP.XLA"!Density(...)) in them and it works.

Thanks again,
Jon