Questions tagged [vba]

Visual Basic for Applications (VBA) is an event-driven, object-oriented programming language for writing macros, used for the entire Office suite as well as other applications. VBA is not equivalent to VB.NET, or VBS; if you're working in Visual Studio use [vb.net]. If your question is specifically about programming any MS Office application, also use the appropriate tag: [excel], [ms-access], [ms-word], [outlook], [visio], or [ms-project].

Visual Basic for Applications (VBA) is an event-driven programming language which was first introduced by Microsoft in 1993 to give Excel 5.0 a more robust object-oriented language for writing macros and automating the use of Excel. It was introduced to Access, PowerPoint and Word in Office 97. The language and its runtime quickly matured and began being licensed and used in products beyond Microsoft Office applications.

Tag usage

  • VBA is not equivalent to VB.NET; if you're working in Visual Studio use .
  • If your question is specifically about programming Excel, Access, Word, Outlook, PowerPoint or Project, also use the appropriate application tag: , , , , or .
  • While code written in VBA is typically called a 'macro', the tag should not be used for general VBA questions. Please see the wiki entry page for more information about correct usage.
  • If your code is specific to an Office application (Excel, Word, Outlook, PowerPoint, etc.) then tag it with one of the application-specific tags found below, in addition to this one.
  • It is expected that questions tagged should either contain VBA code or ask how to use specific VBA code.

Overview

VBA derives from the classic Visual Basic programming language (version 5.0 and later 6.0) and uses a subset of that language's functionality. It is, however, totally dependent on the host application interface for execution and cannot run outside that environment. Unlike classic VB, VBA cannot create independent executables or DLLs. Visual Basic Libraries for interaction with Windows and its services (such as Printers or the Clipboard) are not part of VBA.

VBA 6 was shipped in 1998 and includes a myriad of licensed hosts, among them: Office 2000 - 2007, AutoCAD and PI Processbook. VBA 6 code will run equally well on any host, though the underlying objects native to each host will vary. Though VBA is still built into Microsoft Office applications, its "parent", classic VB, ceased to be an integral of part of Microsoft's development platform when Visual Basic .NET shipped with the first version of the .NET framework in 2002.

VBA has been rarely updated since only including new features to allow it to remain compatible with x64 versions of Windows and Office. Its application-specific object models are regularly supplemented to support new functionality added to the host Office applications. VBA 7 was released in 2010 to address the new 64-bit version of Microsoft Office, which shipped in 2010. There are several important changes made to VBA 7 that make it different from VBA 6, namely compatibility with both 32 and 64-bit versions of Office. Applications using VBA 7 must address both backwards compatibility and 64-bit-safe issues.

In some Starter editions of Office applications, the VBA Editor (VBE) is not available, and programming in VBA cannot be done. VBA support can also be locked out (not installed) by network administrators as a security measure.

It was removed from Office for Mac 2008, however, Microsoft returned VBA to Office 2011. Microsoft has continually been questioned about whether or not VBA will be removed altogether from Office and has repeatedly replied "no", the main reason being a large amount of legacy code in use.

This still holds true, even after the introduction of Visual Studio Tools for Office (VSTO) that allowed the use of C# and VB.NET to automate MS Office, and the JavaScript APIs in Office 2013, although VBA is supported only in the desktop application versions of Office.

VBA inherits much of its syntax from the BASIC programming language, where language features tend to be explicitly expressed in words, e.g. If ... Then ... End If, Function ... End Function. It also has many object-oriented features, such as classes and interfaces, and even has some dynamic features in Variant and Object types. Below is a simple subroutine, which generates a message box and prints a message to the Immediate window:

