|SharePoint Designer Developer Reference|
This topic is designed to give users who may be familiar with Microsoft Office SharePoint Designer, but unfamiliar with Microsoft Visual Basic for Applications (VBA), a background on some of the basic concepts in a Office SharePoint Designer-based programming environment. Programming in Office SharePoint Designer Visual Basic for Applications provides you with HTML tools in an Microsoft Office programming environment where you can create procedures that perform a task or a series of tasks. For example, you could:
- Create a procedure that retrieves data from a Microsoft Access database and displays the data on your Web page.
- Publish a Microsoft PowerPoint presentation for automatic updates over the Internet.
- Automatically update a Microsoft Excel spreadsheet with input from users responding to your Web site.
This topic provides information on the following VBA programming concepts.
Organize code for modular use
Types of procedures
Public and private procedures
Types of procedure calls
Event procedures and arguments
Create a table in Office SharePoint Designer from an Access database
Visual Basic procedures provide a way for developers to organize code for modular use. Instead of writing the same calculator function over and over for each program, you can take that segment of code (the calculator function) and compile it into a general program, that can then be accessed by many other programs. In Visual Basic, a block of code is enclosed between a procedure heading and a closure statement—the Sub and End Sub statements.
The basic syntax of a procedure within Visual Basic is shown in the following code sample.
To run any of the complete code examples included in the Office SharePoint Designer Visual Basic for Applications online help, follow these steps:
- Open Office SharePoint Designer, select Macro from the Tools menu, and then click Visual Basic Editor.
- Double-click Microsoft_Office SharePoint Designer (or the current project) in the Project window and expand the Modules folder.
- Double-click Module 1 to open the Code window.
- Copy the code block from the documentation, and then paste it into the Code window.
- Click Run Sub/UserForm on the toolbar.
Your code will automatically be saved when you close the Visual Basic Editor.
Office SharePoint Designer VBA provides two types of procedures, Sub and Function procedures. Sub procedures perform tasks but do not return any values. They may be called from other subroutines or executed in response to an event, such as a mouse click or a keystroke.
|A Sub procedure can be an event procedure, but it can also perform a task without necessarily responding to an event.|
The following procedure retrieves the version number of Office SharePoint Designer from the active Web site but doesn't return the version number to any other procedure.
A Function procedure also performs tasks, but it can in addition return one or more values as arguments. The following code sample returns the version number of Office SharePoint Designer to a calling procedure.
ReturnVersion now contains the version number of Office SharePoint Designer. To access this value in the calling procedure, you could write code similar to the following sample.
Alternatively, you could assign the expression
ReturnVersion to a variable and append the variable to the message box statement instead of the function call.
Both Sub and Function procedures can be called to perform their tasks, depending on whether the procedures are declared Public or Private.
A macro is a third term used to describe code in VBA. As a public Sub procedure that doesn't take arguments, a macro may or may not call other procedures and can be assigned to command bars and shortcut keys or run from the Macro dialog box.
Visual Basic provides two ways to access a procedure. By default, procedures are public—they can be called from any other procedure in any module within your application. For example, if you write a procedure that lists images by file name on a Web page, you want to declare that procedure public so that you could use it across all of your Web sites. However, if you write a procedure that edits a specific database, you want that procedure to be available only to the module that handles editing the database—in that case, you can declare the procedure private. Procedures that have been declared private can be referenced only by other procedures within the same module. The function shown previously is declared a public function in the following code sample and can be called across modules and projects.
In contrast, a procedure that is used to edit a database should be declared private.
How do you programmatically run a procedure? You declare it the same way that you would use a keyword, such as Open. The following procedure calls the
ReturnVersion function and assigns the returned value to a local variable,
MyVersion, for the value that is passed to the procedure.
If you have no information to pass from one procedure to another, simply declare the procedure name, as shown in the following code sample.
TestCall2 procedure calls another procedure,
DisplayCompanySplashScreen, which doesn't take any arguments or return any values.
If you want an event, such as clicking a command button, to trigger the execution of code in cases in which you usually pass a value into the calling procedure, you can execute the results from the function rather than return the results. In this case the
ReturnVersion function becomes a subroutine and initiates the display of the version number for the application.
DisplayMsgBox subroutine shown in the following code sample displays the contents of the variable
varGotAppVersion that was passed from the
An event procedure can now initiate the display of the value that is passed from the
The following procedure retrieves data from an open Microsoft Access database and inserts it into a table on a Web page. The
ParseDBTable procedure provides the parameters for the
ParseAccessTable function, which calls the following functions to create and populate the table:
AddDBTableToPage—creates a new table
AddDBRow—inserts a row onto the Web page
AddMemo—retrieves the memos from the Access database, returns them as bookmarks at the bottom of the page below the new table, and returns the URL to the bookmark
|The Access database Northwind.mdb is used for this example. To run the example, you must have references in the Visual Basic Editor to the Microsoft DAO 3.6 Object Library and the Microsoft Access Object Library. You must also open an Access database before running the example, and you must add a blank temporary file named tmp.htm in the active Web site. If you use a database other than Northwind.mdb, you must specify the database name and table in the |