VBA Macro calls function without code specifying it to

Question

I'm working on an Excel file which is meant to help keep track of financial transactions. Its core function consists of the user being able to input information and then by use of a "New Transaction" macro create a new page which contains all that info as well as keeps track of the result.

These results are summarized on a "Results" page. In order to keep track of different kinds of transactions separately, I implemented (copy pasted) a function which sums by cell color:

Function SumByColor(CellColor As Range, rRange As Range)

Dim cSum As Long
Dim ColIndex As Integer


ColIndex = CellColor.Interior.ColorIndex

For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
    
        cSum = WorksheetFunction.Sum(cl, cSum)
        
    End If
    
Next cl

SumByColor = cSum

End Function

Whenever a new page is created using the "New Transaction" macro, this page is inserted after the results page (the page "Vorlage - EXP" is a template)

If Sheets("Hilfsdaten").Range("H" & tcurrencyc) = "EXP" Then
    Worksheets("Vorlage - EXP").Copy After:=Worksheets("Results") 
    Sheets("Vorlage - EXP (2)").Name = tname

At this point the "New Transaction" macro calls the function "SumByColor" seemingly for every cell it is used on the results page. That is not necessarily a problem (except for the fact that it seems redundant), but some of those cells on the results page then display a #VALUE error afterwards, which can be removed by double clicking the cell and pressing enter. Since there are around 50 of those cells, that's not a practical way of resolving this. Is there a way I can stop the "New Transaction" macro from indirectly calling the "SumByColor" function ? It's worth noting that manually inserting a page after the results page doesn't yield the same #VALUE errors on the results page. Sorry if my wording was lengthy or unclear, please let me know what I could to clarify.

