1

folks. I'm a programming newbie, trying to write a macro to extract some rows of data from multiple workbooks and compile them into a new workbook, then graph them. I have figured out how to loop through the source spreadsheets, and a few other things, but right now I'm stuck on a couple of places. (I'll ask my different questions in different threads for clarity.)

This question is about an error when using WorksheetFunction.CountA to get the number of a list of items I'm searching for. I want to know the number so that I can know when I have found all of my search strings. If I can't find them all, I want to inform the user and quit.

Following advice on this question, I wrote a snippet to test the CountA function. I get an "object required" error. I have reviewed several threads on that topic, but I'm still not getting it. Here is my code snippet:

Sub a_test_kpi_count()

Dim kpi_list_count As Integer

 Set kpi_list_count = Application.WorksheetFunction.CountA("kpi_list")

 MsgBox "There are " & kpi_list_count & "kpis in the list."


End Sub

When I run the code, the editor stops with either "kpi_list_count" OR "CountA" highlighted. Well, THAT'S helpful!

I should add that "kpi_list" is a named range on the worksheet where the macro lives. However, I get the same error when I specify the range in this manner:

Set kpi_list_count = Application.WorksheetFunction.CountA("K3:K7")

Ergo, I don't think the named range is my problem.

A nudge in the right direction would be much appreciated!

OR, feel free to call me an idiot and point out my obvious error! ;-)

EDIT:

Thanks, Ben.

This code is working:

Sub a_test_KPI_count()

Dim KPIListCount As Long

 KPIListCount = WorksheetFunction.CountA(Sheet1.Range("KPI_list"))

 MsgBox "There are " & KPIListCount & "KPIs in the list."

End Sub

Thank you! Oddly, it does NOT work when I use the sheet name, buttons. Are sheet names case sensitive? I ask because the editor insists on "Buttons" rather than, "buttons".

A couple more questions for my learning, if I may, about some of your tips:

3, long vs. integer. In this case, I'm counting a short list (<6) items. Is integer acceptable here, or would it be best practice to stick with Long?

  1. Camelcase vs snakecase. Noted. Is this simply a preference of more experienced programmers? Or is there a functional difference?

Your answers have all been of the kind I hope for. A fishing lesson, rather than a fish! Thank you!

JohnSquare
  • 11
  • 4
  • 1
    Since `kpi_list_count` is an integer, remove `Set`. – Brian M Stafford Jan 14 '20 at 14:16
  • Sheet names are not the same as sheet code names. To use the sheet name, you'd use `ThisWorkbook.Worksheets("Buttons")` instead of `Sheet1`. – BigBen Jan 14 '20 at 16:34
  • 1
    See [this question](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) for why `Long` is preferable to `Integer`. – BigBen Jan 14 '20 at 16:39
  • 1
    There's no "functional difference" in naming conventions, other than how (positively/negatively) it affects readability and maintainability. There are exactly 0 members in VBA standard libraries that use `snake_case`, that should be a good clue! Also if/when you start doing OOP and implement interfaces, you'll find that underscores in public member names will actually make your code uncompilable, because the underscore *does* have a special meaning in VBA. – Mathieu Guindon Jan 14 '20 at 17:06

1 Answers1

4

  1. Set is for Object variables - remove it.

  2. You need a Range call when specifying a named range.

    kpi_list_count = Application.WorksheetFunction.CountA(Range("kpi_list"))

  3. 99.9999% of the time you want a Long, not an Integer; see this question:

    Dim kpi_list_count as Long

  4. Best practice is to specify which sheet the Range is on (change as necessary):

    kpi_list_count = Application.WorksheetFunction.CountA(Sheet1.Range("kpi_list"))

  5. You can drop the Application if you want.

  6. Consider using camelCase instead of snake_case.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Thank you very much for an educational ansser. However, I'm still gettgng an "object required" error with both of these approaches. ' 'KPIListCount = Application.WorksheetFunction.CountA(Range("KPI_list")) KPIListCount = Application.WorksheetFunction.CountA(Buttons.Range("KPI_list"))' – JohnSquare Jan 14 '20 at 15:19
  • Can you edit your question with the revised code you're using? – BigBen Jan 14 '20 at 15:20
  • Should I do that in the original question? Still learning the protocol here. – JohnSquare Jan 14 '20 at 15:21
  • Yes that would be best. You can add it onto the end, as an "EDIT". – BigBen Jan 14 '20 at 15:22
  • Note - you can refer to the sheet by sheet name or by sheet code name. I'm using the latter. The code name can be found in the VBEditor. Most likely `Buttons` is not the actual code name of the sheet. – BigBen Jan 14 '20 at 15:25