How to debug my simple Excel VBA macro?

Question

I'm just trying to check the latest Date cell, which is in Column A, against the current date. If the difference is 30 days, I write a new row.

When I execute, it says I can't call CheckAttendance on Sheet("Occurences"). But why?

    Option Explicit

    Public LastCell As Long
    Public today As Date


    Function CheckAttendance()

        Dim DaysSinceOcc As Integer

        'returns last occupied row
        LastCell = Cells(Rows.Count, 1).End(xlUp).Row

        'gets current date
        today = Date

        'subtracts last cell in specified column from today's date.
        DaysSinceOcc = today - Cells(LastCell, 1).Value

        'writes what I want written in the cells I want it written in.        
        If DaysSinceOcc > 29 Then
            Cells(LastCell, 1).Offset(1, 1) = "winback"
            Cells(LastCell, 1).Offset(1, 2) = -0.5
            Cells(LastCell, 1).Offset(1, 4) = "Earned back 0.5 pts for 30 days perfect attendance (AutoGenerated)"
            Cells(LastCell, 1).Offset(1, 5) = "AUTO"
            Cells(LastCell, 1).Offset(1, 0) = today
        Else

        End If


    End Function

    Sub Attendance()

        Sheets("Occurences").CheckAttendance
        'yes Occurences is suppose to be spelled like that (don't ask)

    End Sub

edit: there are probably multiple problems with this. I fixed a lot of things, but then got stuck.

in addition to @Slai's comment: 1) it should be a sub, not function; 2) you need to provide a proper reference to Cells in your code (explicitly reference workbook and worksheet, otherwise your code might be unstable) — RADO
– RADO, Commented Jun 7, 2017 at 23:22
@RADO Thanks for your comment. My understanding of your comment compared to the A.S.H. answer is that I have two different options: make the worksheet a parameter, which allows the sub to be called on different worksheets, OR I can make it more specific, so that it only works on my Occurences sheet. Am I understanding that correctly, or are those options not mutually exclusive like I think they are? — Christopher Graves
– Christopher Graves, Commented Jun 8, 2017 at 12:18
A.S.H. gave you a solid advice. I was referring to the fact that your code contained unqualified reference to Cells(), which is dangerous because it essentially means: ActiveBook.ActiveSheet.Cells(). If your code starts running in non-active book, or your sheet is no longer active, it will either crash or lead to unexpected results. If you look at A.S.H code, he preceds every Cell with ws. (worksheet object). Re: parameter or hard-coded sheet name - the choice is dictated by your goals. If you need to do the same operation on many sheets, use parameter. Otherwise, just use the sheet name. — RADO
– RADO, Commented Jun 8, 2017 at 13:22

A.S.H · Accepted Answer · 2017-06-07 23:52:19Z

2

It seems that you want to parametrize your CheckAttendance routine so that it can be called on different worksheets. To do that, make it a Sub that takes a worksheet as parameter. Moreover

qualify you cells and ranges in the code
dim your variables and use Option Explicit

Option Explicit

Sub CheckAttendance(ws As Worksheet)
    Dim DaysSinceOcc As Long, lastRow As Long, today As Long
    lastRow = ws.Cells(ws.Rows.count, 1).End(xlUp).row

    'gets current date
    today = Date

    'subtracts last cell in specified column from today's date.
    DaysSinceOcc = today - ws.Cells(lastRow, 1).Value2

    'writes what I want written in the cells I want it written in.
    If DaysSinceOcc > 29 Then
        ws.Cells(lastRow + 1, 1) = today
        ws.Cells(lastRow + 1, 2) = "winback"
        ws.Cells(lastRow + 1, 3) = -0.5
        ws.Cells(lastRow + 1, 5) = "Earned back 0.5 pts for 30 days perfect attendance (AutoGenerated)"
        ws.Cells(lastRow + 1, 6) = "AUTO"
    End If
End Sub

Sub Attendance()
    CheckAttendance Sheets("Occurences") ' <-- this is how you call it on any worksheet
End Sub

edited Jun 7, 2017 at 23:52

answered Jun 7, 2017 at 23:46

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christopher Graves Over a year ago

Thanks a bunch. With the Variables--Dim vs. Public: I was under the impression that you should use Public if you want to be able to use the variable in other macros in the same book. In my mind VBA-Public = python-global. Is that correct? What is the rationale for making all the variables local?

A.S.H Over a year ago

@ChristopherGraves we have no public variables in this snippet, only locals. Public/global variables are declared outside routine and aim to be accessible by many routines. By the principles of data hiding (aka encapsulation), they are to avoid unless really necessary. Why? because they tend to make the code more difficult to track and maintain.

Christopher Graves Over a year ago

Thank you for your help!

Collectives™ on Stack Overflow

How to debug my simple Excel VBA macro?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related