Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert Sub routine to Function
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 |
#2
|
|||
|
|||
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 wrote in message oups.com... 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 |
#3
|
|||
|
|||
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 wrote in message oups.com... 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 |
#4
|
|||
|
|||
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. : ) |
#5
|
|||
|
|||
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" wrote in message oups.com... 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. : ) |
#6
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clock | Excel Worksheet Functions | |||
CONVERT Function Disappered in Excel | Excel Discussion (Misc queries) | |||
Improve Convert function in Excel | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
How do I use Excel's convert function for apothecary weight? | Excel Worksheet Functions |