Talk:Visual Basic for Applications

Object-Oriented Programming

1. Discussion of how VBA, WSH, VB script all are similar and may share technology 2. Links to MS documentation on VBA (found on the MS Office developer page)

Indeed it would be good to list specific differences for example between VBA and VB. For example, "VBA doesn't support type casting. It isn't a fully object-oriented language. You can neither inherit classes in your own custom class definitions, nor can you get your objects to behave polymorphically (e.g., cast them as an instance of their base class). VB6 doesn't support inheritance/polymorphism either, but VB.NET and C# do." (http://p2p.wrox.com/topic.asp?TOPIC_ID=26628) ~TP —Preceding unsigned comment added by 91.84.98.138 (talk) 08:37, 19 August 2008 (UTC)

It's more complicated than that. VBA is not considered a fully OO language, but it does support type casting and polymorphic functions can be written. Further, the language above, addressing the reader with "you", is unencyclopedic. That's why I reverted the edit which inserted the text above into the article. -- Michael Bednarek (talk) 07:32, 25 January 2013 (UTC)

VBW (the original version of VBA, effectively its predecessor, only available on MS Word [hence VBW - Visual Basic for Winword]) ought to be mentioned on here. -- 134.225.165.163 (talk) 02:53, 16 February 2010 (UTC)

Could you please insert a correct explanation of the relationship between VBA and object-oriented languages, then? It seems important. --Jules.LT (talk) 13:20, 8 February 2013 (UTC)

There's not all that much that I can add. There are quite a few characteristics of OO Programming in VBA, like classes and some kind of constructors (but not conversion constructors), destructors, and instance methods. OTOH, VBA doesn't support inheritance. All this is my WP:OR; if anything like this should make it into the article, it needs reliable sources better than a quote from a web forum. -- Michael Bednarek (talk) 07:18, 10 February 2013 (UTC)

Recent Update

VBA was recently updated to version 7.1 in Office 2013. wwwEagle (talk) 08:17, 21 January 2013 (UTC).

The VBA article should include that the JavaScript Office Add in provides a small subset of VBA capability and importantly requires the user's Office application to access a cloud hosted service to download the custom written add-in component. VBA is shipped inside of the Office document and does not have this external point of failure and does not require a cloud subscription service.

The marketing material from Microsoft does not mention that this Office AddIn is a downgrade in functionality and an increase in the support cost for Office applications. — Preceding unsigned comment added by 2020‎ 2600:1700:d591:5f10:780e:467e:6c55:e1bf (talk) 02:40, 12 November (UTC)

"Hundreds of applications"?

Office, Autocad, Intellicad... anything else? Mathcad and Visual Basic Script.

It looks like the reference to "Hundreds of applications" was removed some time ago. Btw - Visual Basic Script is an independent implementation of Visual Basic based syntax. It is not an application of VBA. Ralphy 09:45, 7 February 2006 (UTC)

Examples

User:Snakeyes060280 - why have you added 'DAO' library qualifications to the object names? Ralphy 16:32, 6 February 2006 (UTC)

Where is there a VBA language reference/specificaation?

I've been looking for an online reference to the VBA language, but have not been able to find anything about the language itself (as opposed to APIs for the various MS Office applications for instance).

Does anyone know where there is a VBA language specification? Erland Lewin 13:40, 15 March 2006 (UTC)

Try Here: http://msdn2.microsoft.com/en-us/isv/aa905357.aspx Oorang (talk) 22:45, 13 February 2008 (UTC)

Edit by 84.146.195.216

I started to remove errors in the edit by 84.146.195.216 on 22:09, 19 September 2006 [1] however I find almost everything in that edit is incorrect or misleading. Furthermore there is a much better discussion of the strengths and weakness of the Visual Basic language in the visual basic article. This is why I am rolling back to the edit before 84.146.195.216. There some of the problems with the edit I am removing:

VBA's entities can be roughly divided in to classes: Firstly, there are the usual elements forming a programming language: data types, variables, control- and loop-struktures. This part of VBA is intended not to be application specific and is used to express the program logic. Secondly, there is an API which allows VBA to interact with it's host application. This API is specific to the relative application.

The APIs described in this section are not part of the VBA language or runtime environment. They are OLE Automation APIs which can be called from VBA and other languages with COM support. This also simplification of VBA also ignores that VBA is more than a language, it is also an integrated development environment. All these parts of VBA are discussed in the Description section of the article.

VBA itself is an [[Interpreter (computing)|interpreted] language. Precompilation is only used for syntax-check and to build tables for the variables used. Therefore VBA cannot be used to create standalone executables. Interpretation also causes the high memory requirement and low execution speed compaired with compiled programs.

VBA is an interpreted language and it can't be used to create standalone executables, I am keeping this in the article. However, this section does not say what VBA is being compared to. I some cases VBA may be slower or require more memory than alternatives and in some cases the opposite is the case. Since VBA runs in process with the application it is automating VBA programs often run faster that out of process compiled programs. Many compiled programs also load large libraries used for COM support.

VBA is a descentant of Microsoft's Visual Basic and therefor has it's roots in the Beginner's All-purpose Symbolic Instruction Code. Like BASIC, VBA also targets end users, being unexperienced programmers or complete beginners in the field of programming. It aims to support many of the simple language constructs, that were used in early BASIC Languages. Since BASIC since the early days lacked standardisation, there were lots of different dialects over the time, so VBA usually supports several syntactial constructs for the same programming goal. This can lead to a confusing variety of syntax and logical breaches: Examples:

• ) The keyword "Let" might be neglected when asigning predefined types. But when asigning user defined types another keyword, "Set", is mandatory.
• ) Variables are implicitely defined, except a directive, "Option Explicit", is used at the beginning of a script.
• ) Subroutines are cathegorized in "functions" (returning a value) and sub (not returning a value). Functions must be called using brackets arount their arguments. Eigher their returnvalue as to be assigned to a variable, or the keyword "call" must be used, while subs must not be used in an assignment. When calling subs, the keyword "call" is optional but determines, whether brackets have to be used or not. All of the above syntax variants exist with named or unnamed arguments, additionally all arguments might be optional, taking on a default value proposed by the programmer when no value is assigned in the call.

