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)
Add a comment
|
5 Answers
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"
3 Comments
John M Gant
+1, I remember Microsoft suggesting during the buildup to .NET that VB6 developers start doing this to get ourselves ready for VB.NET.
Pete Alvin
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.
Paul Verschoor
In MS Access 2003, this one line assignment gives me an 'Out of Memory' error.
You can sort-of do that with objects, as in the following.
Dim w As New Widget
But not with strings or variants.
8 Comments
GoldBishop
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.John M Gant
@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.GoldBishop
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.John M Gant
The colon trick works with variant and string assignments. The
New keyword doesn't. That's all I'm saying.GoldBishop
@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.
|
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 7, 2016 at 20:56
Patrick Lepelletier
1,6522 gold badges19 silver badges24 bronze badges
1 Comment
ivan_pozdeev
This is for arguments, not local variables.
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
1 Comment
TSmith
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.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
