Error 13 - Active Sheet issue

[iklil]

Dear Mr Excels out there

I am trying to use an excel sheet containing macro to calculate my capital costs. This is actually a free download, something I have used 16 years ago and now I needed again and was very happy to find it online: https://richardturton.faculty.wvu.e...48ec-83f3-683353487bfc/capcost_2017_rev2.xlsm

It is a quite a powerful tool....when it works as it gives you the ability to get a crude estimate without really needing to go in depth. Again...when it works.

However with a few equipment - it causes and error. I have been looking at "debugging" it for a week now and I actually encountered this question here: Error on ActiveCell.Offset().Value about the same excel sheet. That is the day I registered to your forum. But as I was writing over an old thread I do not think I was able to reach any of you.

I have never been a modeller or never used Visual Basic before, but I can more or less follow logic.
I believe the issue is the code, unfortunately calculates the value, then converts to a format containing the currency.

however as this part is written in a sub, I believe next part where this data is to be registered to the result sheet (Equipment Summary), the value is further calculated with an inflation factor (CEPCI) however the calculation is carried out with the currency rather than the value hence causing an error in the "Active sheet" value.

I have used the suggestion from the earlier thread, which said changing [($\ #,###,###) to [(#,###,###) but this ends up also removing it from the small programme where you add/edit/define your equipment details (see below images) So I am not really sure if there is a better way. But it only worked when I have made this change at a certain part (calculation part) not when I changed it in whole module. But ok - that parts is in a way solved.

before:

but there is error and do not work. after change above

Second issue is that although a formatting is defined in the code for the currency to be added to the active sheet, somehow it ends up in pounds (see picture below). Obviously - I can change this from "Format cell" one by one, and in fact, I can clearly see somehow the code is defined in custom formatting but is replaced with pound. I believe this has something to due with the definition in the code with regards to "currency" but I have no idea how could set the default of the specific excel sheet to match the code.

If there is anyone who can help with both issues that would make the excel work in a similar fashion, yet, do not make it too complicated, I would be glad to hear.
Thanks and best regards,

 

[Norie]

Have you checked what the value of the userAddedFilter named range is?

P.S. When posting code can you post, well the code, rather than an image.

 

[iklil]

Yep! And that was your answer to the person who asked the question before too! So bizarre yet I love that you are very consistent!
The spreadsheet has many worksheets. In one of the worksheets the constants and the limits of one parameter (A) that the user defined is ranged as Amin and Amax. I am sure the value I am entering is correct.

I will try to write the code below... hopefuly I do it correctly

Dim iSelection As Integer
Dim aProperties(7, 1)
Dim sK1 As Single
Dim sK2 As Single
Dim sK3 As Single
Dim sFM As Single
Dim sArea As Single
Dim lCp As Long
Dim sFP As Single
Dim iSpares As Integer
Dim lCBM As Long
Dim lCBM0 As Long
Dim sFBM As Single
Dim bQuestion As Boolean
Dim strFilterInfo As String
Dim strFilterMOC As String
Dim strFilterType As String
Dim iTemp As Integer
Dim sHolder As Single
Dim iCounter As Integer

'*****************************************************************************
'   The three modules below change information in "filterForm" depending     *
' on whether the action chosen was add, edit, or add data.                   *
'*****************************************************************************

Sub displayAddFilter()
    Unload addEquipmentForm
    Load filterForm
    filterForm.Caption = "Add a Filter to Unit " & Range("unitNumber").Value
    Call prepareFilterFormUnits
    filterForm.Show
End Sub

Sub displayEditFilter()
    Load filterForm
    Call prepareFilterFormUnits
    Call fillFilterForm
    
    filterForm.Caption = "Edit Filter Fr-" & (Val(Range("unitNumber")) + iSelection)
    
    filterForm.filterFinish.Caption = "Edit"
    filterForm.filterAdd.Enabled = False
    filterForm.filterAdd.Visible = False
    filterForm.Show
End Sub

Sub prepareFilterFormUnits()
    With filterForm.cbArea
        .RowSource = ""
        .AddItem "square meters"
        .AddItem "square feet"
    End With
    
    Select Case True
        Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
            .Value = "square meters"
                filterForm.cbArea.ListIndex = 0
        Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
            .Value = "square feet"
                filterForm.cbArea.ListIndex = 1
    End Select
End Sub

'**************************************************************************
'   The two modules below call different filter procedures depending      *
' on whether the action chosen was add a filter or edit a filter.         *
'**************************************************************************

Sub addFilter()
    If Range("userAddedFilters").Offset(1, 0).Value = "" Then
        Call editEquipment.unhideEquipment("userAddedFilters")
    End If
    Call getFilterData
    Call calculateFilterCosts
    Application.ScreenUpdating = False
    Call insertRowsFilter
    Call insertFilter
    Application.ScreenUpdating = True
End Sub

Sub editFilter()
    Call getFilterData
    Call calculateFilterCosts
    Application.ScreenUpdating = False
    Call insertFilter
    Call listFilterProperties
    Application.ScreenUpdating = True
End Sub

Sub addMultipleFilters(iCounter As Integer)
    Application.ScreenUpdating = False
    
    If Range("userAddedFilters").Offset(1, 0).Value = "" Then
        Call editEquipment.unhideEquipment("userAddedFilters")
    End If
    
    Call getFilterData
    sArea = sArea / iCounter
    Call calculateFilterCosts
    sArea = sArea * iCounter
    lCp = lCp * iCounter
    lCBM = lCBM * iCounter
    
    With filterForm
        .tbBaseCost.Value = lCp
        .tbBaseCost = Format(filterForm.tbBaseCost, ["$\ #,###,###,###"])
        .tbModuleCost.Value = lCBM
        .tbModuleCost = Format(filterForm.tbModuleCost, ["$\ #,###,###,###"])
    End With

    Call insertRowsFilter
    Call insertFilter
    
    Application.ScreenUpdating = True
End Sub

Sub editMultipleFilters(iCounter As Integer)
    Application.ScreenUpdating = False
    
    Call getFilterData
    sArea = sArea / iCounter
    Call calculateFilterCosts
    sArea = sArea * iCounter
    lCp = lCp * iCounter
    lCBM = lCBM * iCounter
    
    With filterForm
        .tbBaseCost.Value = lCp
        .tbBaseCost = Format(filterForm.tbBaseCost, ["$\ #,###,###,###"])
        .tbModuleCost.Value = lCBM
        .tbModuleCost = Format(filterForm.tbModuleCost, ["$\ #,###,###,###"])
    End With
    Call insertFilter
    Call listFilterProperties
    Application.ScreenUpdating = True
End Sub

' This module takes the data from user form "filterForm"

Sub getFilterData()
    sArea = convert(filterForm, "area", Val(filterForm.tbArea))
    
    Select Case True
        Case filterForm.obBent
            strFilterInfo = "filterBent"
            strFilterType = "Bent"
        Case filterForm.obCartridge
            strFilterInfo = "filterCartridge"
            strFilterType = "Cartridge"
        Case filterForm.obDiscAndDrum
            strFilterInfo = "filterDiscAndDrum"
            strFilterType = "Disc And Drum"
        Case filterForm.obGravity
            strFilterInfo = "filterGravity"
            strFilterType = "Gravity"
        Case filterForm.obLeaf
            strFilterInfo = "filterLeaf"
            strFilterType = "Leaf"
        Case filterForm.obPan
            strFilterInfo = "filterPan"
            strFilterType = "Pan"
        Case filterForm.obPlateAndFrame
            strFilterInfo = "filterPlateAndFrame"
            strFilterType = "Plate And Frame"
        Case filterForm.obTable
            strFilterInfo = "filterTable"
            strFilterType = "Table"
        Case filterForm.obTube
            strFilterInfo = "filterTube"
            strFilterType = "Tube"
    End Select
    
    sHolder = -99
    If sArea < Range(strFilterInfo & "Amin") Then
        sHolder = sArea
        sArea = Range(strFilterInfo & "Amin")
    End If

    sK1 = Worksheets("Equipment Cost Data").Range(strFilterInfo & "K1").Value
    sK2 = Worksheets("Equipment Cost Data").Range(strFilterInfo & "K2").Value
    sK3 = Worksheets("Equipment Cost Data").Range(strFilterInfo & "K3").Value
    sFBM = Worksheets("Equipment Cost Data").Range(strFilterInfo & "FBM").Value  '2007
End Sub

'**********************************************************************************
'   The module below calculates cost for filters according to the correlations    *
'  given in "Analysis, Synthesis, and Design of Chemical Processes."              *
'**********************************************************************************

Sub calculateFilterCosts()
    lCp = 10 ^ (sK1 + sK2 * (Log(sArea) / Log(10)) + sK3 * _
        ((Log(sArea) / Log(10)) ^ 2)) * (iSpares + 1) / 397 * Range("CEPCI")
        
    If sHolder <> -99 Then
        sArea = sHolder
    End If
    
    lCBM = lCp * sFBM   '2007

    filterForm.tbBaseCost.Value = lCp
    filterForm.tbBaseCost = Format(filterForm.tbBaseCost, ["$\ #,###,###,###"])

    filterForm.tbModuleCost.Value = lCBM
    filterForm.tbModuleCost = Format(filterForm.tbModuleCost, ["$\ #,###,###,###"])
    
End Sub

Sub insertRowsFilter()
    bQuestion = False
    iSelection = 0
    Range("userAddedFilters").Select

    Do While bQuestion = False
        iSelection = iSelection + 1
        If IsEmpty(ActiveCell.Offset(iSelection, 0)) Then
            bQuestion = True
        End If
    Loop
    ActiveCell.Offset((iSelection + 1), 0).Rows("1:1").EntireRow.insert Shift:=xlDown
End Sub

Sub insertFilter()
' Filter naming procedure

Range("userAddedFilters").Offset(iSelection, 0).Value _
    = "=""Fr-"" & unitNumber + " & Val(iSelection)

    Range("userAddedFilters").Offset(iSelection, 1).Value = strFilterType
    
    iTemp = roundAmount(sArea * Range("preferenceArea"))
    Range("userAddedFilters").Offset(iSelection, 2).Value = "=ROUND(" & _
    sArea & "*preferenceArea, " & iTemp & ")"
    
    iTemp = roundAmount(filterForm.tbBaseCost)
    Range("userAddedFilters").Select
    ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
        (filterForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
    ActiveCell.Offset(iSelection, 7).Style = "Currency"
    ActiveCell.Offset(iSelection, 7).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    iTemp = roundAmount(filterForm.tbModuleCost)
    ActiveCell.Offset(iSelection, 8).Value = "=ROUND(" & _
        (filterForm.tbModuleCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
    ActiveCell.Offset(iSelection, 8).Style = "Currency"
    ActiveCell.Offset(iSelection, 8).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    iTemp = roundAmount(filterForm.tbBaseCost)
    Range("userAddedFilters").Select
    ActiveCell.Offset(iSelection, 9).Value = "=ROUND(" & _
        (filterForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
    ActiveCell.Offset(iSelection, 9).Style = "Currency"
    ActiveCell.Offset(iSelection, 9).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    iTemp = roundAmount(filterForm.tbModuleCost)
    ActiveCell.Offset(iSelection, 10).Value = "=ROUND(" & _
        (filterForm.tbModuleCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
    ActiveCell.Offset(iSelection, 10).Style = "Currency"
    ActiveCell.Offset(iSelection, 10).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
End Sub

'**************************************************************************
'   This module fills the edit version of filterForm with information     *
' regarding the filter that has been chosen for editing.  Both of the     *
' modules below are for editing an existing filter.                       *
'**************************************************************************

Sub fillFilterForm()
    Select Case Range("userAddedFilters").Offset(iSelection, 1).Value
        Case "Bent"
            filterForm.obBent = True
        Case "Cartridge"
            filterForm.obCartridge = True
        Case "Disc And Drum"
            filterForm.obDiscAndDrum = True
        Case "Gravity"
            filterForm.obGravity = True
        Case "Leaf"
            filterForm.obLeaf = True
        Case "Pan"
            filterForm.obPan = True
        Case "Plate And Frame"
            filterForm.obPlateAndFrame = True
        Case "Table"
            filterForm.obTable = True
        Case "Tube"
    End Select
    
    filterForm.tbArea = Val(Range("userAddedFilters").Offset(iSelection, 2))
End Sub

Sub listFilterProperties()
    editEquipmentForm.lbEquipmentDescription.Clear

' This section of code fills an array with the selected filter's properties
' The properties are then displayed in the edit window

    iSelection = editEquipmentForm.lbEquipmentName.ListIndex + 1

    aProperties(0, 0) = "Filter Type.................................."
    aProperties(0, 1) = Range("userAddedFilters").Offset(iSelection, 1)
    aProperties(1, 0) = "Filter Area................................"
    aProperties(1, 1) = Range("userAddedFilters").Offset(iSelection, 2) & " " _
        & Range("preferenceArea").Offset(0, -1).Value
    aProperties(2, 0) = " "
    aProperties(2, 1) = " "
    aProperties(3, 0) = " "
    aProperties(3, 1) = " "
    aProperties(4, 0) = " "
    aProperties(4, 1) = " "
    aProperties(5, 0) = " "
    aProperties(5, 1) = " "
    aProperties(6, 0) = "Base Equipment Cost...................................."
    aProperties(6, 1) = Format(Range("userAddedFilters").Offset(iSelection, 9), ["$\ #,###,###,###"])
    aProperties(7, 0) = "Base Bare Module Cost............................."
    aProperties(7, 1) = Format(Range("userAddedFilters").Offset(iSelection, 10), ["$\ #,###,###,###"])

    editEquipmentForm.lbEquipmentDescription.List = aProperties
End Sub

' The modules below are used for adding the filters to the cost summary, updating
' filters in the cost summary as they are edited, and deleting filters from
' the cost summary.

Sub addSummaryFilter()
    Dim rRange
    Application.ScreenUpdating = False

    Do While Range("costSummary").Offset(iCounter, 0).Value <> ""
        iCounter = iCounter + 1
    Loop
    
    Range("costSummary").Offset(iCounter, 0).Rows("1:1").EntireRow.insert Shift:=xlDown
    
    Range("costSummary").Offset(iCounter, 0) = "=""Fr-"" & unitNumber + " & Val(iSelection)
    iTemp = roundAmount(lCBM * Range("totalModuleFactor") / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 1) = "=ROUND(" & lCBM & _
    "*totalModuleFactor, " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 1).Style = "Currency"
    Range("costSummary").Offset(iCounter, 1).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Call filterGrassRoots
    
    iTemp = roundAmount((Range("totalModuleFactor") * lCBM _
    + Range("grassRootsFactor") * lCBM0) / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 3) = "=ROUND((totalModuleFactor*" & lCBM & _
    "+ grassRootsFactor*" & lCBM0 & "), " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 3).Style = "Currency"
    Range("costSummary").Offset(iCounter, 3).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Range("costSummary").Offset(iCounter, 5).ClearFormats
    Range("costSummary").Offset(iCounter, 5).HorizontalAlignment = xlCenter
    Range("costSummary").Offset(iCounter, 5).Font.Size = 12

    Range("costSummary").Offset(iCounter, 5) = "NA"
End Sub

Sub editSummaryFilter()
Dim rRange
    Application.ScreenUpdating = False

    iCounter = 0
    strFilterMOC = "Fr-" & (Range("unitNumber")) + Val(iSelection)
    Do While Range("costSummary").Offset(iCounter, 0) <> strFilterMOC
        iCounter = iCounter + 1
    Loop
    
    Range("costSummary").Offset(iCounter, 0) = "=""Fr-"" & unitNumber + " & Val(iSelection)
    iTemp = roundAmount(lCBM * Range("totalModuleFactor") / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 1) = "=ROUND(" & lCBM & _
    "*totalModuleFactor, " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 1).Style = "Currency"
    Range("costSummary").Offset(iCounter, 1).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Call filterGrassRoots
    
    iTemp = roundAmount((Range("totalModuleFactor") * lCBM _
    + Range("grassRootsFactor") * lCBM0) / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 3) = "=ROUND((totalModuleFactor*" & lCBM & _
    "+ grassRootsFactor*" & lCBM0 & "), " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 3).Style = "Currency"
    Range("costSummary").Offset(iCounter, 3).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Range("costSummary").Offset(iCounter, 5).ClearFormats
    Range("costSummary").Offset(iCounter, 5).HorizontalAlignment = xlCenter
    Range("costSummary").Offset(iCounter, 5).Font.Size = 12
    
    Range("costSummary").Offset(iCounter, 5) = "NA"
End Sub

Sub filterGrassRoots()
    lCBM0 = lCp
End Sub

 

[Norie]

Yep! And that was your answer to the person who asked the question before too! So bizarre yet I love that you are very consistent!

Can I ask what you are referring to here?

 

[iklil]

Can I ask what you are referring to here?

Sorry - I guess my first was really long, here it is:

Error 13 - Active Sheet issue

 

[iklil]

Can I ask what you are referring to here?

Can I assume that my problem with the excel sheet/code is too complicated that I would need to re-write the coding and until then it would not work?