Sleeper: Macro from Sub to Function question

I have a macro that needs to be changed to a function.
The macro will have to change to select the cells (relatively) of C2:C44 from cell C45(the cell that will call the function). Here is my macro:

Code:

---

Sub NewHires()
Dim totNew As Long
Dim numN As Long
Dim cmt As Comment
Dim sStrN As String
Dim ilocN As Long
Dim myKeyWordsN As Variant
Dim iCtrN As Long
Dim rng As Range
Set rng = Selection
myKeyWordsN = Array("new hire")
totNew = 0
For Each cmt In ActiveSheet.Comments
If Intersect(cmt.Parent, rng) Is Nothing Then
Else
For iCtrN = LBound(myKeyWordsN) To UBound(myKeyWordsN)
sStrN = cmt.Text
Do
ilocN = InStr(1, sStrN, myKeyWordsN(iCtrN), vbTextCompare)
If ilocN > 0 Then
If IsNumeric(Mid(sStrN, ilocN - 2, 2)) Then
numN = CLng(Mid(sStrN, ilocN - 2, 2))
totNew = numN + totNew
Else
MsgBox "Error on: " & cmt.Parent.Address(0, 0)
End If
Else
Exit Do
End If
sStrN = Mid(sStrN, ilocN + Len(myKeyWordsN(iCtrN)))
Loop
Next iCtrN
End If
Next cmt
ActiveCell.Offset(43, 0).Range("A1).Select
ActiveCell.FormulaR1C1 = totNew

---

The macro orignally checked the comments in a selected range and place a numeric value into cell C45 based on the criteria. If the comment in a cell had "# New Hires", it would take the # and add it to the next # in the next cell that had a comment with "new hire" in it. I want this to become a function so that it is MORE automatic than before.

Thanks in advance!!!

Hello Joseph,

A function cannot be called upon to act upon another cell unless called from a procedure in code.

What I'm understanding is, you have this routine that adds up numbers found in comments. You want to automate this instead of having to call it when you want the new value(s)? If this is the case, you could setup a change event to run the routine when you change certain Target cells..

Code:

---

Function NewHires(rng As Range)
    Dim totNew As Long
    Dim numN As Long
    Dim cmt As Comment
    Dim sStrN As String
    Dim ilocN As Long
    Dim myKeyWordsN As Variant
    Dim iCtrN As Long
myKeyWordsN = Array("new hire")
    totNew = 0
For Each cmt In ActiveSheet.Comments
        If Intersect(cmt.Parent, rng) Is Nothing Then
        Else
            For iCtrN = LBound(myKeyWordsN) To UBound(myKeyWordsN)
                sStrN = cmt.Text
                Do
                    ilocN = InStr(1, sStrN, myKeyWordsN(iCtrN), vbTextCompare)
                    If ilocN > 0 Then
                        If IsNumeric(Mid(sStrN, ilocN - 2, 2)) Then
                            numN = CLng(Mid(sStrN, ilocN - 2, 2))
                            totNew = numN + totNew
                        Else
                            MsgBox "Error on: " & cmt.Parent.Address(0, 0)
                        End If
                    Else
                        Exit Do
                    End If
                    sStrN = Mid(sStrN, ilocN + Len(myKeyWordsN(iCtrN)))
                Loop
            Next iCtrN
        End If
    Next cmt
NewHires = totNew
End Function

---

call like

Code:

---

=NewHires(C2:c44)

---

hey firefytr,
yes I do want to use a change event (like Sub Worksheet_Change(ByVal Target as Excel.Range) but I have two questions with that.
1. Will changing/adding/deleting comments count as a worksheet_change event (I assume so)
2. How do I make it so cell C45 could be =NewHires() in a way that when there is a worksheet change that function (in cell C45 and adjacent columns) would recheck the range of cells above it (specified in the code) for any changes and perform the procedure?????????

Or am I just at a dead end and I have to select the range and I guess just create a toolbar button??

Quote:

---

Originally Posted by firefytr

Hello Joseph,

A function cannot be called upon to act upon another cell unless called from a procedure in code.

What I'm understanding is, you have this routine that adds up numbers found in comments. You want to automate this instead of having to call it when you want the new value(s)? If this is the case, you could setup a change event to run the routine when you change certain Target cells..

---

Ah, you're wanting the Function in C45, not to affect any other cells? If so, try xld's proposition.

And the answer to your #1 is No, changing comments does not constitute as a "Change" in Excel.

It's giving me a #VALUE! error...........
:banghead:

Quote:

---

Originally Posted by xld

Code:

---

Function NewHires(rng As Range)
    Dim totNew As Long
    Dim numN As Long
    Dim cmt As Comment
    Dim sStrN As String
    Dim ilocN As Long
    Dim myKeyWordsN As Variant
    Dim iCtrN As Long
myKeyWordsN = Array("new hire")
    totNew = 0
For Each cmt In ActiveSheet.Comments
        If Intersect(cmt.Parent, rng) Is Nothing Then
        Else
            For iCtrN = LBound(myKeyWordsN) To UBound(myKeyWordsN)
                sStrN = cmt.Text
                Do
                    ilocN = InStr(1, sStrN, myKeyWordsN(iCtrN), vbTextCompare)
                    If ilocN > 0 Then
                        If IsNumeric(Mid(sStrN, ilocN - 2, 2)) Then
                            numN = CLng(Mid(sStrN, ilocN - 2, 2))
                            totNew = numN + totNew
                        Else
                            MsgBox "Error on: " & cmt.Parent.Address(0, 0)
                        End If
                    Else
                        Exit Do
                    End If
                    sStrN = Mid(sStrN, ilocN + Len(myKeyWordsN(iCtrN)))
                Loop
            Next iCtrN
        End If
    Next cmt
NewHires = totNew
End Function

---

call like

Code:

---

=NewHires(C2:c44)

---

---

Works for me .. except it does not handle more than one digit very well..

Make sure that the Function is in a Standard Module in the same workbook as well.

It works for me too now....I had a second NewHires() macro in a module that I was testing and forgot to delete it...
Now just to find a way to make it work when a change in the comments occurs.....???hmm???

Otherwise thanks a lot! Great job!

Quote:

---

Originally Posted by firefytr

Works for me .. except it does not handle more than one digit very well..

Make sure that the Function is in a Standard Module in the same workbook as well.

---

The comments should be ignored. I can't see how changes to them would have any effect. Can you paste the code or a sample workbook?

Sure...
And the best I can think of is the Private Sub Worksheet_Change(ByVal Target As Range) event...but I want to avoid that, just incase the user DOESN'T change anything EXCEPT the comments

You lost me.

What about? I pasted a sample workbook....Something in the workbook you're confused about?

Quote:

---

Originally Posted by xCav8r

The comments should be ignored. I can't see how changes to them would have any effect. Can you paste the code or a sample workbook?

---

Believe it or not, I didn't see it. I'm blind!!! (See it now though)

I didn't read the whole thread till now. My bad. I thought you meant code comments. That'll teach me.

I don't see any procedures in your sheet module that should be in the sheet module. The only procedure that should be in the sheet module (the change event procedure) is in a standard mod. Am I missing something? If not, you might find this helpful: http://www.cpearson.com/excel/codemods.htm :motz2:

After your procedures are put into the right modules, I think your problem is that when a worksheet is changed, you need to supply a range as a parameter to NewHires. :)

PS. I have to say that I personally find your variable naming convention difficult. I don't want to prescribe a naming method, but I would recommend commenting them in the future (wherever they are declared) when seeking help. I started with C, so I use Hungarian, and your prefixes confused me. Writing code for yourself is one thing, but writing code that will be shared is another thing altogether. The easier it's read, the easier it's understood, and the quicker your problems will be solved. Unsolicited advice. I know. :whip

Hey thanks xCav8r for the help and the link. I'm a novice at VBA and am currently reading a HUGE book about it right now and any additional info is well appreciated, so thanks!!
I have the module running okay for now...I'll see if I can make it more efficient though.

Quote:

---

Originally Posted by xCav8r

I didn't read the whole thread till now. My bad. I thought you meant code comments. That'll teach me.

I don't see any procedures in your sheet module that should be in the sheet module. The only procedure that should be in the sheet module (the change event procedure) is in a standard mod. Am I missing something? If not, you might find this helpful: http://www.cpearson.com/excel/codemods.htm :motz2:

After your procedures are put into the right modules, I think your problem is that when a worksheet is changed, you need to supply a range as a parameter to NewHires. :)

