Using the VBA Object Library

In the previous example, you used the properties of objects that are members of the Shapes collection in the Excel object library. While the Excel library contains objects specific to using Microsoft Excel, the VBA object library provides access to many built-in VBA functions grouped by categories. These functions are general in nature. They allow you to manage files, set the date and time, interact with users, convert data types, deal with text strings, or perform mathematical calculations. In the following exercise, you will see how to use one of the built-in VBA functions to create a new subfolder without leaving Excel.

1. Return to the Manipulations module where you entered the MoveTextBox and MoveCircle procedures.

2. Enter on a new line the name of the new procedure: Sub NewFolder().

3. Click Enter. Visual Basic will enter the ending keywords End Sub.

4. Press F2 to activate the Object Browser.

5. Click the drop-down arrow in the Libraries/Project list box and select VBA.

6. Enter file as the search text in the Search box, and press Enter.

7. Scroll down in the Members list box and highlight the MkDir method (see Figure 2-21 on the following page).

8. Click the Copy button in the Object Browser window to copy the selected method name to the Windows clipboard.

9. Return to the Manipulations window and paste the copied instruction inside the procedure NewFolder.

10. Enter a space, followed by "C:\Study". Make sure to enter the name of the entire path in the quotes. The NewFolder procedure is:

Sub NewFolder()

MkDir "C:\Study" End Sub

11. Run the NewFolder procedure.

Sub NewFolder()

MkDir "C:\Study" End Sub

Object Browser




J file

"3 «F

1— Search Results



I Library | Class

I Member



W', VBA FileSystem

Ift VBA ^ VbFileAttribute BFi VBA ErrObject eS1 HelpFlle IA VBA FileSystem FlleAltr ift VBA A FileSystem & F He Copy Ifi. VBA FileSystem ^ FlleDateTime ■A VBA FileSystem FileLen



Members of F lie system1

Conversion DateTime

Kill Lot







Information Interaction <*£ KeyCodeConstants


■Ï» Reset & RmDir Seek


Sub mkdii iPath As string Member of VBA .FileSvstem

Figure 2-21:

When writing procedures from scratch, consult the Object Browser for names of the built-in VBA functions.

Figure 2-21:

When writing procedures from scratch, consult the Object Browser for names of the built-in VBA functions.

When you run the NewFolder procedure, Visual Basic creates a new folder on drive C. To see the folder, activate Windows Explorer. After creating a new folder, you may realize that you don't need it after all. Although you could easily delete the folder while in Windows Explorer, how about getting rid of it programmatically? The Object Browser displays many other methods that are useful for working with folders and files. The RmDir method is just as simple to use as the MkDir method. To remove the Study folder from your hard drive, simply replace the MkDir method with the RmDir method, and then rerun the NewFolder procedure. Or, create a new procedure called RemoveFolder, as shown here:

Sub RemoveFolder()

RmDir "C:\Study" End Sub

The RmDir method allows you to remove unwanted folders from your hard disk.

0 0

Post a comment