| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
There must be a better way to code this
I am no VBA pro but I do write several semi complicated macro.
Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check If error2 < 0 Then 'error 1 & 2 found MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Else 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." Exit Sub End If Else If error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue." Exit Sub Else ' no error Exit Sub End If End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
Hi
The exit sub statements are not needed, as the code will terminate after all. Look at this: Sub aaa() If error1 < 0 And error2 < 0 Then 'error 1 & 2 found 'error 1 check MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." ElseIf error1 < 0 Then 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." ElseIf error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue." End If End Sub Regards, Per On 21 Jan., 22:01, Frank wrote: I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check * * If error2 < 0 Then *'error 1 & 2 found * * * * MsgBox error1 & " data input are missing and highlighted in yellow. " _ * * * * & error2 & " entries are a missmatch and highlighted in blue." * * * * Exit Sub * * * * Else 'error 1 only found * * * * MsgBox error1 & " data input are missing and highlighted in yellow." * * * * Exit Sub * * End If * * Else * * If error2 < 0 Then *'error 2 only found * * * * MsgBox error2 & " entries entries are a missmatch and highlighted in blue." * * * * Exit Sub * * * * Else ' no error * * * * Exit Sub * * End If End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
On Jan 21, 1:56*pm, Per Jessen wrote:
Hi The exit sub statements are not needed, as the code will terminate after all. Look at this: Sub aaa() If error1 < 0 And error2 < 0 Then 'error 1 & 2 found 'error 1 check * * MsgBox error1 & " data input are missing and highlighted in yellow. " _ * * & error2 & " entries are a missmatch and highlighted in blue." ElseIf error1 < 0 Then 'error 1 only found * * MsgBox error1 & " data input are missing and highlighted in yellow." ElseIf error2 < 0 Then *'error 2 only found * * MsgBox error2 & " entries entries are a missmatch and highlighted in blue." End If End Sub Regards, Per On 21 Jan., 22:01, Frank wrote: I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check * * If error2 < 0 Then *'error 1 & 2 found * * * * MsgBox error1 & " data input are missing and highlighted in yellow. " _ * * * * & error2 & " entries are a missmatch and highlighted in blue." * * * * Exit Sub * * * * Else 'error 1 only found * * * * MsgBox error1 & " data input are missing and highlighted in yellow." * * * * Exit Sub * * End If * * Else * * If error2 < 0 Then *'error 2 only found * * * * MsgBox error2 & " entries entries are a missmatch and highlighted in blue." * * * * Exit Sub * * * * Else ' no error * * * * Exit Sub * * End If End If- Hide quoted text - - Show quoted text - Thanks. Not familiar with elseif but it looks very simple! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
Dim msg As String
msg = "" If error1 < 0 Then 'error 1 check msg = error1 & " data input are missing and highlighted in yellow." End If If error2 < 0 Then 'error 1 & 2 found msg = error2 & " entries entries are a missmatch and highlighted in blue." End If If msg < "" Then MsgBox msg HTH Bob "Frank" wrote in message ... I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check If error2 < 0 Then 'error 1 & 2 found MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Else 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." Exit Sub End If Else If error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue." Exit Sub Else ' no error Exit Sub End If End If |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
Untested but only slightly restructured
If error1 < 0 and error2 < 0 Then 'error 1 and error 2 detected MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Elseif error1 < 0 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." Elseif error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue. End If There is no need for Exit Sub if this is all there is since whenever an IF condition is satisfied control passes to the the statements below that if and then to endif without testing other conditions If you sub has more code after this, then how about adding after the above If error1 < 0 or error2 < 0 then exit sub "Frank" wrote in message ... I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check If error2 < 0 Then 'error 1 & 2 found MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Else 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." Exit Sub End If Else If error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue." Exit Sub Else ' no error Exit Sub End If End If |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
You could try this. Its not that much shorter, but its a little easier to
read. Hope this helps! If so, let me know, click "YES" below. Select Case True Case error1 < 0 And error2 < 0 MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Case error1 < 0 MsgBox error1 & " data input are missing and highlighted in yellow." Exit Sub Case error2 < 0 MsgBox error2 & " entries entries are a missmatch and highlighted in blue." Exit Sub Case Else Exit Sub End Select -- Cheers, Ryan "Frank" wrote: I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check If error2 < 0 Then 'error 1 & 2 found MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Else 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." Exit Sub End If Else If error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue." Exit Sub Else ' no error Exit Sub End If End If . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
I tend to do that sort of thing this way:
msg="" If error1 < 0 Then msg = error1 & _ " data input are missing and highlighted in yellow. " If error2 < 0 Then if msg < "" then msg = msg & " " msg = msg & error2 & " entries are a missmatch and highlighted in blue." end if If msg < "" Then MsgBox msg Exit Sub End If --- "Frank" wrote: Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
There must be a better way to code this
Try doing it like this...
Dim Msg As String ........ ........ Msg = "" If Error1 < 0 Then Msg = Error1 & " data input are missing and highlighted in yellow." If Error2 < 0 Then Msg = Trim(Msg & " " & Error2 & " entries are a mismatch and highlighted in blue.") If Len(Msg) 0 Then MsgBox Msg -- Rick (MVP - Excel) "Frank" wrote in message ... I am no VBA pro but I do write several semi complicated macro. Here is that I wrote to check for two errors using if then else statements. It works perfectly but I wonder I it could be written more professionally. If error1 < 0 Then 'error 1 check If error2 < 0 Then 'error 1 & 2 found MsgBox error1 & " data input are missing and highlighted in yellow. " _ & error2 & " entries are a missmatch and highlighted in blue." Exit Sub Else 'error 1 only found MsgBox error1 & " data input are missing and highlighted in yellow." Exit Sub End If Else If error2 < 0 Then 'error 2 only found MsgBox error2 & " entries entries are a missmatch and highlighted in blue." Exit Sub Else ' no error Exit Sub End If End If |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating my code? | Excel Programming | |||
| Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
| Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
| Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
![Frank[_28_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