PS. I have to say that I personally find your variable naming convention difficult. I don't want to prescribe a naming method, but I would recommend commenting them in the future (wherever they are declared) when seeking help. I started with C, so I use Hungarian, and your prefixes confused me. Writing code for yourself is one thing, but writing code that will be shared is another thing altogether. The easier it's read, the easier it's understood, and the quicker your problems will be solved. Unsolicited advice. I know. :whip

---

So users can change comments now without problems?

no not yet...i'll figure that one out tomorrow at work :yes

Quote:

---

Originally Posted by xCav8r

So users can change comments now without problems?

---

Okay, cool. I, like a handful of others, am here to help, so don't hesitate to ask. :)

Awesome, thanks! :thumb I'm sure you'll be hearing from me tomorrow!:hi:

Tell me if this is right.
I placed the event procedure in the active sheet module as:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
NewHires
End Sub

---

And I placed the Function in a standard module as:

Code:

---

Function NewHires(rng As Range)
Code....
End Function

---

Now when I make a sheet change, I get an error: "Arguement Not Optional" and then it highlights NewHires and makes 'Private Sub Worksheet_Change(ByVal Target As Range)' background color yellow. How come? I thought I placed these in the correct modules??

It's because you're calling NewHIres without the parameter. It requires a range.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
NewHires "A1:C20"
End Sub

