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
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
