VBA: Single line if statement with multiple actions

Question

I really should be able to google this, but I can't find what I wanna know about.

I want to check if a file exists. If not, a MessageBox should pop up and VBA should exit the sub.

If Dir("C:\file.txt", vbDirectory) = "" Then 
    MsgBox "File doesn't exist"
    Exit Sub
End If

It works, I just wanna know if you can you do this in a single line statement? Does VBA allow this when more than one thing is supposed to happen (like it is the case here)? This code doesn't work (syntax error):

If Dir("C:\file.txt", vbDirectory) = "" Then  MsgBox "File doesn't exist" And Exit Sub

As a personal opinion: I don't think code-readability is worth sacrificing for more compact code. So while it may be good here, I wouldn't recommend transforming your entire code into one-liners — Samuel Hulla, Apr 23 '19 at 13:12
I agree with @Rawrplus - it makes it very hard to read with no gain — Tom, Apr 23 '19 at 13:35
To further add to my comment - if you want to shorten your code for sake of readability, then **don't**. When you got 20k lines of codes, structurally divide your code into categories, create separate modules by logical structure and so on. But code should always be above all well comprehensive only then aesthitcally pleasing. Trust me, upon re-visiting the code, your colleagues, testers and your future self will thank you — Samuel Hulla, Apr 23 '19 at 13:37
i think the keyword you are looking for is "ternary operator" — aaaaa says reinstate Monica, Apr 23 '19 at 18:36
I would *definitely* not inline an early return inside a conditional statement. An exit is something that should be made to stand out, in any programming language. Use the four-line version. Anyone reading your code will be happy you did. — jpmc26, Apr 23 '19 at 19:05
@Rawrplus (and the rest of the commenters), thanks for the hint, I'll use the four line version then. But at least now I know how to properly use the one line if statement, too. Thx for the help — NoNameNo123, Apr 23 '19 at 19:27
Yeah, for a very fitting example as to why you should avoid it, just look at the comment section under my answer. As you can see, the single-line `If` syntax within itself is pretty confusing, combining that with `:` creates even further confusion. So yeah, I personally would steer clear from it altogether. Even in the [answer I linked regarding single-line If](https://stackoverflow.com/questions/52506801/what-am-i-missing-in-this-code-else-without-if-error/52506986#52506986) I strongly advise its usage!. But `:` is useful in obvious cases though *(eg. variable declaration and initialization)* — Samuel Hulla, Apr 23 '19 at 19:33
It looks to me that these two lines should be extracted into a function, like `exitWithMessage` — Alexander, Apr 23 '19 at 21:26
@Alexander `Exit Sub` means to exit the current procedure. It's like `return;` in C# or Java. You can't shift it to a another function. — jpmc26, Apr 23 '19 at 21:32

score 26 · Accepted Answer · answered Apr 23 '19 at 12:39

26

You absolutely can!

If Dir("C:\file.txt", vbDirectory) = "" Then  MsgBox "File doesn't exist" : Exit Sub

answered Apr 23 '19 at 12:39

Zamar

499
6
13

A single-line IF statement has the "end if" being understood. I tested a compound single line statement using the ":" character and it works correctly. Execution continues the "Then" segment until the end of the statement. Thanks! – Bruce Gavin Mar 08 '23 at 20:55

Samuel Hulla · Answer 2 · 2019-04-23T18:46:08.650

19

The If statement does already support single-line syntax.
In simple terms this means, we can either have:
1. ```
If {boolean-expression} Then
   {execution}
End If
```
2. ```
If {boolean-expression} Then {execution}
```
  - Note the lack of End If at the second option, as it's fully omitted in single-line syntax
  - Also keep in mind, the execution block can only contain a single statement

Then, further way of concatenating the code together is with the : which acts as a new line ↵ in the compiler.

This is fairly common practice in variable declaration:
```
Dim x As Integer: x = 42
```

Now, let's apply those steps together:

The original code

If Dir("C:\file.txt", vbDirectory) = "" Then 
   MsgBox "File doesn't exist"
   Exit Sub
End If

Applying the single-line If syntax

If Dir("C:\file.txt", vbDirectory) = "" Then MsgBox "File Doesn't Exist"
Exit Sub

Use the : symbol to put Exit Sub into our single-line If

If Dir("C:\file.txt", vbDirectory) = "" Then MsgBox "File Doesn't Exist" : Exit Sub

edited Apr 23 '19 at 18:46

answered Apr 23 '19 at 13:31

Samuel Hulla

6,617
7
36
70

1

This (particularly your step #2) suggests that the two forms are not equivalent (that is, the `Exit Sub` is not part of the branch), which I do not believe to be the case. Or, if it is the case, all of these answers are wrong because this is then not proper equivalent code to the multi-line version. – Lightness Races in Orbit Apr 23 '19 at 15:30
@LightnessRacesinOrbit You're right, in the sense that `step #2` is not equivalent, because in that step `Exit Sub` is executed regardless of the if expression. – Louis Apr 23 '19 at 15:46
@Louis It's an interesting language feature that simulates a lexical newline and causes a logical end-of-statement in one sense, without causing a logical end-of-statement in another! VB's blocks are weird. – Lightness Races in Orbit Apr 23 '19 at 15:48
@LightnessRacesinOrbit Yes. It's technically 2 lines merged artificially into one with `:`. Really it's about semantics of what you consider a single-line expression. It's a cosmetically merged expression (`:`) consisting of two single-line expression. The `:` serves no practical purpose, other than visually fusing two new-lines – Samuel Hulla Apr 23 '19 at 16:09
@Rawrplus But it's slightly more than that, because without the `:`, the two substatements would in fact be one [invalid] substatement. – Lightness Races in Orbit Apr 23 '19 at 16:23
I'm not sure I follow. Check the 2nd step out of 3 and try it in your editor. It's valid code. Differently written `If Then – Samuel Hulla Apr 23 '19 at 18:37
I'd recommend checking the question I linked to in my answer, as it's elaborated there in further detail – Samuel Hulla Apr 23 '19 at 18:49

Louis · Answer 3 · 2019-04-23T16:18:56.697

6

In VBA you can execute even more than two lines of code in one, just add : between one instruction and the other! This is perfectly legal:

If True Then MsgBox "True - Line 1": MsgBox "True - Line 2": Exit Sub

edited Apr 23 '19 at 16:18

answered Apr 23 '19 at 12:47

Louis

3,592
2
10
18

IOviSpot · Answer 4 · 2019-04-23T23:09:17.140

2

If Dir("C:\file.txt", vbDirectory) = "" Then : MsgBox "File doesn't exist" : End If

I do not have enough reputation to fix the answer above. : should be added between Then and your action block as well.

edited Apr 23 '19 at 23:09

answered Apr 23 '19 at 12:46

IOviSpot

358
3
19

8

Actually There's no need to put `:` after `Then`. The @Zamar answer is correct. – Louis Apr 23 '19 at 12:51
I am getting an error, though. `'End If' must be preceded by a matching 'If'` – IOviSpot Apr 23 '19 at 12:54
It must be for another reason, I just copied and executed his code and it works (so does mine)... – Louis Apr 23 '19 at 12:56
@Winand, oh I did not notice that. Fixed the post. Thanks! I did test it out with the End If today btw and so did now to double-check. No `:` still gives me an error... – IOviSpot Apr 23 '19 at 23:10
Oh apparently my eyes started working at last. This is VBA which has a **similar** syntax to VB.Net. I feel dumb now. haha – IOviSpot Apr 23 '19 at 23:12

score 1 · Answer 5 · answered Dec 07 '20 at 15:07

1

If you need Else particule, sintaxis would be:

If i Mod 2 <> 0 Then debug.print "Odd" Else: debug.print "Even"

answered Dec 07 '20 at 15:07

Ovichan

66
6

VBA: Single line if statement with multiple actions

5 Answers5

Linked