I am keeping some of this section since it is correct that VBA is a decedent of BASIC and design decisions in the language favor inexperienced programmers. VBA's attempt to hide the difference between value and reference types does lead to confusion and the ability to omit variable deceleration can lead to errors however these are common in many languages. The description of how Let and Set are used is incorrect. Let is used with value types and set with object references, not with predefined types and user defined types. The use of a keyword to specify whether a subroutine returns a value or not may actually lead to less confusion than other approaches such as using a void return type. The discussion about the Call keyword and the use of practices is completely incorrect in VBA 6.

Despite of its close resemplance to many old BASIC dialects, VBA is not compatible to any of them, except Microsoft Visual Basic: Here the source-code of VBA Modules can be directly imported. Compatibility ends with Visual Basic Verison 6, VBA ist not compatible to VB.NET. Although being along with MS-Office a widespread product, VBA is proprietary to Microsoft and forms no Open standard.

This I am keeping except for the removal of the reference to Microsoft Office as VBA's use within those products has no relationship to whether it is an open standard or not.

VBA still supports flow control using the keyword "goto" (often called Spaghetti code in the jargon) but is mostly designed for Procedural programming.

VBA supports procedural and object orientated programming, event driven programming and possible other paradigms. Goto can lead to Spaghetti code but they are not the same thing. I will link to article on VBA where there is a more in depth discussion of the strengths and weakness of the Visual Basic language

VBA can depict classes and thereby supports data encapsulation, but lacks support for the other [[Object-oriented programming|object-orientated] techniques, like class-attributes and inheritance. So most of the [[Design pattern (computer science)|design patterns] of object orientated programming cannot be realized and VBA cannot be called an object orientated language.

VBA supports class-attributes and interface inheritance and can be used to implement many popular design patterns. I don't know if it can implement most design because there is no list of all design patterens.

Interaction with the host application is usually well documented. One of the most important elements is the "application" object, which integrates almost all automatizeable functions. The API is usually specific to an application and incompatible to all others. So VBA-code written for MS-Office API can not be used in [[OpenOffice]} using the StarBasic-API for example, although StarBasic and VBA are very resemblant considering the language. Most software products (Autodesk AutoCAD / Microsoft Office / Adobe Illustrator) provide an API in form of an 'Object Model' to the Visual Basic Environment allowing the user to create anything from small macros that perform repetitive tasks to extensive programs that add functionality to the host program.

The article on OLE Automation provides a good discussion on how VBA can interact with the host application's APIs. I wall callout more clearly at the start of the article.

Macros can be attached to a menu, button a keyboard shortcut or an event in the application like the opening of the document. Also user-defined functions for excel-worksheets can be provided.

Security Issues VBA is designed without any security features in the language, like for example the sandbox that java appletts run in. Any function of the application or even of the whole operating system, that is accessible to the user running an document containing VBA-macros can be (ab)used by a VBA-makro. Even persons of low programming skills can thus easily write a so called macro virus causing damage to almost unlimited extent. To ensure basic safety for the system and to protect data from espionage or destruction, several measures are necessary:

• ) An external Virus-Scanner with macro-blocking function must be used, to close the security holes left open by VBA design.
• ) Macro-capable Documents from unknown or unreliable sources must not be opened.
• ) Macro-capable applications must not be run as system administrator.
• ) The signature features provided with newer VBA-Versions should be used to verify the origin of a macro-containing document.