The "New Transactions" macro is quite lengthy, but here it is: (some words and comments in german, hope that doesn't confuse too much)

Sub NewTransaction()

Dim tname As String

Dim typecounter As Integer
Dim tcurrencyc As Integer
Dim tcurrencyt As String
Dim resultr As Integer
Dim resultc As Integer
Dim colorv As Long
resultr = 5
tname = Range("C22").Value
tcurrencyc = Sheets("Hilfsdaten").Range("E5").Value + 5




If Sheets("Hilfsdaten").Range("H" & tcurrencyc) = "EXP" Then
    Worksheets("Vorlage - EXP").Copy After:=Worksheets("Results")
    Sheets("Vorlage - EXP (2)").Name = tname
    
ElseIf Sheets("Hilfsdaten").Range("H" & tcurrencyc) = "IMP" Then
    Worksheets("Vorlage - IMP").Copy After:=Worksheets("Results")
    Sheets("Vorlage - IMP (2)").Name = tname
End If


'Übernehmen der festgelegten Daten auf dieses neue Worksheet'

typecounter = Sheets("Hilfsdaten").Range("B5").Value + 5

Sheets("Hilfsdaten").Range("B" & typecounter).Copy
Sheets(tname).Range("B2").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("C22").Copy
Sheets(tname).Range("B6").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("K24").Copy
Sheets(tname).Range("E6").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("G20").Copy
Sheets(tname).Range("D10").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("K20").Copy
Sheets(tname).Range("I10").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("G22").Copy
Sheets(tname).Range("D12").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("K22").Copy
Sheets(tname).Range("D14").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("D24").Copy
Sheets(tname).Range("O10").PasteSpecial Paste:=xlPasteFormulas

Sheets("Start").Range("H24").Copy
Sheets(tname).Range("O12").PasteSpecial Paste:=xlPasteFormulas

CutCopyMode = False


tcurrencyt = Sheets("Hilfsdaten").Range("F" & tcurrencyc).NumberFormat


Sheets(tname).Range("E6").NumberFormat = tcurrencyt

Sheets("Hilfsdaten").Range("G" & tcurrencyc).Copy
Sheets(tname).Range("C21").PasteSpecial Paste:=xlPasteFormulas

CutCopyMode = False

'Ergebnis auf "Ergebnisse" verlinken'

resultc = (Sheets("Hilfsdaten").Range("E5").Value * 2)


Do While Not IsEmpty(Sheets("Results").Cells(resultr, resultc))

resultr = resultr + 1

Loop

If Sheets(tname).Range("B2") = "Sicherung" Then

    Sheets("Results").Cells(resultr, resultc).Formula = "='" & tname & "'!G21"

    colorv = Sheets(tname).Range("G21").Interior.Color
    
    Sheets("Results").Cells(resultr, resultc).Interior.Color = colorv
    
ElseIf Sheets(tname).Range("B2") = "Stopp-Sicherung" Then

    Sheets("Results").Cells(resultr, resultc).Formula = "='" & tname & "'!G22"
    
    colorv = Sheets(tname).Range("G22").Interior.Color
    
    Sheets("Results").Cells(resultr, resultc).Interior.Color = colorv
    
End If

Sheets("Results").Cells(resultr, resultc - 1).Formula = "='" & tname & "'!B6"

'Übernehmen auf Übersichtsseite'

tcurrencyc = tcurrencyc + 10

Sheets("Übersicht").Range("C" & tcurrencyc).Formula = "='" & tname & "'!N2"
Sheets("Übersicht").Range("D" & tcurrencyc).Formula = "='" & tname & "'!B2"
Sheets("Übersicht").Range("E" & tcurrencyc).Formula = "='" & tname & "'!B6"
Sheets("Übersicht").Range("F" & tcurrencyc).Formula = "='" & tname & "'!D10"

If Sheets(tname).Range("B2") = "Sicherung" Then

Sheets("Übersicht").Range("G" & tcurrencyc).Formula = "='" & tname & "'!D14"

ElseIf Sheets(tname).Range("B2") = "Stopp-Sicherung" Then

Sheets("Übersicht").Range("G" & tcurrencyc).Formula = "='" & tname & "'!D12"

End If

Sheets("Übersicht").Range("H" & tcurrencyc).Formula = "='" & tname & "'!I10"
Sheets("Übersicht").Range("I" & tcurrencyc).Formula = "='" & tname & "'!J21"
Sheets("Übersicht").Range("J" & tcurrencyc).Formula = "='" & tname & "'!E6"
Sheets("Übersicht").Range("J" & tcurrencyc).NumberFormat = tcurrencyt

If Sheets(tname).Range("B2") = "Sicherung" Then

Sheets("Übersicht").Range("K" & tcurrencyc).Formula = "='" & tname & "'!E14"

ElseIf Sheets(tname).Range("B2") = "Stopp-Sicherung" Then

Sheets("Übersicht").Range("K" & tcurrencyc).Formula = "='" & tname & "'!E12"

End If


Sheets("Übersicht").Range("L" & tcurrencyc).Formula = "='" & tname & "'!E6/'" & tname & "'!C21"

Sheets("Übersicht").Range("N" & tcurrencyc).Formula = "='" & tname & "'!L25"
Sheets("Übersicht").Range("O" & tcurrencyc).Formula = "='" & tname & "'!L21"


If Not Sheets("Hilfsdaten").Range("E" & tcurrencyc - 10) = "Akt. Net" Then

Sheets("Übersicht").Range("B" & tcurrencyc & ":S" & tcurrencyc).Interior.Color = colorv

End If







'Start-Inputs bereinigen'

Sheets("Hilfsdaten").Range("B5").Value = 1
Sheets("Hilfsdaten").Range("E5").Value = 1

Sheets("Start").Activate

Range("G20").MergeArea.ClearContents
Range("K20").MergeArea.ClearContents
Range("C22").MergeArea.ClearContents
Range("G22").MergeArea.ClearContents
Range("K22").MergeArea.ClearContents
Range("D24").ClearContents
Range("H24").ClearContents
Range("K24").MergeArea.ClearContents




End Sub

Is there a question? How is this `SumByColor` function used? You say the `New Transaction` script starts it but I cannot find it in your code. — Pᴇʜ, Oct 23 '20 at 07:16
The `SumByColor` function is used only on the results page only in cells directly. It's not in the code but when I execute the macro step for step in Debug-mode, it calls the function around 50 times after the new page is inserted. — mj_u, Oct 23 '20 at 07:18
Recalculate the sheet in the end? [Disable calculation](https://stackoverflow.com/a/16962844/11683) and enable it at the end? — GSerg, Oct 23 '20 at 07:21
Add a breakpoint at the beginning of the `SumByColor()` function, when it gets hit you can see the call stack in order to know which function calls it. — Dominique, Oct 23 '20 at 07:56
@GSerg that did the trick ! for some reason it only works when I re-enable calculation at the very end ( as opposed to before clearing cells on the input page for example ). Anybody have an idea as to why that happens ? Thank you very much for your suggestion. — mj_u, Oct 23 '20 at 08:28
Because formulas are always recalculated when any of their arguments changes? — GSerg, Oct 23 '20 at 11:07
Well I am aware of that, but it seems like the problem occurs even when I am changing something else that is not an argument of the formula.. but I might be overlooking something. Anyway, thanks a bunch. — mj_u, Oct 23 '20 at 11:09

VBA Macro calls function without code specifying it to

0 Answers0