PDA

View Full Version : Sleeper: Macro from Sub to Function question



malik641
07-25-2005, 03:15 PM
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
07-25-2005, 04:03 PM
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
07-25-2005, 04:12 PM
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
07-25-2005, 04:17 PM
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
07-25-2005, 04:21 PM
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
07-25-2005, 04:22 PM
It's giving me a #VALUE! error...........
:banghead:




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
07-25-2005, 04:26 PM
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
07-25-2005, 04:52 PM
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
07-25-2005, 05:05 PM
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
07-25-2005, 05:23 PM
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
07-25-2005, 05:34 PM
You lost me.

malik641
07-25-2005, 05:40 PM
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
07-25-2005, 06:22 PM
Believe it or not, I didn't see it. I'm blind!!! (See it now though)

xCav8r
07-25-2005, 06:33 PM
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

malik641
07-26-2005, 06:17 PM
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 :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

xCav8r
07-26-2005, 06:35 PM
So users can change comments now without problems?

malik641
07-26-2005, 08:04 PM
no not yet...i'll figure that one out tomorrow at work :yes

So users can change comments now without problems?

xCav8r
07-26-2005, 08:20 PM
Okay, cool. I, like a handful of others, am here to help, so don't hesitate to ask. :)

malik641
07-26-2005, 08:37 PM
Awesome, thanks! :thumb I'm sure you'll be hearing from me tomorrow!:hi:

malik641
07-27-2005, 05:47 AM
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??

xCav8r
07-27-2005, 06:10 AM
It's because you're calling NewHIres without the parameter. It requires a range.



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

malik641
07-27-2005, 06:17 AM
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.

malik641
07-27-2005, 06:20 AM
Also getting a Type mismatch with a range type of "C2:C44"....????

xCav8r
07-27-2005, 06:53 AM
Whoops, my bad. You need to call it something like this:



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

Bob Phillips
07-27-2005, 07:16 AM
Whoops, my bad. You need to call it something like this:



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



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

malik641
07-27-2005, 07:51 AM
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).

Zack Barresse
07-27-2005, 08:02 AM
..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.

malik641
07-27-2005, 08:10 AM
Grrr......I'll think of SOMETHING. I mean, what's an engineer for? lol


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.

Zack Barresse
07-27-2005, 08:12 AM
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.

malik641
07-27-2005, 08:22 AM
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.


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.

geekgirlau
07-27-2005, 07:08 PM
Private Sub Worksheet_Change(ByVal Target As Range)
NewHires Target
End Sub

geekgirlau
07-27-2005, 07:11 PM
Oops - hit that quick reply button without noticing page 2!!

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

geekgirlau
07-27-2005, 07:25 PM
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.

malik641
07-28-2005, 06:50 AM
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

Zack Barresse
07-28-2005, 11:21 AM
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..