Find the percentages/count of values in a class vba

Question

I have this class with a number of collections

    'class properties
     public a as collection
     public b as collection
     public c as collection

a,b,c contain values for instance "a,a,a,a,b,b,b,b,c,c,c,c,c,c,c" and so does b
and c, and more possibly. Those values could be any alphabet we can assume. to get the percentages for each property for every class, I thought of doing it like this
(skeleton, coz am not sure how to exactly do it)

     for each class in collectionOfclassobjects
       for each ca in class.a
       'here am supposed to count all a's and b's divide by class.a.count but am not sure how to do this, I have an idea of adding the first item, and everytime it occurs i add it or add a count, when it's a create collection when its a, add it, when its b create new collection, and so on i have a collection of each value and i can easily print the count/total count and the name
       next ca
       for each cb in class.b

       next cb
       for each cc in class.c

       next cc
     next class

any suggestions are appreciated, am new and so far i have two noobie questions :) neither been solved not sure why :P

You can use a `Scripting.Dictionary` object for this type of task: if the "key" (a,b,c,etc) is not present then add it and set the corresponding "value" to 1. If the key has already been added then increment the "value" by 1. Eg: first answer here - http://stackoverflow.com/questions/9663200/excel-vba-to-count-and-print-distinct-values — Tim Williams, Aug 22 '13 at 22:30
@TimWilliams I am not sure he wants to remove duplicates. I think he wants the percentage of occurance of a character in each collection. — , Aug 23 '13 at 08:10
@mehow - I wasn't proposing "he" remove duplicates, just use the dictionary to provide a count of all distinct items. — Tim Williams, Aug 23 '13 at 14:59

score 0 · Accepted Answer · answered Aug 23 '13 at 08:17

0

Run the below procedure and wait for the message box to pop up in the end. It will show you the details

result

Sub Percentage()

    Dim a As Collection
    Set a = New Collection

    a.Add "a"
    a.Add "a"
    a.Add "a"
    a.Add "a"
    a.Add "b"
    a.Add "b"
    a.Add "b"
    a.Add "b"
    a.Add "c"
    a.Add "c"
    a.Add "c"
    a.Add "c"
    a.Add "c"
    a.Add "c"
    a.Add "c"

    Dim i As Long, cnt As Long
    cnt = 0
    ' checking the percentage of occurance of letter 'a'
    For i = 1 To a.Count
        If a.Item(i) = "a" Then cnt = cnt + 1
    Next i

    MsgBox "'a' occurs " & cnt & " times in collection A" & _
    vbCrLf & vbCrLf & " which is " & Format((cnt / a.Count), "0.0%") & " of the entire collection"

End Sub

answered Aug 23 '13 at 08:17

I keep getting confused with vba in excel, not sure if its better or easier to process data from collections after retrieving from a recordset or just directly recordset to worksheet and work on the data from the worksheet, I am starting to feel its easier with working on a worksheet and speed wise no difference, am i right ? – trackmeifUcan Aug 23 '13 at 09:14
I think it depends how comfortable you are with VBA. I prefer VBA over Spreadsheeet tools for any sort of automation. It takes me less time and effort to actually solve a problem using VBA. Usually, it is faster and more efficient to use Objects(arrays, dictionaries, collections) for any calculations then to manually work on a spreadsheet. Im not familiar with your case. There are a few basic questions you would need to put together for me to provide a good answer. Consider starting a new question, explain how you getting data and what you want to do with it and ask for an approach – Aug 23 '13 at 09:41

Find the percentages/count of values in a class vba

1 Answers1