Call Another Sub in The Same Sheet VBA

Question

At the First I tried to run my codes in VBA but It ended up error like "Procedure too large". So I tried to divide it into some subs and when i called another sub inside my main sub, It doesn't work. Before I devided into sum subs, my codes run so well.

I tried to call Private sub Calca1() inside Private Sub Commandbutton2_Click()

Here's my Code

Private Sub Calca1()
        If flag60 = 1 Then
        Sheets("KPIAgent").Cells(trow2 + 1, 1).Value = "Agnes"
        CS_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("R4:R65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 30
        CS_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("R4:R65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
        Sheets("KPIAgent").Cells(trow2 + 1, 3).Value = (CS_Yes + CS_No) / var60
        HT_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("T4:T65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 20
        HT_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("T4:T65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
        Sheets("KPIAgent").Cells(trow2 + 1, 4).Value = (HT_Yes + HT_No) / var60
        H_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("V4:V65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 40
        H_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("V4:V65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
        Sheets("KPIAgent").Cells(trow2 + 1, 5).Value = (H_Yes + H_No) / var60
        RP_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("X4:X65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 10
        RP_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("X4:X65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
        Sheets("KPIAgent").Cells(trow2 + 1, 6).Value = (RP_Yes + RP_No) / varreport60
        trow2 = trow2 + 1
        End If
End Sub

Private Sub CommandButton2_Click()
Sheets.Add.Name = "KPIAgent"
Sheets("KPIAgent").Activate
Sheets("KPIAgent").Cells(1, 1).Value = "Agent Name"
Sheets("KPIAgent").Cells(1, 2).Value = "AVG Score"
Sheets("KPIAgent").Cells(1, 3).Value = "AVG Common Sense Score"
Sheets("KPIAgent").Cells(1, 4).Value = "AVG Human Touch Score"
Sheets("KPIAgent").Cells(1, 5).Value = "AVG Helpful Score"
Sheets("KPIAgent").Cells(1, 6).Value = "AVG Reporting Score"
Sheets("KPIAgent").Cells(1, 7).Value = "Satisfaction - STP"
Sheets("KPIAgent").Cells(1, 8).Value = "Satisfaction - TP"
Sheets("KPIAgent").Cells(1, 9).Value = "Satisfaction - P"
Sheets("KPIAgent").Cells(1, 10).Value = "Satisfaction - SP"
    Sheets("KPIAgent").Columns("A:J").Select
    Selection.EntireColumn.AutoFit
    Sheets("KPIAgent").Range("A1:J1").Font.Bold = True
Dim i As Integer
Dim flag60 As Integer
Dim trow As Integer
Dim trow2 As Integer
Dim var60 As Integer
Dim varreport60 As Integer
var60 = Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("K4:K65536"), "Agnes")
varreport60 = Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("K4:K65536"), "Agnes", Sheets("RawData").Range("Q4:Q65536"), "Recording")
trow = Sheets("RawData").UsedRange.Rows.Count
trow2 = Sheets("KPIAgent").UsedRange.Rows.Count
For i = 4 To trow
        If Sheets("RawData").Cells(i, 11).Value = "Agnes" Then
        flag60 = 1
        End If
Next i
Dim CS_Yes As Integer
Dim CS_No As Integer
Dim CS As Integer
Dim HT_Yes As Integer
Dim HT_No As Integer
Dim HT As Integer
Dim H_Yes As Integer
Dim H_No As Integer
Dim H As Integer
Dim RP_Yes As Integer
Dim RP_No As Integer
Dim RP As Integer
Call Calca1
End Sub

Here my data bank (RawData)

Here my expected result in KPIAgent

Here the result from my problem Thankyou in Advanced!

@DirkReichel I tried your method, but it ended up with error "Method or data member not found". By the way why should use "Me."? I'm a newbie in VBA. — Shieryn, Feb 01 '16 at 08:47
Can you post your code rather than a picture of your code - can copy and paste in to Excel for testing then. — Darren Bartrup-Cook, Feb 01 '16 at 08:56
@DarrenBartrup-Cook I've already post my code. Check it out. :D — Shieryn, Feb 01 '16 at 09:00
What **exactly** do you mean by "it doesn't work"? What happens? — Rory, Feb 01 '16 at 09:08
@DirkReichel You can't use `Me.calcal` if the routine is marked private. — Rory, Feb 01 '16 at 09:10
You must pass all variables that needed in part2 from part1. now all variables in part2 are `0` or empty string `""`. — Fadi, Feb 01 '16 at 09:11
@Rory I mean that the code inside private sub Calca1() doesn't work, so the result didn't shown as my expected. The data didn't shown up. — Shieryn, Feb 01 '16 at 09:14
@Fadi Should i really to pass all of my variables? does it already work? — Shieryn, Feb 01 '16 at 09:14
You need to declare the variables `calca1` needs in that routine, and pass any that are calculated by the button click to `calca1` as arguments. — Rory, Feb 01 '16 at 09:16
@Rory What do you mean? i'm sorry , i don't understand with your technical language because i'm really a newbie for VB & stuffs. I think, it would be nice if you answer my question :D — Shieryn, Feb 01 '16 at 09:19

Darren Bartrup-Cook · Answer 1 · 2016-02-01T12:12:15.580

I've updated my answer after looking through your code and trying to figure out what you're after.
I've added code to get a unique list of agent names (note - this won't work in Excel 2003 or earlier - let me know if it fails on RemoveDuplicates)
The last block adds a COUNTIFS formula to your reporting sheet using the R1C1 style of referencing cells. Hopefully you'll be able to figure out how the other formula work from this.
R1C1 notation (could do with a link to a StackOverflow answer here): http://www.numeritas.co.uk/2013/09/the-%E2%80%98dark-art%E2%80%99-of-r1c1-notation/

I've added a part answer as I'm not sure what you're after. The top part will create your new sheet and give the headings.
rLastCell finds the last cell in column K on your Raw Data sheet. After that you start counting stuff - but on some you then multiply the result by 0 which will give you 0 - so I'm confused.

Private Sub CommandButton2_Click()

    Dim wrkSht As Worksheet
    Dim wrkShtTmp As Worksheet
    Dim rLastCell As Range
    Dim rLastKPICell As Range
    Dim var60 As Integer
    Dim varReport60 As Integer

    'Add the new sheet and give headings.
    Set wrkSht = ThisWorkbook.Worksheets.Add
    With wrkSht
        .Name = "KPIAgent"
        .Range("A1:J1") = Array("Agent Name", "AVG Score", "AVG Common Sense Score", _
            "AVG Human Touch Score", "AVG Helpful Score", "AVG Reporting Score", _
            "Satisfaction - STP", "Satisfaction - TP", "Satisfaction - P", _
            "Satisfaction - SP")

        With .Range("A1:J1")
            .EntireColumn.AutoFit
            .Font.Bold = True
        End With
    End With

    With ThisWorkbook.Worksheets("Raw Data")
        'Find last cell on raw data sheet.
        Set rLastCell = .Cells.Find("*", , , , , xlPrevious)

        If Not rLastCell Is Nothing Then
            'Get unique list of agent names and paste into KPIAgent sheet.
            Set wrkShtTmp = ThisWorkbook.Worksheets.Add
            .Range(.Cells(3, 11), .Cells(rLastCell.Row, 11)).Copy _
                Destination:=wrkShtTmp.Cells(1, 1)
            wrkShtTmp.Range(wrkShtTmp.Cells(1, 1), wrkShtTmp.Cells(rLastCell.Row, 1)) _
                .RemoveDuplicates Columns:=1, Header:=xlNo
            wrkShtTmp.Range(wrkShtTmp.Cells(1, 1), wrkShtTmp.Cells(rLastCell.Row, 1)).Copy _
                Destination:=wrkSht.Range("A2")
            Application.DisplayAlerts = False
            wrkShtTmp.Delete
            Application.DisplayAlerts = True
        End If
    End With

    'Add figures to the report.
    With wrkSht
        'Find last cell on KPI sheet.
        Set rLastKPICell = .Columns(1).Find("*", , , , , xlPrevious)
        'Add formula to column 3 (C) of reporting sheet.
        .Range(.Cells(2, 3), .Cells(rLastKPICell.Row, 3)).FormulaR1C1 = _
            "=COUNTIFS('Raw Data'!R2C11:R" & rLastCell.Row & "C11,RC1, " & _
                     "'Raw Data'!R2C14:R" & rLastCell.Row & "C14,""Recording""," & _
                     "'Raw Data'!R2C15:R" & rLastCell.Row & "C15,""Yes"")*30"
    End With
End Sub

How about my sub Calca1? do you suggest to combine all code in one sub? If i combine both, it ended up error like "Procedure too large". Because that error, I decided to devided my code into some subs. and i want the code run as the same order like the first condition(Combine all code) — Shieryn, Feb 01 '16 at 09:32
The data in Calca1 can be combined in your click event code - I've no idea why you're getting 'Procedure too large' errors. It doesn't appear to be too large based on what I've read - http://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel. Lines like this are confusing me though: `(Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("R4:R65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0`. If you count something and then multiply by 0, surely the final answer will be 0? 10 x 0 = 0. — Darren Bartrup-Cook, Feb 01 '16 at 10:08

Dirk Reichel · Accepted Answer · 2016-02-01T10:26:07.533

To leave your code untouched (just to make it work):

Private Sub Calca1(trow2, var60, varreport60)
  Dim CS_Yes As Integer
  Dim CS_No As Integer
  Dim CS As Integer
  Dim HT_Yes As Integer
  Dim HT_No As Integer
  Dim HT As Integer
  Dim H_Yes As Integer
  Dim H_No As Integer
  Dim H As Integer
  Dim RP_Yes As Integer
  Dim RP_No As Integer
  Dim RP As Integer
  Sheets("KPIAgent").Cells(trow2 + 1, 1).Value = "Agnes"
  CS_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("R4:R65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 30
  CS_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("R4:R65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
  Sheets("KPIAgent").Cells(trow2 + 1, 3).Value = (CS_Yes + CS_No) / var60
  HT_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("T4:T65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 20
  HT_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("T4:T65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
  Sheets("KPIAgent").Cells(trow2 + 1, 4).Value = (HT_Yes + HT_No) / var60
  H_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("V4:V65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 40
  H_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("V4:V65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
  Sheets("KPIAgent").Cells(trow2 + 1, 5).Value = (H_Yes + H_No) / var60
  RP_Yes = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("X4:X65536"), "Yes", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 10
  RP_No = (Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("X4:X65536"), "No", Sheets("RawData").Range("K4:K65536"), "Agnes")) * 0
  Sheets("KPIAgent").Cells(trow2 + 1, 6).Value = (RP_Yes + RP_No) / varreport60
  trow2 = trow2 + 1
End Sub

Private Sub CommandButton2_Click()
  Sheets.Add.Name = "KPIAgent"
  Sheets("KPIAgent").Activate
  Sheets("KPIAgent").Cells(1, 1).Value = "Agent Name"
  Sheets("KPIAgent").Cells(1, 2).Value = "AVG Score"
  Sheets("KPIAgent").Cells(1, 3).Value = "AVG Common Sense Score"
  Sheets("KPIAgent").Cells(1, 4).Value = "AVG Human Touch Score"
  Sheets("KPIAgent").Cells(1, 5).Value = "AVG Helpful Score"
  Sheets("KPIAgent").Cells(1, 6).Value = "AVG Reporting Score"
  Sheets("KPIAgent").Cells(1, 7).Value = "Satisfaction - STP"
  Sheets("KPIAgent").Cells(1, 8).Value = "Satisfaction - TP"
  Sheets("KPIAgent").Cells(1, 9).Value = "Satisfaction - P"
  Sheets("KPIAgent").Cells(1, 10).Value = "Satisfaction - SP"
  Sheets("KPIAgent").Columns("A:J").Select
  Selection.EntireColumn.AutoFit
  Sheets("KPIAgent").Range("A1:J1").Font.Bold = True
  Dim i As Integer
  Dim flag60 As Integer
  Dim trow As Integer
  Dim trow2 As Integer
  Dim var60 As Integer
  Dim varreport60 As Integer
  var60 = Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("K4:K65536"), "Agnes")
  varreport60 = Application.WorksheetFunction.CountIfs(Sheets("RawData").Range("K4:K65536"), "Agnes", Sheets("RawData").Range("Q4:Q65536"), "Recording")
  trow = Sheets("RawData").UsedRange.Rows.Count
  trow2 = Sheets("KPIAgent").UsedRange.Rows.Count
  For i = 4 To trow
    If Sheets("RawData").Cells(i, 11).Value = "Agnes" Then
      flag60 = 1
    End If
  Next i
  If flag60 = 1 Then Calca1 trow2, var60, varreport60
End Sub

EDIT

This is like I would write the code:

Option Explicit

Private Sub CommandButton2_Click()
  Sheets.Add.Name = "KPIAgent"
  With Sheets("KPIAgent")
    .Activate
    With .Range("A1:J1")
      .Value = Array("Agent Name", "AVG Score", "AVG Common Sense Score", "AVG Human Touch Score", "AVG Helpful Score", "AVG Reporting Score", _
                     "Satisfaction - STP", "Satisfaction - TP", "Satisfaction - P", "Satisfaction - SP")
      .Font.Bold = True
      .EntireColumn.AutoFit
    End With
  End With
  With Sheets("RawData")
    If IsNumeric(Application.Match("Agnes", .Range("K4:K" & Sheets("RawData").UsedRange.Rows.Count).Value, 0)) Then
      Calca1 2, Application.WorksheetFunction.CountIf(.Range("K4:K65536"), "Agnes"), _
             Application.WorksheetFunction.CountIfs(.Range("K4:K65536"), "Agnes", .Range("Q4:Q65536"), "Recording")
    End If
  End With
End Sub

Private Sub Calca1(ByVal tRow As Long, ByVal var60 As Long, ByVal varreport60 As Long)
  With Sheets("RawData")
    Sheets("KPIAgent").Cells(tRow, 1).Value = "Agnes"
    Sheets("KPIAgent").Cells(tRow, 3).Value = (Application.WorksheetFunction.CountIfs(.Range("R4:R65536"), "Yes", .Range("K4:K65536"), "Agnes") * 30) / var60
    Sheets("KPIAgent").Cells(tRow, 4).Value = (Application.WorksheetFunction.CountIfs(.Range("T4:T65536"), "Yes", .Range("K4:K65536"), "Agnes") * 20) / var60
    Sheets("KPIAgent").Cells(tRow, 5).Value = (Application.WorksheetFunction.CountIfs(.Range("V4:V65536"), "Yes", .Range("K4:K65536"), "Agnes") * 40) / var60
    Sheets("KPIAgent").Cells(tRow, 6).Value = (Application.WorksheetFunction.CountIfs(.Range("X4:X65536"), "Yes", .Range("K4:K65536"), "Agnes") * 10) / varreport60
  End With
End Sub

headings as array -> just faster
.AutoFit after .Bold (self explaining)
skipped variables which will be used only 1 time
execute the second sub directly
search match with Match

Ya... Changed that to call the sub only if flag60 is 1 – Dirk Reichel Feb 01 '16 at 09:41 — Dirk Reichel, Feb 01 '16 at 09:41

score 1 · Answer 3 · answered Feb 01 '16 at 09:34

1

Declare all your Variable that you are using in the Calca1 sub as global variables as shown below. This way you needn't pass the variables and also changes happens to your variable will carry forward to Main sub. Hope it should help you without much changes to your code

Public CS_Yes As Integer

answered Feb 01 '16 at 09:34

Siva

1,129
1
8
13

Any luck after trying this? – Siva Feb 01 '16 at 09:56
just for you to know: [use long instead of integer](http://stackoverflow.com/a/26409520/5461205) (just for VBA) – Dirk Reichel Feb 01 '16 at 13:48

Call Another Sub in The Same Sheet VBA

3 Answers3