Sleeper: Macro from Sub to Function question

[malik641]

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:

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!!!

[Zack Barresse]

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..

[Bob Phillips]

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

=NewHires(C2:c44)

[malik641]

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??

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..

[Zack Barresse]

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.

[malik641]

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

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

=NewHires(C2:c44)

[Zack Barresse]

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.

[malik641]

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!

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.

[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?

[malik641]

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

[xCav8r]

You lost me.

[malik641]

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

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?

[xCav8r]

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

[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

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.

[malik641]

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.

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

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.

[xCav8r]

So users can change comments now without problems?

[malik641]

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

So users can change comments now without problems?

[xCav8r]

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

[malik641]

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

[malik641]

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

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

And I placed the Function in a standard module as:

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??