Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
Hi -
I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
#1. I wouldn't be nervous about that technique--well, unless you're a nervous
person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
Dave -
Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
1. If I use "on error resume next", I never clear the error. But you'd want to
turn error handling back on. I only use that sequence between things I know could cause errors. As soon as I expect no errors, then I want excel/VBA to handle the error the way I want (goto 0 or goto errHandler). 2. If you don't handle the error in the subroutine, then the error handler will come from the calling sub: Option Explicit Sub test01() On Error Resume Next Call test02 Debug.Print Err.Number End Sub Sub test02() 'run it yourself and you'll get an error 'but call it from test01 and test01 will handle the error MsgBox 1 / 0 End Sub Neal Zimm wrote: Dave - Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
Dave -
Thanks so much, the fog is beginning to lift. I'll test your example soon. I've got some gray hair, and since I started to write this pretty big addin without any formal VBA training, I think I'm kinda doing with my code, (not relating to this thread) what the VBA error handler does. It stems from some modular assembler language coding I did in the dark ages. (circa early 1970's) What's below, really does not 'require' a response from you, unless you're so inclined. I use many levels of called subs. Each and every one has something like: call macname(arg,arg,arg,Status) When the sub that's running encounters an APPLICATION error, I set the Status string var to 1 of several standard values in my app. If it's a 'bad' one, it ripples up all the back to the sub that the User is going execute. e.g. call macnameA(arg,arg,arg,Status) if instr(status,'my code value') 0 then 'lots of various code here exit sub end if this repeats all the way 'to the top' by the mac that called macnameA, and so on. Probably should do some boning up on the error handler. sounds like I could have saved myself a ton of time. Again Thanks, Neal Z. -- Neal Z "Dave Peterson" wrote: 1. If I use "on error resume next", I never clear the error. But you'd want to turn error handling back on. I only use that sequence between things I know could cause errors. As soon as I expect no errors, then I want excel/VBA to handle the error the way I want (goto 0 or goto errHandler). 2. If you don't handle the error in the subroutine, then the error handler will come from the calling sub: Option Explicit Sub test01() On Error Resume Next Call test02 Debug.Print Err.Number End Sub Sub test02() 'run it yourself and you'll get an error 'but call it from test01 and test01 will handle the error MsgBox 1 / 0 End Sub Neal Zimm wrote: Dave - Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
Just a couple more alternatives...
You may want to consider using functions instead of Subroutines. Then you can pass a value (boolean) back to the calling procedure. dim OkToContinue as boolean oktocontinue = call macname(arg1, arg2, arg3) if oktocontinue then .... .... function macname(somearg1 as..., somearg2 as ...) as boolean if somejunkhere then macname = true else macname = false end if ==== Or you could declare a public variable that you can use that's visible to all the procedures you need. === If your sub/function can return lots of different results, you could change it to: function macname(somearg1 as..., somearg2 as ...) as Variant 'String??? Neal Zimm wrote: Dave - Thanks so much, the fog is beginning to lift. I'll test your example soon. I've got some gray hair, and since I started to write this pretty big addin without any formal VBA training, I think I'm kinda doing with my code, (not relating to this thread) what the VBA error handler does. It stems from some modular assembler language coding I did in the dark ages. (circa early 1970's) What's below, really does not 'require' a response from you, unless you're so inclined. I use many levels of called subs. Each and every one has something like: call macname(arg,arg,arg,Status) When the sub that's running encounters an APPLICATION error, I set the Status string var to 1 of several standard values in my app. If it's a 'bad' one, it ripples up all the back to the sub that the User is going execute. e.g. call macnameA(arg,arg,arg,Status) if instr(status,'my code value') 0 then 'lots of various code here exit sub end if this repeats all the way 'to the top' by the mac that called macnameA, and so on. Probably should do some boning up on the error handler. sounds like I could have saved myself a ton of time. Again Thanks, Neal Z. -- Neal Z "Dave Peterson" wrote: 1. If I use "on error resume next", I never clear the error. But you'd want to turn error handling back on. I only use that sequence between things I know could cause errors. As soon as I expect no errors, then I want excel/VBA to handle the error the way I want (goto 0 or goto errHandler). 2. If you don't handle the error in the subroutine, then the error handler will come from the calling sub: Option Explicit Sub test01() On Error Resume Next Call test02 Debug.Print Err.Number End Sub Sub test02() 'run it yourself and you'll get an error 'but call it from test01 and test01 will handle the error MsgBox 1 / 0 End Sub Neal Zimm wrote: Dave - Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
Dave -
Again thanks for the ideas. I do use functions as you suggest; they tend to be 'smaller' than my called subs with a narrower scope of processing. (just my preferance, I realize there's not much of a 'technical' difference re; the vba inside a function vs. sub.) e.g. If true = AccountEditF(ACnum, other vars) then .... functions as variants, somewhere I read in the MSo help that variant processing is slower than 'other' stuff, so I've tended to stay away from it. I've worked up a 'micro' timer routine to test how long diff versions of code take, and in truth, I've not tested the difference variant vs. not for function xxxxxxxxx As Variant. The functions I've written to date, have not required variant output, tho' I'll keep that thought in mind going forward. Last question this thread, I hope, How much difference is there, in general, between 'variant' processing and not-variant processing ? You've been a big help, Thanks and Regards, Neal Z. -- Neal Z "Dave Peterson" wrote: Just a couple more alternatives... You may want to consider using functions instead of Subroutines. Then you can pass a value (boolean) back to the calling procedure. dim OkToContinue as boolean oktocontinue = call macname(arg1, arg2, arg3) if oktocontinue then .... .... function macname(somearg1 as..., somearg2 as ...) as boolean if somejunkhere then macname = true else macname = false end if ==== Or you could declare a public variable that you can use that's visible to all the procedures you need. === If your sub/function can return lots of different results, you could change it to: function macname(somearg1 as..., somearg2 as ...) as Variant 'String??? Neal Zimm wrote: Dave - Thanks so much, the fog is beginning to lift. I'll test your example soon. I've got some gray hair, and since I started to write this pretty big addin without any formal VBA training, I think I'm kinda doing with my code, (not relating to this thread) what the VBA error handler does. It stems from some modular assembler language coding I did in the dark ages. (circa early 1970's) What's below, really does not 'require' a response from you, unless you're so inclined. I use many levels of called subs. Each and every one has something like: call macname(arg,arg,arg,Status) When the sub that's running encounters an APPLICATION error, I set the Status string var to 1 of several standard values in my app. If it's a 'bad' one, it ripples up all the back to the sub that the User is going execute. e.g. call macnameA(arg,arg,arg,Status) if instr(status,'my code value') 0 then 'lots of various code here exit sub end if this repeats all the way 'to the top' by the mac that called macnameA, and so on. Probably should do some boning up on the error handler. sounds like I could have saved myself a ton of time. Again Thanks, Neal Z. -- Neal Z "Dave Peterson" wrote: 1. If I use "on error resume next", I never clear the error. But you'd want to turn error handling back on. I only use that sequence between things I know could cause errors. As soon as I expect no errors, then I want excel/VBA to handle the error the way I want (goto 0 or goto errHandler). 2. If you don't handle the error in the subroutine, then the error handler will come from the calling sub: Option Explicit Sub test01() On Error Resume Next Call test02 Debug.Print Err.Number End Sub Sub test02() 'run it yourself and you'll get an error 'but call it from test01 and test01 will handle the error MsgBox 1 / 0 End Sub Neal Zimm wrote: Dave - Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
I've read the same sort of things that you've read. But if you want the
function to return a string, a number, an error, an object, or whatever, then using a Variant seems like the way to go. On the other hand, if you're declaring variables that you know will be used to count things, you'd use Long instead of Variant. If you're dealing with strings, then use String. Neal Zimm wrote: Dave - Again thanks for the ideas. I do use functions as you suggest; they tend to be 'smaller' than my called subs with a narrower scope of processing. (just my preferance, I realize there's not much of a 'technical' difference re; the vba inside a function vs. sub.) e.g. If true = AccountEditF(ACnum, other vars) then .... functions as variants, somewhere I read in the MSo help that variant processing is slower than 'other' stuff, so I've tended to stay away from it. I've worked up a 'micro' timer routine to test how long diff versions of code take, and in truth, I've not tested the difference variant vs. not for function xxxxxxxxx As Variant. The functions I've written to date, have not required variant output, tho' I'll keep that thought in mind going forward. Last question this thread, I hope, How much difference is there, in general, between 'variant' processing and not-variant processing ? You've been a big help, Thanks and Regards, Neal Z. -- Neal Z "Dave Peterson" wrote: Just a couple more alternatives... You may want to consider using functions instead of Subroutines. Then you can pass a value (boolean) back to the calling procedure. dim OkToContinue as boolean oktocontinue = call macname(arg1, arg2, arg3) if oktocontinue then .... .... function macname(somearg1 as..., somearg2 as ...) as boolean if somejunkhere then macname = true else macname = false end if ==== Or you could declare a public variable that you can use that's visible to all the procedures you need. === If your sub/function can return lots of different results, you could change it to: function macname(somearg1 as..., somearg2 as ...) as Variant 'String??? Neal Zimm wrote: Dave - Thanks so much, the fog is beginning to lift. I'll test your example soon. I've got some gray hair, and since I started to write this pretty big addin without any formal VBA training, I think I'm kinda doing with my code, (not relating to this thread) what the VBA error handler does. It stems from some modular assembler language coding I did in the dark ages. (circa early 1970's) What's below, really does not 'require' a response from you, unless you're so inclined. I use many levels of called subs. Each and every one has something like: call macname(arg,arg,arg,Status) When the sub that's running encounters an APPLICATION error, I set the Status string var to 1 of several standard values in my app. If it's a 'bad' one, it ripples up all the back to the sub that the User is going execute. e.g. call macnameA(arg,arg,arg,Status) if instr(status,'my code value') 0 then 'lots of various code here exit sub end if this repeats all the way 'to the top' by the mac that called macnameA, and so on. Probably should do some boning up on the error handler. sounds like I could have saved myself a ton of time. Again Thanks, Neal Z. -- Neal Z "Dave Peterson" wrote: 1. If I use "on error resume next", I never clear the error. But you'd want to turn error handling back on. I only use that sequence between things I know could cause errors. As soon as I expect no errors, then I want excel/VBA to handle the error the way I want (goto 0 or goto errHandler). 2. If you don't handle the error in the subroutine, then the error handler will come from the calling sub: Option Explicit Sub test01() On Error Resume Next Call test02 Debug.Print Err.Number End Sub Sub test02() 'run it yourself and you'll get an error 'but call it from test01 and test01 will handle the error MsgBox 1 / 0 End Sub Neal Zimm wrote: Dave - Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application inputbox, Range versus Cancel
D -
Good words, thanks, over and out for now. -- Neal Z "Dave Peterson" wrote: I've read the same sort of things that you've read. But if you want the function to return a string, a number, an error, an object, or whatever, then using a Variant seems like the way to go. On the other hand, if you're declaring variables that you know will be used to count things, you'd use Long instead of Variant. If you're dealing with strings, then use String. Neal Zimm wrote: Dave - Again thanks for the ideas. I do use functions as you suggest; they tend to be 'smaller' than my called subs with a narrower scope of processing. (just my preferance, I realize there's not much of a 'technical' difference re; the vba inside a function vs. sub.) e.g. If true = AccountEditF(ACnum, other vars) then .... functions as variants, somewhere I read in the MSo help that variant processing is slower than 'other' stuff, so I've tended to stay away from it. I've worked up a 'micro' timer routine to test how long diff versions of code take, and in truth, I've not tested the difference variant vs. not for function xxxxxxxxx As Variant. The functions I've written to date, have not required variant output, tho' I'll keep that thought in mind going forward. Last question this thread, I hope, How much difference is there, in general, between 'variant' processing and not-variant processing ? You've been a big help, Thanks and Regards, Neal Z. -- Neal Z "Dave Peterson" wrote: Just a couple more alternatives... You may want to consider using functions instead of Subroutines. Then you can pass a value (boolean) back to the calling procedure. dim OkToContinue as boolean oktocontinue = call macname(arg1, arg2, arg3) if oktocontinue then .... .... function macname(somearg1 as..., somearg2 as ...) as boolean if somejunkhere then macname = true else macname = false end if ==== Or you could declare a public variable that you can use that's visible to all the procedures you need. === If your sub/function can return lots of different results, you could change it to: function macname(somearg1 as..., somearg2 as ...) as Variant 'String??? Neal Zimm wrote: Dave - Thanks so much, the fog is beginning to lift. I'll test your example soon. I've got some gray hair, and since I started to write this pretty big addin without any formal VBA training, I think I'm kinda doing with my code, (not relating to this thread) what the VBA error handler does. It stems from some modular assembler language coding I did in the dark ages. (circa early 1970's) What's below, really does not 'require' a response from you, unless you're so inclined. I use many levels of called subs. Each and every one has something like: call macname(arg,arg,arg,Status) When the sub that's running encounters an APPLICATION error, I set the Status string var to 1 of several standard values in my app. If it's a 'bad' one, it ripples up all the back to the sub that the User is going execute. e.g. call macnameA(arg,arg,arg,Status) if instr(status,'my code value') 0 then 'lots of various code here exit sub end if this repeats all the way 'to the top' by the mac that called macnameA, and so on. Probably should do some boning up on the error handler. sounds like I could have saved myself a ton of time. Again Thanks, Neal Z. -- Neal Z "Dave Peterson" wrote: 1. If I use "on error resume next", I never clear the error. But you'd want to turn error handling back on. I only use that sequence between things I know could cause errors. As soon as I expect no errors, then I want excel/VBA to handle the error the way I want (goto 0 or goto errHandler). 2. If you don't handle the error in the subroutine, then the error handler will come from the calling sub: Option Explicit Sub test01() On Error Resume Next Call test02 Debug.Print Err.Number End Sub Sub test02() 'run it yourself and you'll get an error 'but call it from test01 and test01 will handle the error MsgBox 1 / 0 End Sub Neal Zimm wrote: Dave - Thanks. like it a lot BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL). Part of my concern is I know damn little about the error handler, and the whole concept of on error go to 0 leaves me numb. Two small follow ups, please 1) Whenever I use on error resume next, I always set the err back to zero. So, do I then still need to "goto 0" ? 2) If an err is NOT set back to 0, and the sub in which it happened is exited, what's the harm in NOT setting it back to zero? Thanks again, nervous Neal -- Neal Z "Dave Peterson" wrote: #1. I wouldn't be nervous about that technique--well, unless you're a nervous person to begin with <vbg. But it's not something that should add to your overall "nervousoscity" index. #2. Not that I know. But instead of checking for an error, I find this easier to write and understand: dim rng as range set rng = nothing on error resume next set rng = application.inputbox(Prompt:="...", type:=8) on error goto 0 if rng is nothing then 'user hit cancel else 'user supplied a range end if Neal Zimm wrote: Hi - I could not get the app input box to either accept a range, or let the user click cancel to exit the vba sub of which the code below is a part, until I used the on error 'method'. Tried different variations for dim'ing UserRng and diff values for app box Type:= values. All did not work. 1) Should i be nervous about using on error ..... method ? 2) What is another way, Not using On Error Resume Next to a) accept range or b) let user click cancel as a trigger to exit the vba sub? Thanks, Neal Z. Dim UserRng As Range Get_Sub: 'notes, Sub here means subscriber, ' Tb and Cr2 set up as constants for vbTab and vbCr ' to save a little typing. RMi... vars are numeric constants, too. On Error Resume Next Set UserRng = Application.InputBox _ (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _ & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _ & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _ Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8) If Err.Number < 0 Then Err.Number = 0 Exit Sub Else sCellAdr = UserRng.Address End If Return -- Neal Z -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test for Cancel in Inputbox | Excel Programming | |||
Copy a range with application.inputbox | Excel Programming | |||
How do I capture CANCEL on an Inputbox? | Excel Programming | |||
Clicking Cancel on an Inputbox | Excel Programming | |||
Inputbox and cancel button | Excel Programming |