I am leaving this section in with a cleanup tag since it contains a number of errors and typos.

Patleahy 15:59, 20 September 2006 (UTC)

Second Person Adress

In the section addressing security, this article uses second person address. I know generally, this type of address has no place in formal writing, but I'm unfamiliar with the etiquette of this type of article. Should someone reword the offending passage?--Amanaplanacanalpanama 23:06, 7 November 2006 (UTC)

Yes, you should reword it. --Patleahy 15:47, 9 November 2006 (UTC)

Interpreted vs. Compiled

The article says VBA is a interpreted language. The compiled language article lists VBA and the interpreted language article does not. Which is it?Jcdietz03 15:14, 7 May 2007 (UTC)

It's definitely interpreted. If it's compiled then things like CurrentDB or Selection would never work. What happens when VBA codes are run is that the program will run the code from the top (which happens sometimes when badly written vba codes would work in some situations and not in others depends on what programs are open and which files are open). The biggest giveaway is that you don't need to compile first to run VBA macros. :) Leigao84 16:17, 10 May 2007 (UTC)

It is compiled to a p-code machine. It hasn't been interpreted since Access Basic/Word Basic. It takes a text version in BASIC, and compiles it to a seperate copy, which it runs in the VB Virtual Machine, MSVBVM60.DLL, and before that MSVBVM50.DLL. Corruption of the compiled copy is common problem: google the Access coding news groups. Depending on your environment, you may have Compile On Demand or Compile On Save. CurrentDB is a function that does a lookup, including refreshing a collection. I don't know what Selection does, but I do know that you can do File Open in compiled languages: basic input and output is not a mark of an interpreted language.

VBA, which is/was a microsoft product for compilation into a host product, includes an on-demand compiler option. That may look a lot like an interpreter when called from the VBA immediate window, but it's not the same thing.

Obviously, this article needs a lot of work, but it was a labour of love from some one: I'm not going to trash it today. 150.101.166.15 07:23, 31 August 2007 (UTC)

for what it's worth, the MS article about Access 97 currently used as a reference (December 2007), http://support.microsoft.com/kb/109382, is no longer completely accurate: text is not interpreted as you type. Instead, text is syntax checked by an independent syntax checker in the IDE as you type. The syntax checker shares code with the visual studio code completion feature, not the VBA compiler. 218.214.18.240 (talk) 06:05, 29 December 2007 (UTC)

Advert?

This article was tagged advert today. I must say I don't really agree. Anyone in for some discussion? Classical geographer 22:03, 14 August 2007 (UTC)

Concur. I've removed it until the editor can explain his concerns. Kuru ^talk 23:25, 14 August 2007 (UTC)

UserForms aren't "part of the language" are they?

Aren't UserForms an OLE/ActiveX object that can be used with VBA?150.101.166.15 (talk) 04:25, 13 December 2007 (UTC)

Fair use rationale for Image:VBA logo.jpg

Image:VBA logo.jpg is being used on this article. I notice the image page specifies that the image is being used under fair use but there is no explanation or rationale as to why its use in this Wikipedia article constitutes fair use. In addition to the boilerplate fair use template, you must also write out on the image description page a specific explanation or rationale for why using this image in each article is consistent with fair use.

Please go to the image description page and edit it to include a fair use rationale. Using one of the templates at Wikipedia:Fair use rationale guideline is an easy way to insure that your image is in compliance with Wikipedia policy, but remember that you must complete the template. Do not simply insert a blank template on an image page.

If there is other fair use media, consider checking that you have specified the fair use rationale on the other images used on this page. Note that any fair use images lacking such an explanation can be deleted one week after being tagged, as described on criteria for speedy deletion. If you have any questions please ask them at the Media copyright questions page. Thank you.

BetacommandBot (talk) 06:56, 21 January 2008 (UTC)

Pointers

There is a section here that says "VBA is functionally rich and extremely flexible but it does have some important limitations, including limited support for function pointers which are used as callback functions in the Windows API. It has the ability to use (but not create) (ActiveX/COM) DLLs, and later versions add support for class modules."

