Convert Sub routine to Function

[jtfreed]

Hi all


I have the following VB code


Sub ErrorCheck2()
'Error checking for when H1=1
'Error message to display in cell B50
Dim d As Range
If Range("h1") = 1 Then
Range("b50") = ""
For Each d In Range("F19:F38")
If d.Text <> "" And d.Text > "119999" Then
Range("B50") = "ERROR"
Exit Sub
End If
Next
End If
End Sub


I wanted to make this a UDF - but it is not visible when I display the
list of UDF's. This is all new to me, I am learning as I go, and from
what I can see - I need to tell Excel somehow that this is a function
by putting FUNCTION in the code. But I am not sure what syntax I need
- and whatever I am doing is not correct. What is confusing me is
what, if any parameters have to be defined in the parentheses.


Can someone give me some guidance..(I guess the short question is - I
want to be able to place this macro in a cell as a formula so it will
run automatically). Thanks

 

[Niek Otten]

A function can not change anything in a worksheet. It can only replace its
call to a result. So Converting your Sub to a function will not help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Doug Glancy]

User-defined functions can't change the spreadsheet, so you'll have to alter
your objective. Take a look here for some on functions versus subs:

http://www.cpearson.com/excel/differen.htm

hth,

Doug

 

[JTF]

Thanks Doug. I had already looked at that.

I thought that a user defined function could place a value in a cell,
which is essentially what I am trying to do (place an error message in
a cell). But I must have misunderstood.

The next thing I tried to do is call the macro from a cell using the
Worksheet Change Event. That's not working either. I am at the point
now where I have just decided this isn't going to work at all, and I
can't justify spending much more time on it with my very limited
knowledge of vb code (after working the better part of 2 days on it).
I'll just have to figure something else out. There has got to be a way
to do this and have it run automatically.

Thank for everyone's help. These groups are invaluable and it's been a
learning experience and that is never a bad thing. : )

 

[Bob Phillips]

This works

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$H$1" Then
If Target.Value = 1 Then
Range("b50") = ""
For Each d In Me.Range("F19:F38")
If d.Value <> "" And d.Value > 119999 Then
Me.Range("B50") = "ERROR"
End If
Next d
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JTF]

Bob

A million thank you's!!!!! I was trying the worksheet change event,
but going about it in the wrong way! I was way off course, and I thank
you for setting me straight and helping me out!

This works perfectly! Again, thanks!