Title: Introduction to VBA

Issue: Spectrum Nov/Dec '98

author:Nathan Rector

company:Natec Systems

email:nater@northcoast.com

http:www.northcoast.com/~nater/

VBA, or Visual Basic for Applications, is commonly thought of as the macro language in the Microsoft Office products. VBA originated from Microsoft Office, but has expanded to other programs outside of the Microsoft label.

Each VBA application, even within the various products of Microsoft Office, works just a little different. They all use the basic underlying object oriented structure, but the objects supplied with each application are different. Even the objects that have same names may not have the same properties, methods, or events as the other applications.

This causes many programmers to only use VBA to a minor degree, or for the little macro programs that may be needed. Selecting all the text with the words "Subroutine" and changing the font from "Roman Times" to "Impact" is an limited use example of VBA.

VBA is much more than just a macro language. Programming in VBA gives the programmer complete control over the application. A programmer can take off menu options, add new ones, and even (in Microsoft Office) create custom help notes for the Microsoft Assistant (the little animated Paperclip Guy that most people find extremely annoying) to display.

Imagine - giving users a Microsoft Word document created from your MultiValue Host information that uses a special template to keep users from altering the text that was pulled from the Host system, or keeps the users from saving and opening another document other than the ones you control.

How about creating a special dialog save/open dialog box that uses the security levels found on your MultiValue Host system to control what and where the user has access on the Network or PC they happen to be work on.

These are just some examples of what VBA can do with an application like Microsoft Word. You can do the same things in Microsoft Excel, or any other application that uses VBA.

VBA in Microsoft Excel is used more often. Just about every accountant who's worked with Microsoft Excel has fallen in love with it. For those that have not used Excel, its a spreadsheet program that allows you to create all kind of different visual displays when the user is done working with the data, like graphs.

The main problem most people have with letting their employees to use Excel is that the data is now in two different places. How do you keep the user from altering the data from the MultiValue Host once it has been pulled from the Host system? An even more common problem is having to rewrite all the accounting calculating programs that are still on their Host system so they can have them in Excel.

With VBA and Visual Basic Host supplied by the Host system or third-party products like WinLink from Via Systems, a programmer can call a MultiValue Host program from inside Excel without the user even knowing it. This allows both the control of how the data is being processed as well as the use of legacy programs the accounting department already has.

These are just some of the examples of what VBA can do. An additional advantage to VBA is that all the code is included in the document or spreadsheet, or whatever data file the application uses. If the user decides to walk off with this data file, all the VBA code goes with it, including any security you've programmed into the document using VBA.

Many people are concerned about the users altering the VBA code a document so they can alter it. Most applications supply the ability to password protect access to the VBA code. This keeps your users from altering or copy existing VBA code for their own use without permission.

Now that I've talked about the different practical uses of VBA, let me talk a little bit about programming with VBA. VBA is uses the object-oriented model to its maximum capacity. Everything is control using one object or another.

An example of VBA code:

Sub Macro2()

'

' Macro2 Macro

' creates new chart. Make it a line graph.

Charts.Add

ActiveChart.ChartType = xlLineMarkers

' Telling the chart which range of cells to use, and to plot by

' columns. Use xlRows if you want to plot by rows

ActiveChart.SetSourceData _

Source:=Sheets("Monthly Sales").Range("B4:N12"), _

PlotBy:=xlColumns

' tells Excel that the chart is located as an object in the

' worksheet "Monthly Sales"

ActiveChart.Location Where:=xlLocationAsObject, _

Name:="Monthly Sales"

' Sets a few default values

With ActiveChart

.HasTitle = False

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

End With

End Sub

The next few articles I will be talking about VBA giving specific examples with code on how to do things in Microsoft Office.

Title: Introduction to VBA

Issue: Spectrum Nov/Dec '98

author:Nathan Rector

company:Natec Systems

email:nater@northcoast.com

http:www.northcoast.com/~nater/

VBA, or Visual Basic for Applications, is commonly thought of as the macro language in the Microsoft Office products. VBA originated from Microsoft Office, but has expanded to other programs outside of the Microsoft label.

Each VBA application, even within the various products of Microsoft Office, works just a little different. They all use the basic underlying object oriented structure, but the objects supplied with each application are different. Even the objects that have same names may not have the same properties, methods, or events as the other applications.

This causes many programmers to only use VBA to a minor degree, or for the little macro programs that may be needed. Selecting all the text with the words "Subroutine" and changing the font from "Roman Times" to "Impact" is an limited use example of VBA.

VBA is much more than just a macro language. Programming in VBA gives the programmer complete control over the application. A programmer can take off menu options, add new ones, and even (in Microsoft Office) create custom help notes for the Microsoft Assistant (the little animated Paperclip Guy that most people find extremely annoying) to display.

Imagine - giving users a Microsoft Word document created from your MultiValue Host information that uses a special template to keep users from altering the text that was pulled from the Host system, or keeps the users from saving and opening another document other than the ones you control.

How about creating a special dialog save/open dialog box that uses the security levels found on your MultiValue Host system to control what and where the user has access on the Network or PC they happen to be work on.

These are just some examples of what VBA can do with an application like Microsoft Word. You can do the same things in Microsoft Excel, or any other application that uses VBA.

VBA in Microsoft Excel is used more often. Just about every accountant who's worked with Microsoft Excel has fallen in love with it. For those that have not used Excel, its a spreadsheet program that allows you to create all kind of different visual displays when the user is done working with the data, like graphs.

The main problem most people have with letting their employees to use Excel is that the data is now in two different places. How do you keep the user from altering the data from the MultiValue Host once it has been pulled from the Host system? An even more common problem is having to rewrite all the accounting calculating programs that are still on their Host system so they can have them in Excel.

With VBA and Visual Basic Host supplied by the Host system or third-party products like WinLink from Via Systems, a programmer can call a MultiValue Host program from inside Excel without the user even knowing it. This allows both the control of how the data is being processed as well as the use of legacy programs the accounting department already has.

These are just some of the examples of what VBA can do. An additional advantage to VBA is that all the code is included in the document or spreadsheet, or whatever data file the application uses. If the user decides to walk off with this data file, all the VBA code goes with it, including any security you've programmed into the document using VBA.

Many people are concerned about the users altering the VBA code a document so they can alter it. Most applications supply the ability to password protect access to the VBA code. This keeps your users from altering or copy existing VBA code for their own use without permission.

Now that I've talked about the different practical uses of VBA, let me talk a little bit about programming with VBA. VBA is uses the object-oriented model to its maximum capacity. Everything is control using one object or another.

An example of VBA code:

Sub Macro2()

'

' Macro2 Macro

' creates new chart. Make it a line graph.

Charts.Add

ActiveChart.ChartType = xlLineMarkers

' Telling the chart which range of cells to use, and to plot by

' columns. Use xlRows if you want to plot by rows

ActiveChart.SetSourceData _

Source:=Sheets("Monthly Sales").Range("B4:N12"), _

PlotBy:=xlColumns

' tells Excel that the chart is located as an object in the

' worksheet "Monthly Sales"

ActiveChart.Location Where:=xlLocationAsObject, _

Name:="Monthly Sales"

' Sets a few default values

With ActiveChart

.HasTitle = False

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

End With

End Sub

The next few articles I will be talking about VBA giving specific examples with code on how to do things in Microsoft Office.