By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,795 Members | 1,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,795 IT Pros & Developers. It's quick & easy.

What does mean := in VBA ?

P: 33
Hi.
Can anyone tell the purpose/meaning of ":=" which is used in VBA ? It sounds like a smiley, lol.

Example :

ActiveWorkbook.Sheets("Sheet1").Copy after:=ActiveWorkbook.Sheets ("Sheet1")

Thanks in advance....
Dec 13 '07 #1
Share this Question
Share on Google+
1 Reply


SammyB
Expert 100+
P: 807
In VBA, := allows you to specify named parameters. In your example, the Copy method has two optional parameters, Before and After, so you can also write your code as
ActiveWorkbook.Sheets("Sheet1").Copy (,ActiveWorkbook.Sheets ("Sheet1"))

So, using the := with named parameters allows you to see clearly which parameters were passed, plus you can specify them in any order. You can use this in your own code, see example below. In this silly example, NamedParametersTest(, , , , , , , , , , 22) and NamedParametersTest(k:=22) give the same result. HTH --Sam
Expand|Select|Wrap|Line Numbers
  1. Sub test()
  2.     MsgBox NamedParametersTest(, , , , , , , , , , 22)
  3.     MsgBox NamedParametersTest(k:=22)
  4. End Sub
  5. Function NamedParametersTest(Optional a, Optional b, Optional c, _
  6.                         Optional d, Optional e, Optional f, Optional g, _
  7.                         Optional h, Optional i, Optional j, Optional k, _
  8.                         Optional l, Optional m, Optional n, Optional p)
  9.     If Not IsMissing(a) Then
  10.         NamedParametersTest = "a=" & a
  11.     ElseIf Not IsMissing(b) Then
  12.         NamedParametersTest = "b=" & b
  13.     ElseIf Not IsMissing(c) Then
  14.         NamedParametersTest = "c=" & c
  15.     ElseIf Not IsMissing(d) Then
  16.         NamedParametersTest = "d=" & d
  17.     ElseIf Not IsMissing(e) Then
  18.         NamedParametersTest = "e=" & e
  19.     ElseIf Not IsMissing(f) Then
  20.         NamedParametersTest = "f=" & f
  21.     ElseIf Not IsMissing(g) Then
  22.         NamedParametersTest = "g=" & g
  23.     ElseIf Not IsMissing(h) Then
  24.         NamedParametersTest = "h=" & h
  25.     ElseIf Not IsMissing(i) Then
  26.         NamedParametersTest = "i=" & i
  27.     ElseIf Not IsMissing(j) Then
  28.         NamedParametersTest = "j=" & j
  29.     ElseIf Not IsMissing(k) Then
  30.         NamedParametersTest = "k=" & k
  31.     ElseIf Not IsMissing(l) Then
  32.         NamedParametersTest = "l=" & l
  33.     ElseIf Not IsMissing(m) Then
  34.         NamedParametersTest = "m=" & m
  35.     ElseIf Not IsMissing(n) Then
  36.         NamedParametersTest = "n=" & n
  37.     ElseIf Not IsMissing(p) Then
  38.         NamedParametersTest = "p=" & p
  39.     End If
  40. End Function
  41.  
Dec 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.