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)

Rodie

Answer 1

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"

Wirewove

+1, I remember Microsoft suggesting during the buildup to .NET that VB6 developers start doing this to get ourselves ready for VB.NET. – Sharasharai

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. – Antifreeze

In MS Access 2003, this one line assignment gives me an 'Out of Memory' error. – Ldopa

Answer 2

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

Dim w As New Widget

But not with strings or variants.

Sharasharai

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. – Vocabulary

@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. – Sharasharai

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. – Vocabulary

The colon trick works with variant and string assignments. The New keyword doesn't. That's all I'm saying. – Sharasharai

@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. – Vocabulary

@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) – Territoriality

@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. – Sharasharai

@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 – Vocabulary

Answer 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.

Wightman

This is for arguments, not local variables. – Beefeater

Answer 4

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

Amblyoscope

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. – Meeting

Answer 5

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

Unbeaten