Can I simultaneously declare and assign a variable in VBA?

Question

Can I convert the following declaration and assignment into one line:

Dim clientToTest As String
clientToTest = clientsToTest(i)

or

Dim clientString As Variant
clientString = Split(clientToTest)

score 286 · Accepted Answer · edited Oct 01 '19 at 05:12

286

There is no shorthand in VBA unfortunately, The closest you will get is a purely visual thing using the : continuation character if you want it on one line for readability;

Dim clientToTest As String:  clientToTest = clientsToTest(i)
Dim clientString As Variant: clientString = Split(clientToTest)

Hint (summary of other answers/comments): Works with objects too (Excel 2010):

Dim ws  As Worksheet: Set ws = ActiveWorkbook.Worksheets("Sheet1")
Dim ws2 As New Worksheet: ws2.Name = "test"

edited Oct 01 '19 at 05:12

Rahul

10,830
4
53
88

answered Jul 15 '10 at 13:55

Alex K.

171,639
30
264
288

14

+1, I remember Microsoft suggesting during the buildup to .NET that VB6 developers start doing this to get ourselves ready for VB.NET. – John M Gant Jul 15 '10 at 14:05
3

This is my biggest single complaint about VBA; I bet it would take a junior programmer only a day to add this shortcut to VBA if management cared to add it. – Pete Alvin Sep 15 '19 at 18:06

John M Gant · Answer 2 · 2010-07-15T14:18:23.893

26

You can sort-of do that with objects, as in the following.

Dim w As New Widget

But not with strings or variants.

edited Jul 15 '10 at 14:18

answered Jul 15 '10 at 13:50

John M Gant

18,970
18
64
82

This is not correct, as a whole. You can declare and initialize a variable on the same line with any data-type (value or object), by simply seperating the "action" with the semi-colo `:`. There are some limitations as you can not have multiple value declarations on the same line (ie `var1 = val1: var2 = val2`). It will bug out speradically and allow you to do this type of assignment sometimes but as a whole not suggested by this notation. – GoldBishop Oct 10 '12 at 14:17
2

@GoldBishop, yes, using the colon to combine multiple statements into a single line generally works (as Alex K. said). What I'm saying won't work with strings or variants (or probably with other primitives either) is the `Dim x As New T` syntax, which only works with objects. – John M Gant Oct 10 '12 at 16:56
yeah wont work on a Constructor Initialization line but it will work with Variant and String assignments. I use it all the time for Value Types and some Object Types. `dim str as String: str = "value"` and `dim str as Worksheet: set str = ActiveWorkbook.worksheets("Sheet1")` both work repeatedly. Although, if i do an Object instantiation `dim ws as New Worksheet: set ws = ActiveWorkbook.Worksheets("Sheet1")` would error out like a any other invalid operation in VBA. – GoldBishop Oct 10 '12 at 19:06
3

The colon trick works with variant and string assignments. The `New` keyword doesn't. That's all I'm saying. – John M Gant Oct 10 '12 at 19:56
2

@JohnMGrant might want to clarify your answer, as i read it, states: that you cant do same-line assignment with constructor initialization and string/variant value types. Might be a little confusing to some. – GoldBishop Oct 10 '12 at 22:14
@JohnMGant, @GoldBishop: `dim ws as New Worksheet: set ws = ActiveWorkbook.Worksheets("Sheet1")` works in Excel 2010! So `New` causes no issues there. (of course it does not make much sense to initialize something that will be overridden immediately again) – Andreas Covidiot Jan 16 '19 at 10:42
@AndreasDietrich good observation on both points. It does work, but no reason to do it that I can think of (haven't done VBA in 10+ years, though). The key takeaway from all this, I think, is that the colon simply allows you to combine two statements on one line. The colon is syntactically equivalent to the line feed in that usage. May help readability in a few cases, but I think it was generally frowned upon to code that way. – John M Gant Jan 17 '19 at 17:42
@AndreasDietrich...who ever said VBA made sense? :) I ran away from VB5/6 as soon as .Net came out cause of the crazy syntax patterns to perform relative simple actions for VBA/5/6 – GoldBishop Jan 23 '19 at 20:40

score 3 · Answer 3 · answered Mar 07 '16 at 20:56

3

in fact, you can, but not that way.

Sub MySub( Optional Byval Counter as Long=1 , Optional Byval Events as Boolean= True)

'code...

End Sub

And you can set the variables differently when calling the sub, or let them at their default values.

answered Mar 07 '16 at 20:56

Patrick Lepelletier

1,596
2
17
24

5

This is for arguments, not local variables. – ivan_pozdeev Jul 09 '17 at 03:28

score 3 · Answer 4 · edited Dec 09 '21 at 20:25

3

You can define and assign a value in one line, as shown below. I have given an example of two variables declared and assigned in a single line. If the data type of multiple variables are the same:

Dim recordStart, recordEnd As Integer: recordStart = 935: recordEnd = 946

edited Dec 09 '21 at 20:25

Remy Lebeau

555,201
31
458
770

answered Jul 17 '20 at 05:51

Arpan Saini

4,623
1
42
50

`recordStart` has type `Variant` here before the assignment, not `Integer`. Explicitly-specified data types only apply to one variable at a time in a `Dim` statement. – TSmith Oct 27 '22 at 02:24

score 1 · Answer 5 · answered Mar 19 '19 at 22:11

In some cases the whole need for declaring a variable can be avoided by using With statement.

For example,

    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    If fd.Show Then
        'use fd.SelectedItems(1)
    End If

this can be rewritten as

    With Application.FileDialog(msoFileDialogSaveAs)
      If .Show Then
        'use .SelectedItems(1)
      End If
    End With

Can I simultaneously declare and assign a variable in VBA?

5 Answers5

Linked