The Goal:
My program currently calculates values for heating and cooling costs for a single month of use. Now, I am trying to allow users to enter multiple months of use.
What I Have Done so Far:
I created functions (along with the Userform and output sheet) that calculate the heating and cooling costs for 2 different situations (4 functions total). Below is the code for one of them. The only change between the different functions is the line that has 2 stars next to it. Those are 4 separate drop downs in the VBA userform (there may have been a more efficent way to do that, but alas here we are).
Function CoolingCostS1(month As String, sqft As Single, ElecAUC As Single, Days As Single) As Single
'variable declaeration
Dim cost As Single
Select Case Main.ddRegion.value 'Case statement for region value
Case "South"
**Select Case Main.ddS1Cooling.value**
Case "Chill Water System"
Set chillWater = New clsMonth 'create new object for CWS system
With chillWater 'assign calculated values
.January = 0.7136 / 20
.February = 0.6755 / 20
.March = 0.6528 / 20
.April = 0.7773 / 20
.May = 0.8213 / 20
.June = 0.8715 / 20
.July = 0.9 / 20
.August = 1.0243 / 20
.September = 1.0516 / 20
.October = 0.8514 / 20
.November = 0.7095 / 20
.December = 0.6994 / 20
cost = .ValueFor(month) * sqft * ElecAUC * Days
End With
Case "Direct Expansion"
Set DX = New clsMonth
With DX
.January = 0.577 / 20
.February = 0.553 / 20
.March = 0.516 / 20
.April = 0.611 / 20
.May = 0.703 / 20
.June = 0.74 / 20
.July = 0.801 / 20
.August = 0.834 / 20
.September = 0.9333 / 20
.October = 0.686 / 20
.November = 0.597 / 20
.December = 0.4907 / 20
cost = .ValueFor(month) * sqft * ElecAUC * Days
End With
End Select
Case "North"
Select Case Main.ddS1Cooling.value
Case "Chill Water System"
Set chillWater = New clsMonth 'create new object for CWS system
With chillWater 'assign calculated values
.January = 0.775 / 20
.February = 0.845 / 20
.March = 0.699 / 20
.April = 0.722 / 20
.May = 0.751 / 20
.June = 0.1 / 20
.July = 0.9 / 20
.August = 0.95 / 20
.September = 0.946 / 20
.October = 0.749 / 20
.November = 0.739 / 20
.December = 0.75 / 20
cost = .ValueFor(month) * sqft * ElecAUC * Days
End With
Case "Direct Expansion"
Set DX = New clsMonth
With DX
.January = 0.536 / 20
.February = 0.52 / 20
.March = 0.49 / 20
.April = 0.482 / 20
.May = 0.511 / 20
.June = 0.5 / 20
.July = 0.5 / 20
.August = 0.461 / 20
.September = 543 / 20
.October = 0.521 / 20
.November = 0.497 / 20
.December = 0.531 / 20
cost = .ValueFor(month) * sqft * ElecAUC * Days
End With
End Select
End Select
CoolingCostS1 = cost
End Function
As you can see, these functions only accept single months. The userform has an option for multiple months and when selected it displays check boxes, one for each month.
When the simulate button is clicked, the below code is executed:
Private Sub bSimulate_Click() 'logic to calculate simulation values
'''''''''''variable decleration'''''''''''''''''''''''''
Dim s1Sqft As Single, s2Sqft As Single, s1ElecAUC As Single, s2ElecAUC As Single, s1HeatAUC As Single, s2HeatAUC As Single, Days As Single
'''''''''''conditional if and logic'''''''''''''''''''''
If IsNumeric(Me.txtS2elec.value) = True And IsNumeric(Me.txtS2NG.value) = True And IsNumeric(Me.txtS2sqft.value) = True And Me.ddS2cooling.ListIndex > -1 And Me.ddS2Heating.ListIndex > -1 Then
**case for unselected using prior information**
**case for selected using new information**
'variable assignment
s1Sqft = txtS1sqft.value 'system 1
s1ElecAUC = txtS1elec.value
s1HeatAUC = Me.txtS1NG.value
s2Sqft = txtS2sqft.value 'system 2
s2ElecAUC = txtS2elec.value
s2HeatAUC = txtS2NG.value
Days = txtUseDays.value 'usage page
'resets cells back to white
Me.txtS2elec.BackColor = vbWhite
Me.txtS2NG.BackColor = vbWhite
Me.txtS2sqft.BackColor = vbWhite
'System one output
Cells(13, 3).value = Days
Cells(14, 3).value = Application.WorksheetFunction.RoundUp(CoolingCostS1(Me.ddMonthOfUse.value, s1Sqft, s1ElecAUC, Days) / s1ElecAUC, 0) & " KWH"
Cells(14, 5).value = Application.WorksheetFunction.RoundUp(CoolingCostS1(Me.ddMonthOfUse.value, s1Sqft, s1ElecAUC, Days), 0)
Cells(15, 3).value = Application.WorksheetFunction.RoundUp((HeatingCostS1(Me.ddMonthOfUse.value, s1Sqft, s1HeatAUC, Days) / s1HeatAUC), 0) & " " & Me.ddHeatingUtility.value
Cells(15, 5).value = Application.WorksheetFunction.RoundUp(HeatingCostS1(Me.ddMonthOfUse.value, s1Sqft, s1HeatAUC, Days), 0)
'System two output
Cells(21, 3).value = Days
Cells(22, 3).value = Application.WorksheetFunction.RoundUp(CoolingCostS2(Me.ddMonthOfUse.value, s2Sqft, s2ElecAUC, Days) / s2ElecAUC, 0) & " KWH"
Cells(22, 5).value = Application.WorksheetFunction.RoundUp(CoolingCostS2(Me.ddMonthOfUse.value, s2Sqft, s2ElecAUC, Days), 0)
Cells(23, 3).value = Application.WorksheetFunction.RoundUp((HeatingCostS2(Me.ddMonthOfUse.value, s2Sqft, s2HeatAUC, Days) / s2HeatAUC), 0) & " " & Me.ddHeatingUtility.value
Cells(23, 5).value = Application.WorksheetFunction.RoundUp(HeatingCostS2(Me.ddMonthOfUse.value, s2Sqft, s2HeatAUC, Days), 0)
'System one information output
Cells(12, 9).value = Me.ddS1Cooling.value
Cells(13, 9).value = Me.ddS1Heating.value
Cells(14, 9).value = Me.txtS1sqft.value
'System two information output
Cells(20, 9).value = Me.ddS2cooling.value
Cells(21, 9).value = Me.ddS2Heating.value
Cells(22, 9).value = Me.txtS2sqft.value
Else
HighlightBadCells2 'checks for incorrect cell input values
MsgBox "Please check the highlighted cells"
GoTo CleanFail
End If
Main.Hide
CleanFail:
End Sub
Where the two stars are, that was me brainstorming the possibility of doing two cases -one for multiple months being selected and for not.
Stack wont let me upload pictures yet, so I cant show you the userform.
The Problem:
I am having trouble trying to figure out the most efficient way to get the total cost over the multiple months that are selected. I have some ideas bouncing around but can't seem to get them out. Every time I start brainstorming I end up with either lots and lots of code or tons of separate functions or something that just seems absurd for the task. I figured I could use the existing functions, but I don't know how to do that when they select the multiple months.
Thanks in advance for your help. If anything is unclear, please let me know. That made sense to me but may be a mess for anyone else.
