How to pass RANGE and include Worksheet into a Function ?

[emardell]

Hi all,

Have trawled around for ages and not found quite what I need, so was hoping that someone here could help me.

I have created a function to count comments in a sheet called "data", which works fine if I specify the sheet to "count in" within the function code...and then only send a range in the passing parameter.

What I really want is to be able to create a cell in a SUMMARY sheet...which says something like =CountComments(data!c1:c10) so it passes both the working sheet I want the counting to be in...as well as the range within it.

If I send the above into a RANGE datatype I get a silent error...function simply doesnt work, if I put back to =CountComments(c1:c10) and specify the data sheet specifically...its works fine again.

Code:

Function CountComments(rCommentRange As Range, rCode As String, rColumn As Integer) As Long

* rCommentRange is the range and ideally worksheet to check for comments in cells
* rCode is a code to check if the comment is allocated to a group I pass in
* rColumn is just to tell me which column the group code is held in for check above

The functionality of the code is fine, its just the Range that's the issue, but have included to show what I am using.

full function call would be =CountComments(data!C1:C10,"BBC",2)



I guess questions are:

1) Is Range the correct datatype if sending both sheet AND range into the function ?
2) If it is am I missing a means by which to select that sheet attribute out (not getting that far so don't think is right)
3) Requirement is a means of sending both sheet and range into a function and then using this as the driving sheet to access for the functions purpose.

This must be possible as the built in =sum function does this perfectly.

Would be very grateful if someone could help me !

Ed.

 

[ttdk1]

something like this?

Function countComments(rng As Range) As Integer
  Count = 0
  For Each c In rng
    If c.Value <> "" Then
      Count = Count + 1
    End If
  Next c
  countComments = Count
End Function

 

[emardell]

Thanks for fast response....but I have the counting part and its comments within a cell but...I am stuck on how to pass in the worksheet and range to work with ?

 

[pgc01]

Hi Ed
Welcome to the board

If you pass a range to a udf the worksheet goes with it.

Maybe if you post your code we can understand better your problem.

 

[emardell]

My first VBA function so any other changes welcomed !

Function CountComments(rCommentRange As Range, rCode As String, rColumn As Integer) As Long


' This function is expecting a range of cells (column) to denote which column we are checking for comments.
' We only want to find comments in the correct column, not anywhere
'
' The rCode is the code you want to count comments for. So this function will exclude any comments not marked with this code
' the rColumn is the column to check for the presence of the Code. This could be used to match other fields if the SHEET was a parameter
' Currently hard coded to data sheet


Dim cCmt As Comment
Dim lCount As Long
Dim cLen As Integer
Dim posColon As Integer
Dim currentRow As Integer


For Each cCmt In Worksheets("data").Comments 'was previously activesheet but needs to be the sheet you selected range from really...


If Not Intersect(Range(cCmt.Parent.Address), rCommentRange) Is Nothing Then 'Is the comment in a cell within range we defined ?


currentRow = cCmt.Parent.row ' find current row of the "comment just found" from its "parent" attribute
currentCode = Sheets("data").Cells(currentRow, rColumn).Value 'to check if correct code

If UCase(currentCode) = UCase(rCode) Then ' Check that row belongs to correct Code. Fewer comments than
'Scan to colon to remove any username and then see how much text is there

'Find the total length of the comment text
cLen = Len(cCmt.Shape.TextFrame.Characters.Text)
MsgBox ("Found Comment: " & cLen & " chrs, Row " & currentRow & " <" & cCmt.Text & ">")

'Find the position number of the Colon & LineFeed character combination which is the username of comments ...is there always one ?
posColon = InStr(1, cCmt.Shape.TextFrame.Characters.Text, ":" & vbLf)

If cLen - posColon > 4 Then ' Assumes that if >4 chrs of text its probably a valid comment, else its garbage, can be adjusted
lCount = lCount + 1
Else
MsgBox ("Empty/incomplete CMT at " & cCmt.Parent.Address)
End If
Else
MsgBox ("Found Comment but not our Code (" & UCase(currentCode) & ")")
End If
End If

Next cCmt
CountComments = lCount 'Return value to function to display on screen


End Function

 

[ttdk1]

this code shows how to pass along the sheet name

Function myworksheet(rng As Range) As String
  myworksheet = rng.Parent.Name
End Function

and this shows how to pass long the range

Function myworksheet(rng As Range) As String
  myworksheet = rng.Address
End Function

 

[emardell]

That's excellent...corrected my mistake using your range (doh) and that's got it.

Thanks so much for taking the time to help me out, its for the NHS so will be much appreciated by many !

Thanks

Ed.

 

[ttdk1]

You're welcome