Mr. Dan was most helpful with my first question. I'm reposting this as a separate subject, well, because it is, and to try to elicit a response. Mr. Dan gave me some tips on using checkboxes. The stakeholder insists on radio buttons instead of checkboxes, but I tried to follow the rest of his logic, and it made the code much cleaner.
Now it just won't run, and I don't know why. I think it is because I'm calling out named ranges incorrectly, but this is my first try at Visual Basic, so maybe it's something completely different. Should I be using a Property Let or Property Get statement instead of trying to set variable values based on the contents of a named range? Any tips or helpful hints or insights into why my code doesn't work? (I called out everything as public so it would show up in the macro selection box while I'm working. When the code is working, I'll make those Publics into Dim or Private statements except for the variable declarations).
Thank you very much,
Shelly S.
----------------------------------------------
Option Explicit
Option Base 1
' Path option buttons:
Public BOP1Path As OptionButton
Public BOP2Path As OptionButton
Public HPHybridPath As OptionButton
Public GHybridPath As OptionButton
' Crawlspace option buttons:
Public CrawlYes As OptionButton
Public CrawlNo As OptionButton
' Hot water option buttons:
Public StdDHW As OptionButton
Public EffDHW As OptionButton
' If Gas Hybrid system, "bin" variable to hold AFUE correction.
Public AFUEBin As Byte
' Math fields. Value set depending on radio button selected.
Public PathValue As Byte
Public CrawlValue As Byte
Public DHWValue As Byte
Public SumValue As Byte
Public SetUo As Byte
Public Sub FeedButtons()
' Assign worksheet cells to Option Button variables.
Set BOP1Path.Value = ActiveSheet.Range("TradeOff Worksheet!BOP1")
Set BOP2Path.Value = ActiveSheet.Range("TradeOff Worksheet!BOP2")
Set HPHybridPath.Value = ActiveSheet.Range("TradeOff Worksheet!HPHybrid")
Set GHybridPath.Value = ActiveSheet.Range("TradeOff Worksheet!GHybrid")
Set CrawlYes.Value = ActiveSheet.Range("TradeOff Worksheet!CrawlY")
Set CrawlNo.Value = ActiveSheet.Range("TradeOff Worksheet!CrawlN")
Set StdDHW.Value = ActiveSheet.Range("Tradeoff Worksheet.xls!StdWater")
Set EffDHW.Value = ActiveSheet.Range("Tradeoff Worksheet.xls!EffWater")
' Reads button settings and feeds value variables.
If BOP1Path = True Then PathValue = 1
If BOP2Path = True Then PathValue = 2
If HPHybridPath = True Then PathValue = 3
If GHybridPath = True Then PathValue = 4
If CrawlYes = True Then CrawlValue = 1
If CrawlNo = True Then CrawlValue = 10
If StdDHW = True Then DHWValue = 1
If EffDHW = True Then DHWValue = 2
' Populates the AFUEBin variable(used in index value calculation)
If Range("Tradeoff Worksheet.xls!AFUE") >= 0.7 & Range("Tradeoff Worksheet.xls!AFUE") < 0.74 Then AFUEBin = 0
If Range("Tradeoff Worksheet.xls!AFUE") >= 0.74 & Range("Tradeoff Worksheet.xls!AFUE") < 0.76 Then AFUEBin = 0.0033
If Range("Tradeoff Worksheet.xls!AFUE") > 0.76 Then AFUEBin = 0.0046
If Range("Tradeoff Worksheet.xls!AFUE") < 0.7 Then MsgBox ("The gas hybrid path requires an equipment efficiency of at least 0.70.")
Call DoMath
End Sub
Public Sub GrayStdDHW()
' Grays out standard hot water tank option in hybrid systems.
If PathValue = 3 Then StdDHW.Enabled = False
If PathValue = 4 Then StdDHW.Enabled = False
End Sub
Public Function DoMath()
'Sets SumValue to the sum of the three Value fields
SumValue = PathValue + CrawlValue + DHWValue
Call TargetUo(SumValue)
End Function
Public Sub TargetUo(SumValue)
'Looks up the target Uo value based on SumValue
Select Case SumValue
Case 2
Range("Tradeoff Worksheet.xls!TargetUo") = Range("Weightings.xls!Sum2")
Case 3
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum3")
Case 4
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum4")
Case 5
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum5a + AFUEBin")
Case 6
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum6")
Case 12
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum12")
Case 13
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum13")
Case 14
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum14")
Case 15
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum15a + AFUEBin")
Case 16
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum16")
End Select
End Sub
Now it just won't run, and I don't know why. I think it is because I'm calling out named ranges incorrectly, but this is my first try at Visual Basic, so maybe it's something completely different. Should I be using a Property Let or Property Get statement instead of trying to set variable values based on the contents of a named range? Any tips or helpful hints or insights into why my code doesn't work? (I called out everything as public so it would show up in the macro selection box while I'm working. When the code is working, I'll make those Publics into Dim or Private statements except for the variable declarations).
Thank you very much,
Shelly S.
----------------------------------------------
Option Explicit
Option Base 1
' Path option buttons:
Public BOP1Path As OptionButton
Public BOP2Path As OptionButton
Public HPHybridPath As OptionButton
Public GHybridPath As OptionButton
' Crawlspace option buttons:
Public CrawlYes As OptionButton
Public CrawlNo As OptionButton
' Hot water option buttons:
Public StdDHW As OptionButton
Public EffDHW As OptionButton
' If Gas Hybrid system, "bin" variable to hold AFUE correction.
Public AFUEBin As Byte
' Math fields. Value set depending on radio button selected.
Public PathValue As Byte
Public CrawlValue As Byte
Public DHWValue As Byte
Public SumValue As Byte
Public SetUo As Byte
Public Sub FeedButtons()
' Assign worksheet cells to Option Button variables.
Set BOP1Path.Value = ActiveSheet.Range("TradeOff Worksheet!BOP1")
Set BOP2Path.Value = ActiveSheet.Range("TradeOff Worksheet!BOP2")
Set HPHybridPath.Value = ActiveSheet.Range("TradeOff Worksheet!HPHybrid")
Set GHybridPath.Value = ActiveSheet.Range("TradeOff Worksheet!GHybrid")
Set CrawlYes.Value = ActiveSheet.Range("TradeOff Worksheet!CrawlY")
Set CrawlNo.Value = ActiveSheet.Range("TradeOff Worksheet!CrawlN")
Set StdDHW.Value = ActiveSheet.Range("Tradeoff Worksheet.xls!StdWater")
Set EffDHW.Value = ActiveSheet.Range("Tradeoff Worksheet.xls!EffWater")
' Reads button settings and feeds value variables.
If BOP1Path = True Then PathValue = 1
If BOP2Path = True Then PathValue = 2
If HPHybridPath = True Then PathValue = 3
If GHybridPath = True Then PathValue = 4
If CrawlYes = True Then CrawlValue = 1
If CrawlNo = True Then CrawlValue = 10
If StdDHW = True Then DHWValue = 1
If EffDHW = True Then DHWValue = 2
' Populates the AFUEBin variable(used in index value calculation)
If Range("Tradeoff Worksheet.xls!AFUE") >= 0.7 & Range("Tradeoff Worksheet.xls!AFUE") < 0.74 Then AFUEBin = 0
If Range("Tradeoff Worksheet.xls!AFUE") >= 0.74 & Range("Tradeoff Worksheet.xls!AFUE") < 0.76 Then AFUEBin = 0.0033
If Range("Tradeoff Worksheet.xls!AFUE") > 0.76 Then AFUEBin = 0.0046
If Range("Tradeoff Worksheet.xls!AFUE") < 0.7 Then MsgBox ("The gas hybrid path requires an equipment efficiency of at least 0.70.")
Call DoMath
End Sub
Public Sub GrayStdDHW()
' Grays out standard hot water tank option in hybrid systems.
If PathValue = 3 Then StdDHW.Enabled = False
If PathValue = 4 Then StdDHW.Enabled = False
End Sub
Public Function DoMath()
'Sets SumValue to the sum of the three Value fields
SumValue = PathValue + CrawlValue + DHWValue
Call TargetUo(SumValue)
End Function
Public Sub TargetUo(SumValue)
'Looks up the target Uo value based on SumValue
Select Case SumValue
Case 2
Range("Tradeoff Worksheet.xls!TargetUo") = Range("Weightings.xls!Sum2")
Case 3
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum3")
Case 4
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum4")
Case 5
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum5a + AFUEBin")
Case 6
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum6")
Case 12
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum12")
Case 13
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum13")
Case 14
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum14")
Case 15
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum15a + AFUEBin")
Case 16
Range("Weightings.xls!TargetUo") = Range("Tradeoff Worksheet.xls!Sum16")
End Select
End Sub