Ugly Code - Sending Values to and from VBA

[MattGreer]

I was wondering if there was a better/more succinct way of doing this.

I'm performing a calcuation in Excel 2007 based on the value of a combo box. Here's a snipped of the code:

If ComboBox2.Value = "lb/hr" Then
    ActiveSheet.Range("L20").Value = ActiveSheet.Range("E20").Value / 24 / 379.32 * ActiveSheet.Range("E15").Value

I suppose I could set all those things equal to variables previous to the logic loop, but that just seems, well, wasteful. I guess my question is, is there a way to shorten up the code to pull a value out of a cell in Excel to make the calculations a bit easier to read?

Thanks.

Thanks!!


Matt

 

[SkipVought]

Hi,

VBA aside, I'd be using Named Ranges on the worksheet, in all likelyhood.

I would rarely do a calculation like that in VBA.

Why not a formula on the sheet, and again, using Named Ranges???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

It's because the formula needs to change, quite drastically, depending on the selection in the combo box and on information in other cells. Additionally, a change in selection in more than one cell could change the formula. Nested IF equations are laborious and difficult to debug, in my opinion. Perhaps there's an easier way of doing this?

Additionally, if some values are missing, I don't want the calculation to proceed and I would like to change the formatting of other cells to indicate what is missing.

So if the value in cell A4 is missing, I would like cell A4 to turn yellow and the calculation result to say "err" instead of trying to calculate a value. It seemed that VBA would be the way to go for such a thing.



Thanks!!


Matt

 

[SkipVought]

Please post your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What values are selected in the combobox?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

A bit easier to read way:

With ActiveSheet
  If ComboBox2.Value = "lb/hr" Then
    .Range("L20") = .Range("E20") / 24 / 379.32 * .Range("E15")
...
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

OK, in this particular instance, the combo box is used to choose between the states of the substance, if it is a liquid or a gas, so "L" or "V".

The user enters property data for the substance in question. For example, to calculate density, the user needs to enter the following parameters: Molecular Weight, Pressure, Temperature, Compressibility, and the local Atmospheric Pressure. These values are then used to calculate density.

If the substance is a liquid, then no values are needed and the user must enter the value manually. That's where I would lose the formula if they enter something manually.

One option would be to make the labels/calc result disappear if the user selects "L", and then have another cell "appear" with the manual entry for density. I don't like that solution, honestly. I'd have to add more IF statements to other calculations to determine what phase was selected, and therefore what cell to select. Hope that makes sense.

Here's my newbie code. I was in the middle of playing with range objects to set formatting changes.

Dim aPress As Range
Dim MW As Single
Dim Press As Single
Dim Temp As Single
Dim Z As Single
Dim Dens As Range

Set aPress = ActiveSheet.Range("E14").Value
MW = ActiveSheet.Range("E15").Value
Press = ActiveSheet.Range("E16").Value
Temp = ActiveSheet.Range("E17").Value
Z = ActiveSheet.Range("E19").Value

If aPress.Value = "" Then
    With aPress
    .Value = "ERR"
    .Color = 255 'Bright Red
    .TintAndShade = 0
    End With
End If


If ComboBox3.Value = "L" Then
    ActiveSheet.Range("H16").Value = ""
ElseIf ComboBox3.Value = "V" Then
    ActiveSheet.Range("H16").Value = (MW * (aPress + Press)) / (10.73 * (Temp + 459.67) * Z)
Else
    ActiveSheet.Range("H16").Value = "ERROR"
End If

Set aPress = Nothing

I'm still considering whether or not change the formatting or to simply check for needed values and put "err" in the result.

The other problem is, if the user selects "V", and some values are missing, the user has to change the selection to "L" and then back again to "V" in order to update the calculation and reverse the formatting. Is there an "OnChange" event for an individual cell in Excel? I'm guessing not.

I appreciate the help! I hope this all makes sense!


Thanks!!


Matt