Sub HelloWorld()
    ' This is a comment
    Dim message As String
    message = "Hello World"
    MsgBox message
    Debug.Print "I just made a message box that says """ & message & """!"
End Sub

Related Tags:

Frequently Asked Questions:

Beginner Resources:

References:

Additional Reading:

General VBA Information and History:

211298 questions
73
votes
16 answers

Determining whether an object is a member of a collection in VBA

How do I determine whether an object is a member of a collection in VBA? Specifically, I need to find out whether a table definition is a member of the TableDefs collection.
inglesp
  • 3,299
  • 9
  • 32
  • 30
72
votes
8 answers

Multi-threading in VBA

Does anybody here know how to get VBA to run multiple threads? I am using Excel.
KJ Saxena
  • 21,452
  • 24
  • 81
  • 109
72
votes
3 answers

Using a UDF in Excel to update the worksheet

Not really a question, but posting this for comments because I don't recall seeing this approach before. I was responding to a comment on a previous answer, and tried something I'd not attempted before: the result was interesting so I though I'd…
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
71
votes
9 answers

Running an Excel macro via Python?

I'm trying to run a macro via python but I'm not sure how to get it working... I've got the following code so far, but it's not working. import…
Ryflex
  • 5,559
  • 25
  • 79
  • 148
69
votes
9 answers

Is it possible to declare a public variable in vba and assign a default value?

I want to do this but it won't compile: Public MyVariable as Integer = 123 What's the best way of achieving this?
David
  • 2,101
  • 2
  • 32
  • 41
68
votes
6 answers

How to use the Implements in Excel VBA

I'm trying to implement some shapes for an engineering project and abstract it out for some common functions so that I can have a generalized program. What I'm trying to do is have an interface called cShape and have cRectangle and cCircle implement…
Zigu
  • 1,625
  • 4
  • 23
  • 33
68
votes
6 answers

Protecting cells in Excel but allow these to be modified by VBA script

I am using Excel where certain fields are allowed for user input and other cells are to be protected. I have used Tools Protect sheet, however after doing this I am not able to change the values in the VBA script. I need to restrict the sheet to…
Dheer
  • 3,926
  • 6
  • 34
  • 45
67
votes
8 answers

How to clear the entire array?

I have an array like this: Dim aFirstArray() As Variant How do I clear the entire array? What about a collection?
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
67
votes
7 answers

Save text file UTF-8 encoded with VBA

how can I write UTF-8 encoded strings to a textfile from vba, like Dim fnum As Integer fnum = FreeFile Open "myfile.txt" For Output As fnum Print #fnum, "special characters: äöüß" 'latin-1 or something by default Close fnum Is there some setting on…
Karsten W.
  • 17,826
  • 11
  • 69
  • 103
66
votes
14 answers

How can I find last row that contains data in a specific column?

How can I find the last row that contains data in a specific column and on a specific sheet?
Lipis
  • 21,388
  • 20
  • 94
  • 121
66
votes
6 answers

Removing the password from a VBA project

How can I programmatically remove a (known) password from an Excel VBA project? To be clear: I want to remove the password from the VBA Project, not the workbook or any worksheets.
Barry-Jon
  • 1,341
  • 4
  • 13
  • 16
66
votes
34 answers

Excel: the Incredible Shrinking and Expanding Controls

Occasionally, I'll happen across a spreadsheet which suffers from magic buttons or listboxes which get bigger or smaller over time. Nothing in the code is instructing this. Has anybody else experienced this joy?
Nick
  • 3,573
  • 12
  • 38
  • 43
66
votes
9 answers

Excel VBA code to copy a specific string to clipboard

I'm trying to add a button to a spreadsheet that when clicked will copy a specific URL to my clipboard. I had a bit of knowledge of Excel VBA but it's been a while and I'm struggling.
user1958738
  • 661
  • 1
  • 5
  • 3
65
votes
4 answers

Why cells(1,1) = 500 * 100 causes overflow but 50000*100 doesn't?

I just created a simple sub and it gives an overflow error. However, I don't see anything wrong with the code, and it is really weird since 50000*100 is much bigger than 500*100. sub add() 'This will cause an overflow error cells(1,1) = 500…
Yite Zeng
  • 629
  • 5
  • 7
65
votes
8 answers

Alternative IDE for VB6 and VBA

I've been spoiled by Visual Studio 2008 and Eclipse and have to do a little maintenance work on a Visual Basic 6.0 (VB6) application. Is there an alternative/ updated IDE for Visual Basic 6.0? A rewrite is not an option. I'm just fixing a couple of…
Omar Kooheji
  • 54,530
  • 68
  • 182
  • 238