Exploring Procedures
This topic is designed to give users who may be familiar with Microsoft FrontPage, but unfamiliar with Microsoft Visual Basic for Applications (VBA), a background on some of the basic concepts in a FrontPage-based programming environment. Programming in FrontPage 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.
Event procedures and arguments
Create a table in FrontPage from an Access database
Organize code for modular use
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 basic syntax of a procedure within Visual Basic is shown in the following code sample.
[Private|Public|Static] Sub procedurename(arguments)
statements
End Sub
To run any of the complete code examples included in the FrontPage Visual Basic for Applications online help, follow these steps:
- Open FrontPage, select Macro from the Tools menu, and then click Visual Basic Editor.
- Double-click Microsoft_FrontPage (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.
Types of procedures
FrontPage 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.
Note 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 FrontPage from the active Web site but doesn't return the version number to any other procedure.
Sub DisplayVersion()
Dim myWeb As WebEx
Dim myVersion As String
myVersion = "FrontPage version number: " & ActiveWeb.Application.Version
End Sub
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 FrontPage to a calling procedure.
Function ReturnVersion() As Variant
Dim varAppVersion As Variant
varAppVersion = Application.Version
ReturnVersion = varAppVersion
End Function
The variable ReturnVersion
now contains the version number of FrontPage. To access this value in the calling procedure, you could write code similar to the following sample.
Sub GetAppVersion()
Dim myAppVersion As Variant
MsgBox "This version of FrontPage is version " _
& ReturnVersion
End Sub
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 Sub or Function procedures and can be assigned to command bars and shortcut keys or run from the Macro dialog box.
Public and private procedures
Visual Basic provides two ways to access a procedure. By default, procedures are public
Public Function ReturnVersion() As Variant
statements
End Function
In contrast, a procedure that is used to edit a database should be declared private.
Private Function EditCustomerName(strFirstName As String)
statements
End Function
Types of procedure calls
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.
Sub TestCall()
Dim MyVersion As Variant
MyVersion = ReturnVersion
End Sub
If you didn't have any information to pass from one procedure to another, you would simply declare the procedure name, as shown in the following code sample.
Sub TestCall2()
DisplayCompanySplashScreen
End Sub
The TestCall2
procedure calls another procedure, DisplayCompanySplashScreen
, which doesn't take any arguments or return any values.
Event procedures and arguments
If you want an event, such as clicking a command button, to trigger the execution of code in cases where you would 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.
Sub ReturnVersion()
Dim varAppVersion As Variant
varAppVersion = Application.System.Version
DisplayMsgBox varAppVersion
End Sub
The DisplayMsgBox
subroutine shown in the following code sample displays the contents of the variable varGotAppVersion
that was passed from the ReturnVersion
subroutine.
Sub DisplayMsgBox(varGotAppVersion As Variant)
Dim varDisplayAppVersion As Variant
varDisplayAppVersion = varGotAppVersion
MsgBox "This application is version " _
& varDisplayAppVersion
End Sub
An event procedure can now initiate the display of the value that is passed from the ReturnVersion
subroutine.
Private Sub CommandButton1_Click()
ReturnVersion
End Sub
Create a table in FrontPage from an Access database
The following procedure combines objects from the Page object model
and the Web object model
to retrieve data from an open Microsoft Access database and insert it into a table on a FrontPage-based 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
Note The Access database, Northwind.mdb, was 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 called 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 ParseDBTable
procedure.
Function AddDBTableToPage(myPage As PageWindowEx, _
myTableName As String, myFields As Integer)
Dim myTable As FPHTMLTable
Dim myHTMLString As String
Dim myCount As Integer
myHTMLString = "<table border=""2"" id=""myRecordSet_" & _
myTableName & """>" & vbCrLf
myHTMLString = myHTMLString & "<tr>" & vbCrLf
For myCount = 1 To myFields
myHTMLString = myHTMLString & "<td id=""myDBField_" & _
myCount & """> </td>" & vbCrLf
Next myCount
myHTMLString = myHTMLString & "</tr>" & vbCrLf
myHTMLString = myHTMLString & "</table>" & vbCrLf
Call myPage.Document.body.insertAdjacentHTML("BeforeEnd", _
myHTMLString)
End Function
Function AddDBRow(myDBTable As FPHTMLTable)
Dim myHTMLString As String
Dim myTableRow As FPHTMLTableRow
Set myTableRow = myDBTable.rows(0)
myHTMLString = myTableRow.outerHTML
Call myDBTable.insertAdjacentHTML("BeforeEnd", myHTMLString)
End Function
Function AddMemo(myCurrentPage As PageWindowEx, myDBMemo As String, _
myBkMarkField As String, myIndex) As String
Dim myHTMLString As String
Dim myMemoBkMark As String
Dim myBookMark As FPHTMLAnchorElement
myMemoBkMark = myBkMarkField & "_" & myIndex
myHTMLString = "<a name=""" & myMemoBkMark & """> Memo #" & _
myIndex & "</a>" & vbCrLf
'Add the bookmark to the page.
Call myCurrentPage.Document.body.insertAdjacentHTML("BeforeEnd", _
myHTMLString)
Set myBookMark = myCurrentPage.Document.all(myMemoBkMark)
'Add the memo text to the page.
Call myCurrentPage.Document.body.insertAdjacentHTML("BeforeEnd", _
myDBMemo)
AddMemo = "<a href=""#" & myBookMark.Name & """>"
End Function
Function ParseAccessTable(myDBName As String, myTableName As String)
'Access/DAO Declarations.
Dim myDBApp As Access.Application
Dim myRecordSet As DAO.recordset
Dim myDBField As DAO.Field
'FrontPage Page object model declarations.
Dim myPage As PageWindowEx
Dim myTable As FPHTMLTable
Dim myTableRow As FPHTMLTableRow
Dim myTableCell As FPHTMLTableCell
'Function declarations.
Dim myCount As Integer
Dim myFieldValue As String
Dim myRecordCount As Integer
myRecordCount = 0
'Function constants.
Const myTempPage = "tmp.htm"
'Get the current Access database.
On Error GoTo AccessNotThereYet
Set myDBApp = GetObject(, "Access.Application")
'Get the database table.
On Error Resume Next
Set myRecordSet = myDBApp.CurrentDb.OpenRecordset(myTableName)
'Add a new page to the current Web site.
Set myPage = ActiveWeb.LocatePage(myTempPage)
myPage.SaveAs myTableName & ".htm"
'Delete the temporary file from Web site.
ActiveWeb.LocatePage(myTempPage).File.Delete
'Add a database-ready table to the page with the proper number of fields.
AddDBTableToPage myPage, myTableName, myRecordSet.Fields.Count
'Get a reference to the table.
Set myTable = myPage.Document.all.tags("table").Item(0)
'Populate the first row.
For myCount = 0 To myRecordSet.Fields.Count - 1
myTable.rows(0).cells(myCount).innerHTML = "<b>" & _
Trim(myRecordSet.Fields(myCount).Name) & "</b>"
Next
'Populate the rest of the table.
While Not (myRecordSet.EOF)
AddDBRow myTable
Set myTableRow = myTable.rows(myTable.rows.Length - 1)
For myCount = 0 To myRecordSet.Fields.Count - 1
Set myTableCell = myTableRow.cells(myCount)
If IsNull(myRecordSet.Fields(myCount)) Then
myFieldValue = "None"
Else
myFieldValue = Trim(myRecordSet.Fields(myCount).Value)
End If
If myRecordSet.Fields(myCount).Type = DAO.dbMemo Then
myFieldValue = AddMemo(myPage, _
myRecordSet.Fields(myCount).Value, _
myRecordSet.Fields(myCount).Name, myRecordCount)
End If
myTableCell.innerHTML = myFieldValue
Next myCount
myRecordSet.MoveNext
myRecordCount = myRecordCount + 1
Wend
myPage.Save
myDBApp.Quit
Exit Function
AccessNotThereYet:
Debug.Print Err.Number & ":" & Err.Description
Resume
End Function
Private Sub ParseDBTable()
Call ParseAccessTable("Northwind.mdb", "Products")
End Sub