VBA has an AddressOf operator which returns a pointer to the function for use with API calls.Oorang (talk) 01:19, 28 January 2008 (UTC)

Additional Note: You can get string, object, and variable pointers via the VarPtr, StrPtr, and ObjPtr functions built into the VBA library.Oorang (talk) 19:29, 12 May 2008 (UTC)

References

Infobox

Add info box. A second pair of eyes would not be amiss Oorang (talk) 01:46, 28 January 2008 (UTC)

a virtual machine hosted inside the container application

Arguably false, but perhaps just a simplification. It uses the VB6 dll,s if they are in memory, or an equivilant set of runtime dlls if the vb6 dlls are not in memory. That is, the virtual machine is hosted by Windows, and the program always runs the same way a non-native mode vb6 program runs. Id change it if I could think of a way to write it that wasnt even more confusing and misleading, but I cant, because of course vba is hosted inside the container application. —Preceding unsigned comment added by 218.214.18.240 (talk) 07:59, 9 February 2008 (UTC)

Fair use rationale for Image:Vba office 2003.PNG

Image:Vba office 2003.PNG is being used on this article. I notice the image page specifies that the image is being used under fair use but there is no explanation or rationale as to why its use in this Wikipedia article constitutes fair use. In addition to the boilerplate fair use template, you must also write out on the image description page a specific explanation or rationale for why using this image in each article is consistent with fair use.

Please go to the image description page and edit it to include a fair use rationale. Using one of the templates at Wikipedia:Fair use rationale guideline is an easy way to insure that your image is in compliance with Wikipedia policy, but remember that you must complete the template. Do not simply insert a blank template on an image page.

If there is other fair use media, consider checking that you have specified the fair use rationale on the other images used on this page. Note that any fair use images lacking such an explanation can be deleted one week after being tagged, as described on criteria for speedy deletion. If you have any questions please ask them at the Media copyright questions page. Thank you.

BetacommandBot (talk) 02:33, 12 February 2008 (UTC)

VBA vs. VB Express 2008

VBA is based on the VB6 engine VB Express 2008 is based on VB.net. They are not synonymous. Undid edit by Benrmac129 —Preceding unsigned comment added by Oorang (talk • contribs) 17:57, 10 September 2008 (UTC)

VBA links

VBA msdn home page: http://msdn.microsoft.com/en-us/isv/bb190538.aspx embedded VBA replacement product: http://www.winwrap.com/web/basic/default.asp

Jargon-laden/buzzword-speak introduction

This article's introduction suffers from jargon-laden computer-ese vacuous buzzword-speak. Words like flexible, dynamic, are just gimmicks and explain nothing.Dogru144 (talk) 21:55, 9 April 2009 (UTC)

Shekhar —Preceding unsigned comment added by 124.247.241.138 (talk) 11:27, 19 May 2009 (UTC)

VSTA Section

VSTA is actually an expansion of Visual Studio available in the Visual Studio SDK. Not part of VBA. I suggest that it belongs in it's own article, then have a blurb in this article referring to it. Oorang (talk) 14:48, 15 June 2009 (UTC)

Is it just me or is the only thing in that section that makes it hard to read all the "citation needed" markings. I understand it just fine. Yeah, maybe it doesn't have a whole lot to do with VBA, other than basically being a direct successor, but half of what is marked as "weasel words" aren't, and almost all calls for citations really don't need it. 198.103.195.30 (talk) 11:47, 6 July 2009 (UTC)

I agree. The crazy over-annotation of the section reads more like the equivalent of scare quotes than like a serious attempt to improve the article. Is the meaning of "application customization toolkit" really that unclear in context? 173.14.246.81 (talk) 16:54, 10 July 2009 (UTC)

I also wholeheartedly agree. This is a technical article that assumes a level of understanding of the concepts involved. While it is not perfect, it makes sense to me, therefore I will remove some or all of the markings. Curb Safe Charmer (talk) 15:02, 15 July 2009 (UTC)

WordBASIC Still Has Functions VBA Does Not?

Is it worth including the fact that WordBASIC has some features that were not carried forward in VBA?

For example, sorting the elements in an array. See Word VBA MVP Site for examples. VBA still includes the WordBASIC object so the functionality is still there, and the WordBASIC Help files can still be downloaded from Microsoft. AncientBrit (talk) 23:33, 10 September 2009 (UTC)

{{Tone|section}}

But wait a minute does not sound formal. Plus it looks like copied from somewhere else. Aphranius (talk) 08:45, 16 December 2011 (UTC)

Examples