---

So what if I want the range to be the same as what the function calls for (i.e. rng As Range)??

A new column is added each month and I wouldn't want anyone to have to change the code everymonth...And I don't want to make the range to the end if it is unefficient.

Also getting a Type mismatch with a range type of "C2:C44"....????

Whoops, my bad. You need to call it something like this:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Worksheets("Finding text in Comments").Range("C2:C44")
NewHires myRange
End Sub

---

Quote:

---

Originally Posted by xCav8r

Whoops, my bad. You need to call it something like this:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Worksheets("Finding text in Comments").Range("C2:C44")
NewHires myRange
End Sub

---

---

or more probably

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
    Set myRange = Worksheets("Finding text in Comments").Range( _
        Cells(1, Target.Column), Cells(1, Target.Column).End(xlDown))
    NewHires myRange
End Sub

---

Those both work pretty good...xCav8r's is a little too specific for the range. And xld's works only when a change has been made to a cell value in the column that has the changed comment. I think I'm approaching this the wrong way.

I want to make this so that when a comment is changed, and ONLY a comment is changed, to recalculate the formula in that column immediately. I thought I could just get away with the event procedures that you guys gave me (which I REALLY appreciate, BTW). But if the user solely changes an amount of new hires in a particular column (each column is a month, btw) and never changes anything in that column (cell values), then it will never show up. And I know I could just use a different event procedure such as Workbook_Activate or something, but I kind of wanted to make it instant.

So I'll try to figure out something if I can...keep me posted if you guys think of anything. Otherwise thanks for ALL the help, well appreciated :thumb!!
And if I think of anything, I'll post it. It may take a bit, though, seeing that I'm such a novice at VBA (and some formulas as well).

Quote:

---

Originally Posted by malik641

..I want to make this so that when a comment is changed, and ONLY a comment is changed, to recalculate the formula in that column immediately..

---

No can do hoss. Excel does not recognize this as an 'event'. Kinda stinks, I know. Same with cell formatting (and THAT'S been a pain in my bum..). If you want instant-type results, you'll need to have this information in a column/cell.

Grrr......I'll think of SOMETHING. I mean, what's an engineer for? lol

Quote:

---

Originally Posted by firefytr

No can do hoss. Excel does not recognize this as an 'event'. Kinda stinks, I know. Same with cell formatting (and THAT'S been a pain in my bum..). If you want instant-type results, you'll need to have this information in a column/cell.

---

LOL!

You want my honest opinion? If you're trying to calculate something, a comment is not the best place for it. I think I would take the time to restructure it as so you can enter this data in an adjacent column.

Trust me, I've thought that over plenty of times....but my range of columns that I have to change is from C to CQ....yeah, no thanks. And I guess I COULD change it only from this month on...but I kinda want to keep things consistent, so that's why I'm stuck doing this junk the way I am. The way I have it setup right now is to select the cells to evaluate, then run a macro (I made it as easy as I could by making a custom button to press). But I wanted to make it THAT much more efficient with this new function feature....
Maybe this is what they'll have to deal with...We'll see.

Quote:

---

Originally Posted by firefytr

LOL!

You want my honest opinion? If you're trying to calculate something, a comment is not the best place for it. I think I would take the time to restructure it as so you can enter this data in an adjacent column.

---

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    NewHires Target
End Sub

---

Oops - hit that quick reply button without noticing page 2!!

... okay, I get it ... more coffee ... :omg2:

Have you thought of showing the data in a summary sheet instead? You could display a total # new hires for each month rather than using the comments.

Well the reason for the comments is because there is not just new hires. There are terminations (Involuntary, and Voluntary) and there are transfers and it's just a dumb layout in my opinion. I would have set this up WAY differently. But this was made like 2 years ago...I just started working at my job for like 3 months, so I have to work with what I have. And I really don't want to do an overhaul.

I spoke with the main user and he said it would be fine to make the sheet calculate all the values in each column upon a workbook_open event. So I'll be toying with that today. He also wants me to make a dialog box to pop-up when you press "Insert Comment" either from the right-click or the Insert->Insert Comment menu. In the dialog box he wants me to set it up sort of like the way conditional format's dialog box is set-up....I've never done a dialog box before...but I don't have a deadline, so I can take my time. I put the details in another thread "Userform pop-up when insterting a comment" if you want to check it out.

So I'm going to have fun with that today!:thumb

If you created your own menu/toolbar/button/whatever, you could create your own userform and then make any events you want to off that. Otherwise, there is no event attached to inserting/deleting/adjusting comments. If you're still trying to go that way, it's a dead end.

I'd suggest the custom menu and userform method. That would give you full control over everything the user picks from there. And I'd still suggest having the new hires data go into actual cells..