54
Sub btn1_Click()
Static value As Integer
value = value + 1
MsgBox value
End Sub

I swear when I was taking a VB.net course in college there was a shorter way to tell a variable to add '' to itself. Maybe x=+1. I am using Access now though instead of visual studio. When I try that within the VBE it removes the +. I even removed Option Explicit with no change

Assuming the answer will be no, there is no way to short-hand it & its just a peculiarly of VBA

braX
  • 9,702
  • 5
  • 16
  • 29
gregg
  • 852
  • 1
  • 9
  • 21

2 Answers2

81

Sadly there are no operation-assignment operators in VBA.

(Addition-assignment += are available in VB.Net)

Pointless workaround;

Sub Inc(ByRef i As Integer)
   i = i + 1  
End Sub
...
Static value As Integer
inc value
inc value
Davide Pizzolato
  • 624
  • 6
  • 21
Alex K.
  • 159,548
  • 29
  • 245
  • 267
  • 13
    +1 coming from a cpp background I remember asking myself this question the first day I picked up VBA and doing the exact same pointless workaround haha –  Nov 19 '13 at 15:57
  • 2
    One should note that `inc` should be a `Sub` as in this case the `Function`'s return value is discarded and not needed. Declaring it as a `Sub` instead makes this intention clear and doesn't leave it looking like a bug. – Inarion Apr 04 '19 at 14:34
12

If you want to call the incremented number directly in a function, this solution works bettter:

Function inc(ByRef data As Integer)
    data = data + 1
    inc = data
End Function

for example:

Wb.Worksheets(mySheet).Cells(myRow, inc(myCol))

If the function inc() returns no value, the above line will generate an error.

Andre
  • 24,160
  • 6
  • 28
  • 67
etfa
  • 161
  • 1
  • 5
  • 1
    Your implementation is a prefix increment ++myCol. It returns the incremented value. If you want postfix (myCol++) then the function should be looking like this: `Function inc(ByRef data As Integer) inc = data data = data + 1 End Function` – Andrey Klochkov Dec 08 '20 at 21:43