On 30 March 2012, part of my edit removed the sections "Object models" and "Named variables and user-defined functions" and its subsection "Subroutines", and the section "Examples"; the reason in my edit summary was "WP is not a how-to". On 4 July 2012, Brews ohare restored the section "Named variables and user-defined functions" and its subsection "Subroutines", and the section "Examples"; the reason given in the edit summary was this material had been deleted without discussion.

My reason for the removal is based on the policy What Wikipedia is not, specifically WP:NOTHOWTO and its points 1 and 6: Wikipedia is not an instruction manual, it should not read like a "how-to" style owners manual or a tutorial or walk-through; Wikipedia is not a textbook, its purpose is "not to teach subject matter. It is not appropriate to create or edit articles that read as textbooks, with leading questions and systematic problem solutions as examples."

I believe the parts of the article restored by Brews ohare contavene Wikipedia policy and ought to removed again. -- Michael Bednarek (talk) 05:31, 4 July 2012 (UTC)

The boundary is not sharp between WP:NOTHOWTO and avoidance of too much explanation on one hand , and on the other providing enough information that a reader can understand the capability of the spreadsheet. I believe the deleted sections are in the latter category, helping the reader understand how VBA makes use of functions, subroutines and named variables, in my experience aspects of VBA essential to its utility. Below is the deleted material:

Named variables and user-defined functions

Using VBA, the user can add their own functions and subroutines that refer to these named ranges. In the figure at the right, the function sq is created in the Visual Basic editor supplied with Excel, and x & y are named variables in the spreadsheet.

• 
  Use of named column variables x & y in Microsoft Excel; y = x*x is calculated using the formula displayed in the formula box, which is copied down the entire y-column.
• 
  Use of a user-defined function sq(x) of named variable x in Microsoft Excel. Function supplied automatically from the code in the Visual Basic for Applications editor.

A common use of VBA is to add functionality that may be missing from the standard user interface. Use of VBA is made much easier by using named variables on the spreadsheet, as shown at the left. The formula for y=x² resembles Fortran or BASIC, and the Name Manager shows the definitions of column variables y and x.

Subroutine in Excel calculates the square of named column variable x read from the spreadsheet, and writes it into the named column variable y.

Subroutines

Functions themselves cannot write into the worksheet, but simply return their evaluation. However, in Microsoft Excel, subroutines can write values or text found within the subroutine directly to the spreadsheet. The figure shows the Visual Basic code for a subroutine that reads each member of the x-column (named column variable x), calculates its square, and writes this value into the corresponding y-column (also a named column variable). The y-column contains no formula because its values are calculated in the subroutine and simply written in.

IMO these three figures and accompanying text assist the reader to understand the utility of these features and will assist them in deciding whether VBA is suitable for their purpose. They should be restored. Brews ohare (talk) 05:46, 2 January 2013 (UTC)

The material I deleted with the reason "WP is not a how-to" (and which Brews ohare then added again) was much more than shown above; it included large chunks of VBA code in the section "Examples" – see the diffs above.

As to the material presented above: a discussion of Excel's named ranges and user-defined functions is not appropriate for an article on on VBA. It may be suitable for the article Microsoft Excel where it is barely mentioned and the illustrations seem out-of-context as they don't illustrate anything. But that's besides the point of this article where the discussion of Excel-specific features is out-of-scope. -- Michael Bednarek (talk) 09:34, 3 January 2013 (UTC)

This sentence seems backwards

This sentence in the article seems backwards:

The official reason given was that VBA relied heavily on machine code written for the PowerPC architecture, and that rewriting this code for dual PowerPC/Intel architectures would have added another 2 years to the development of the suite.

I would have guessed that VBA relied on the Intel architecture and that it had not been ported to the PowerPC. Perhaps theMicrosoft P-Code engine is implementing a PowerPC? Unfortunately, the "official reason" did not have a citation and so I can't tell if it's wrong or I don't understand something. --Marc Kupper|talk 22:56, 25 February 2013 (UTC)

Version history?

Looking for release dates / office containers for VBA5, VB6. (PS: History should be at the end of the article, not at the top, where it has unfortunately wound up in many wikipedia software articles) — Preceding unsigned comment added by 203.206.162.148 (talk) 22:50, 30 October 2016 (UTC)

A Commons file used on this page or its Wikidata item has been nominated for deletion

The following Wikimedia Commons file used on this page or its Wikidata item has been nominated for deletion:

• Microsoft Visual Basic for Applications logo.svg

Participate in the deletion discussion at the nomination page. —Community Tech bot (talk) 19:55, 20 May 2023 (UTC)