Creative Blog

Catégorie : Excel VBA Course

  • Some Examples with Excel VBA

    Let us begin with the task of preparing a Word document that contains a list and examples of laboratory assignments for the course “Applied and Integrated Packages”, formatted as a table that includes embedded objects (MS Excel workbooks) displayed as icons.

    To prepare such a document, follow the recommendations below:

    • Create a new Word document and add a title for the table.
    • Design the table layout and add information to the table header and columns: No., Topic.
    • To embed objects into the File Example column so that they appear as icons, go to the Insert tab of the ribbon, in the Text group, click the arrow on the Object button, and select the Object command.
    • In the Object dialog box, on the Create from File tab, specify the file location in the File name field, check the Display as icon option, use the Change Icon button to select an appropriate icon, and add a caption.
    • Format the completed table and save your prepared document.

    Now let us look at preparing a Word document titled “Summary Table Report”, which contains a table with data from an Excel workbook .

    Steps for preparing this document:

    • Prepare the Word document (client): enter the necessary text information and leave space for the Excel table.
    • Prepare the Excel document (server) according:

     , using a formula to calculate the average value.

    • Select the prepared table in the Excel document, go to the Home tab of the ribbon, and in the Clipboard group, click Copy.
    • Switch to Word and place the cursor at the insertion point for the linked data.
    • On the Home tab of the ribbon, in the Clipboard group, click the arrow on the Paste button and select Paste Special.
    • In the dialog box that appears, set the necessary options and click OK.

    • Save the resulting Word document.

    TIP
    When working with a linked object in a client document, it is convenient to use its context menu .

  • Embedding Data from Other Applications with Excel VBA

    Microsoft Office 2010 applications also provide the ability to embed data into a document from any server application that supports the OLE technology.

    After embedding, the data becomes part of the specific Microsoft Office 2010 application document. When such data is edited, the server application is launched from within the client application. The inserted object is saved together with the document file, and editing it does not affect the original file.

    Once the server application is loaded from the client application, the embedded object can be viewed and processed while simultaneously seeing the document into which the object has been embedded. This capability is called in-place activation.

    Embedding an object into a Microsoft Office Word 2010 document can be done in two ways:

    • Using the Object dialog box, which allows the embedded object to be created directly in the client application:
      • On the Create New tab, you can select the embedded object by the type of server application.
      • On the Create from File tab, you can select the embedded object as a file.

    NOTE
    In this case, in the Object dialog box, the Link to file option should not be selected.

    • By copying from the document in which the object already exists.

    Inserting embedded objects into a Microsoft Office 2010 document is done through two types of applications:

    • Any server applications that support OLE.
    • Add-ins that come with Microsoft Office 2010. Add-ins are not standalone applications and can only be used from within a client application.

    Windows applications that only partially support OLE may not appear in the Object dialog box. However, it is possible to embed such objects in one of the following ways:

    • Perform a copy in the server application document.
    • In the client application, go to the Home tab of the ribbon, and in the Clipboard group, click the arrow on the Paste button, then select the Paste Special command from the list.

  • Data Linking with Excel VBA

    If you need to use data linking technology in a document, Microsoft Office applications provide two ways to do this:

    • By using a remote reference formula, which can be entered from the keyboard or inserted into the document with the Paste Special command, chosen from the list that appears when you click the arrow on the Paste button in the Clipboard group on the Home tab of the ribbon.
    • By using macros that control Dynamic Data Exchange (DDE).

    Many Microsoft Office applications can receive data from other applications in the suite, and when the data changes in the server application, it is automatically updated in the client application.

    You can choose how the linked data will be updated: automatically or manually. If updates are performed manually, the linked applications run faster.

    To link, for example, a Microsoft Office Word 2010 client document to another application, follow these steps:

    • Open the Word client document and the server application.
    • Activate the server application, i.e., switch to it.
    • Select the data to be linked.
    • On the Home tab of the ribbon, in the Clipboard group, click Copy.
    • Activate the Word client document and place the cursor where the linked data should be inserted.
    • On the Home tab of the ribbon, in the Clipboard group, click the arrow on the Paste button and select Paste Special.
    • In the Paste Special window that opens, select the Link option, choose the linking method from the As list, and click OK.

    TIP
    When working with Microsoft Office documents, it is convenient to use hyperlinks.

    You can also link a Word 2010 client document to another application by using the Object insertion window. To do this, go to the Insert tab of the ribbon, in the Text group, click the arrow on the Object button, and select Object. In the Object window that opens, go to the Create from File tab .

    In the File name field, specify the location of the server application (using the Browse button if necessary), and check the boxes for Link to file and Display as icon. You can also choose an appropriate icon to be displayed in the document by using the Change Icon button.

    As a result, your document will contain an icon, and clicking it will open the corresponding server application.

    NOTE
    If in the Object window on the Create from File tab you select only the Link to file checkbox, then the content of the server document will be displayed in the client document.

    Clicking it will open the server document.

    For Microsoft Office applications, you can specify the data update mode: go to the File tab of the ribbon and click Options. In the window that opens, select the Advanced category on the left, and in the General group on the right, check the Update automatic links at open option.

    NOTE
    When inserting images into a document using the Insert Picture window (go to the Insert tab of the ribbon and in the Illustrations group click Picture), you can also configure the link between the Word document and the graphic file: in the Insert Picture window, click the arrow on the Insert button and select the desired command.

  • What is ActiveX Technology with Excel VBA

    As you know, each of the MS Office applications is a very flexible product, providing the user with extensive capabilities. By using these capabilities, it is possible to create more efficient and technologically advanced applications. ActiveX technology enables interaction between different MS Office applications, as well as with any programs that support this technology, for example, Visual Basic, through the Component Object Model (COM). This technology was previously called OLE (Object Linking and Embedding). Currently, OLE is part of ActiveX technology.

    ActiveX technology is designed for simple and efficient interaction between different products. For example, MS Word or MS Excel can provide their objects to each other. This makes it possible to create documents consisting of elements developed in different applications. ActiveX provides the developer with two powerful tools for building applications:

    • OLE (Object Linking and Embedding) — linking and embedding objects from different applications, which allows editing objects created by other applications directly within a document.
    • Automation — a technology that enables programmatic control of both embedded objects and the objects of other applications. This significantly reduces development time, since there is no need to reimplement features that are not built-in but are available in other specialized applications.

    NOTE
    ActiveX technology assumes that the integrated applications, whose objects are used in a document, are installed and registered in the system registry. As a rule, application registration takes place automatically during installation.

  • How to Transfer Data from Client to Server with Excel VBA

    To transfer data from the client to the server, forms are often used, which group several controls together.

    In the example below, the form contains two fields — Name and E-Mail — and two buttons — Submit and Reset.

    • Clicking the Submit button initiates the execution of the ASP file specified in the action attribute of the <form> tag (in this case, Answer.asp).
    • The form property of the server-side Request object passes the values of the input elements within the form to this file.
    • The identification of tags is performed using the values of their name attributes.

    After filling out the form, the user clicks Submit, which sends the data to the server and processes it with the program Answer.asp, confirming that the data has been received.

    Clicking the Reset button clears the form values, restoring their default values.

    Steps to Run This Example

    1. In Notepad, type the following codeand save the file as ask.htm.

    Transferring data from client to server. File ask.htm

    <html>
<body>
<h1>Enter Your Data</h1>
<form id="frmName" method="post" action="http://localhost/answer.asp">
Name: <input type="text" name="txtName">
<br>
E-Mail: <input type="text" name="txtEMail">
<br>
<input type="submit" value="Submit">
<input type="reset" value="Reset">
</form>
</body>
</html>
    • Then type the following code in Notepad and save the file as answer.asp.

    Transferring data from client to server. File answer.asp

    <%@ Language=VBScript %>
<html>
<body>
<%
Response.Write "<H2>Received Data</H2>"
Response.Write "Name: " & Request.Form("txtName")
Response.Write "<BR>"
Response.Write "E-Mail: " & Request.Form("txtEMail")
%>
</body>
</html>
    • Place the created files in the directory C:\Inetpub\wwwroot on your computer.
    • Launch the file iisstart.htm, located in the same directory, and in the browser’s address bar enter:

    http://localhost/ask.htm

    • Click the Submit button in the browser window and check the ASP request execution. Also, test the Reset button.

    Our Results

    • Forms are the main way to transfer data from the client to the server.
    • The ASP Request.Form collection is used to retrieve form data.
    • The Response.Write method confirms or processes the submitted data.

  • How to Create Server-Side Web Scripts with Excel VBA

    Microsoft Active Server Pages (ASP) scripts, written in VBScript, allow you to create programs that are executed on the server side. From all the output generated, ASP scripts send only text and HTML tags to the client’s computer, where they are displayed in the browser window.

    Using the ADO (ActiveX Data Objects) model, ASP scripts can access databases stored on the server, and using FSO (File System Object) they can work with text files stored there as well. This makes it easy to create various web applications such as virtual catalogs. In simplified terms, ADO can be described as a data access model optimized for web projects.

    To use ASP, you need to install Microsoft’s Internet Information Services (IIS) web server to work with operating systems such as Windows XP, Windows Vista, or Windows 7.

    Windows provides the ability to run server applications on the client’s computer. To do this, the server application file must be placed in the directory C:\Inetpub\wwwroot on the client’s computer. This is the root directory of the server, created by default during installation.

    Clients connected to your computer via the HTTP protocol automatically access this directory. The directory contains the file iisstart.htm, which opens in the client’s browser when connecting to the server without specifying a file name.

    To open an ASP file from your computer in a browser, you must enter:

    http://127.0.0.1/filename.asp

    or

    http://localhost/filename.asp

    where http://127.0.0.1 or http://localhost is a reserved IP address for connecting to a server running on the same computer from which the request originates.

    Example 1: Greeting Based on Time of Day

    As an example, let’s create the following code, which displays different greetings in the browser depending on the time the document is loaded.

    To let the computer know which part of the ASP file should be executed on the server, you must use the <% statements %> tag, where statements are the server-side commands. To determine the document load time, the Time() function is used.

    Steps:

    • Verify that IIS is installed on your computer. If not, install it. For example, on Windows 7: go to Control Panel → Programs and Features → Turn Windows features on or off, and enable IIS Services.
    • In Notepad, type the code  and save it with the .asp extension, for example, 6-Hello.asp.
    • Place the file 6-Hello.asp in C:\Inetpub\wwwroot.
    • Launch iisstart.htm from C:\Inetpub\wwwroot, then in the browser’s address bar enter:
    • http://127.0.0.1/6-hello.asp
    • The browser window will display a greeting depending on the time of day).
    • Right-click in the browser window, choose View HTML Source, and you will see the code that the client receives from the server.

    Greeting. File 6-Hello.asp

    <html>
<body>
<h1>Greeting</h1>
<% If Time>= #04:00:00# And Time < #12:00:00# Then %>
    <h2>Good Morning!!!</h2>
<% ElseIf Time>= #12:00:00# And Time < #18:00:00# Then %>
    <h2>Good Afternoon!!!</h2>
<% ElseIf Time>= #18:00:00# And Time < #23:00:00# Then %>
    <h2>Good Evening!!!</h2>
<% Else %>
    <h2>Good Night!!!</h2>
<% End If %>
</body>
</html>

    Greeting. Code returned to the client

    <html>
<body>
<h1>Greeting</h1>
<h2>Good Afternoon!!!</h2>
</body>
</html>

    Example 2: Greeting Based on the Day of the Week

    The following code displays greetings depending on the day of the week when the document is loaded.

    The <% statements %> tag again marks the server-side code. The equal sign in <%=Variable%> is shorthand for the Response.Write method, which sends information from the server to the client.

    Your first server-side script. File day1.asp

    <%@ Language=VBScript %>
<html>
<h1>Today <%= Now %></h1>
<% If Weekday(Now) = vbFriday Then %>
    <h2>Tomorrow the weekend begins!!!!</h2>
<% ElseIf Weekday(Now) = vbSaturday Or Weekday(Now) = vbSunday Then %>
    <h2>Weekend!!!!</h2>
<% Else %>
    <h2>Still have to work</h2>
<% End If %>
</html>

    Example 3: Using Response.Write Explicitly

    Here is the same script, rewritten using the Response object and its Write method.

    Your first server-side script. File day2.asp

    <%@ Language=VBScript %>
<html>
<h1>Today <% Response.Write Now %></h1>
<%
If Weekday(Now) = vbFriday Then
    Response.Write "<h2>Tomorrow the weekend begins!!!!</h2>"
ElseIf Weekday(Now) = vbSaturday Or Weekday(Now) = vbSunday Then
    Response.Write "<h2>Weekend!!!!</h2>"
Else
    Response.Write "<h2>Still have to work</h2>"
End If
%>
</html>

  • How to Create Client-Side Web Scripts with Excel VBA

    Using VBScript, you can write code directly inside HTML files, and this code will be executed when such a file is loaded in a browser — i.e., on the client side. This code must be placed inside the paired <script> tag, whose language attribute specifies the scripting language.

    As an example, The code shows that displays a greeting window when you click on the text “Hello, World!”.

    <html>
<head>
<script language="VBScript">
Sub hello()
    Msgbox "Hello, World, again!"
End Sub
</script>
</head>
<body>
    <h1 onclick="hello">Hello, World!</h1>
</body>
</html>

    To create and run this example, do the following:

    • Open the standard Windows application Notepad.
    • In the window that opens, type the suggested code.
    • Save the code in a file with the .html extension, for example, Hello-world.html in the root directory of drive D:.
    • Open the file you created, either by double-clicking it with the left mouse button or pressing Enter.
    • In the browser window, the greeting you wrote (“Hello, World!”) will be displayed. If your browser blocks the execution of scripts or ActiveX elements, click the message that appears in the browser window and allow the blocked content to run.
    • Click the “Hello, World!” text in the browser window and verify that the script executes.

  • Web Query and Retrieving Data from a Web Page with Excel VBA

    Web pages often contain information needed for analysis in Microsoft Office Excel. For example, in MS Excel you can analyze stock quotes using data that comes directly from a web page, or a sales table from an organization’s private webpage.

    If necessary, you can extract refreshable data (in which case it can be updated directly in MS Excel to reflect the latest changes on the web page) or static data from a web page to store on a worksheet.

    Using a Web Query lets you obtain data from a web page—such as a single table, several tables, or all text—and analyze it with Excel’s tools.

    To create a web query, do the following:

    • Place the file Data.htm on the D: drive of your computer.
    • Open a new workbook, go to the Data tab on the ribbon, and in the Get External Data group choose From Web.
    • In the New Web Query window, in the address field enter a link to the file you placed: …/Data.htm. 

    NOTE
    The Address field is for entering the URL of the required web page. For example, if Internet Information Services (IIS) is installed on your computer (described later), its root directory is C:\Inetpub\wwwroot. You can place in this directory, for instance, a file with the .asp extension (a web page that also contains data). In that case, you should enter the following link in the Address field:
    http://localhost/filename.asp.
    You must not specify a link in the form C:\Inetpub\wwwroot\filename.asp in this window.

    • Using the arrow buttons, select the desired table on the web page.
    • Click Import.

    • In the Import Data window, set Where do you want to put the data? to Existing worksheet, and in the corresponding field enter the cell reference (for example, A1) where the upper-left corner of the imported table will be placed.

    • Click Properties to specify query parameters.
    • In the External Data Range Properties window, the Name field sets the query name. The Query definition group is used to save the password and the query definition. The Refresh control group specifies how the imported data will be refreshed. The Formatting and layout group sets formatting parameters. The If the number of rows in the range changes group specifies how to handle changes in the size of the range. The Fill down formulas in columns adjacent to data checkbox determines whether explanatory formulas should be filled along with the data. Leave all default settings in the External Data Range Properties window and click OK.

    • The External Data Range Properties window will close. Click OK in the Import Data window.
    • The data from the selected table on the web page will be imported to the worksheet.

  • Navigating Through Hyperlinks from a List with Excel VBA

    Hyperlinks can also be used when organizing data on a worksheet. For example, you can use a list directly linked to hyperlinks in worksheet cells. As an example, consider preparing a statement called Examples of Laboratory Work), which uses a ListBox control that allows you to navigate to the required example file).

    To prepare this example, do the following:

    • Prepare the necessary example files with the .xlsm extension that will be used for hyperlink navigation, and place them in the folder.

    • Open a new workbook and prepare a statement on the worksheet according. In the range C4:C8, insert hyperlinks to the corresponding prepared example files. Save the prepared file in the folder mentioned above under the name.
    • In each of the files prepared in step 1, create a backward hyperlink to the file using the Add method to insert the hyperlink.

    Returning to the original file via hyperlink. ThisWorkbook module

    Private Sub Workbook_Open()
    Dim i As Integer
    For i = 1 To 3
        With Worksheets(i)
            .Hyperlinks.Add Anchor:=.Range("A1"), _
            Address:="D:\3-Example of using a list and hyperlinks\" & _
            "1-Examples of Laboratory Work.xlsm", _
            ScreenTip:="Return to the initial file", TextToDisplay:="BACK"
        End With
    Next
End Sub
    • Place a ListBox control over the range C4:C8 by using the Insert button located on the Developer tab in the Controls group.
    • Click the ListBox control and, in the Sheet1 module, enter the code from, which uses the Follow method to navigate via the specified hyperlink.

    Navigating via hyperlink from a list. Sheet1 module

    Private Sub ListBox1_Click()
    Hyperlinks(ListBox1.ListIndex + 1).Follow
End Sub
    • In the ThisWorkbook module, enter the code , which fills the list and creates the corresponding Hyperlink objects.

    Navigating via hyperlink from a list. ThisWorkbook module

    Private Sub Workbook_Open()
    Worksheets(1).ListBox1.ColumnCount = 2
    Worksheets(1).ListBox1.ColumnWidths = "100;0"
    Worksheets(1).ListBox1.Clear  
    Dim lst(4, 1) As String
    lst(0, 0) = "Statements" : lst(0, 1) = "2-Statements.xlsm"
    lst(1, 0) = "Charts, Surfaces, Diagrams"
    lst(1, 1) = "3-Charts_Surfaces_Diagrams.xlsm"
    lst(2, 0) = "Arrays" : lst(2, 1) = "4-Arrays.xlsm"
    lst(3, 0) = "Text Functions"
    lst(3, 1) = "5-Text Functions.xlsm"
    lst(4, 0) = "Lists" : lst(4, 1) = "6-Lists.xlsm"   
    Worksheets(1).ListBox1.List = lst   
    Dim i As Integer
    Dim r As Hyperlink  
    For Each r In Worksheets(1).Hyperlinks
        r.Delete
    Next   
    For i = 0 To 4
        Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Cells(i + 4, 3), _
        Address:=lst(i, 1), TextToDisplay:=lst(i, 0)
    Next
End Sub
    • Place the prepared folder with files on your computer’s D: drive and test that your hyperlinks work correctly.

  • The Hyperlink Object and the Hyperlinks Collection with Excel

    Hyperlinks in VBA are represented by the Hyperlink object, which is an element of the Hyperlinks collection consisting of all the hyperlinks of a worksheet or range.

    This collection has two methods: Add (to add a new hyperlink to the collection) and Delete (to delete all hyperlinks from the collection).

    Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

    • Anchor — specifies the location of the hyperlink. It can be either a Range object or a Shape object.
    • Address — the hyperlink address.
    • SubAddress — the area in the document (for example, a cell range or a bookmark) to which the hyperlink points.
    • ScreenTip — the text of the tooltip.
    • TextToDisplay — the text displayed as the hyperlink.

    In Table 1, the properties of the Hyperlink object are listed, and in Table 2, its methods are given.

    Table 1. Properties of the Hyperlink Object

    Property Description
    Address The address of the hyperlink
    EmailSubject The text string passed as the email subject
    Range Returns the Range object to which the hyperlink is assigned
    ScreenTip The text of the tooltip
    Shape Returns the Shape object to which the hyperlink is assigned
    SubAddress The area in the document to which the hyperlink points
    TextToDisplay The text displayed as the hyperlink
    Type Returns the object type to which the hyperlink is attached. Possible values: msoHyperlinkInlineShape, msoHyperlinkRange, msoHyperlinkShape

    Table 2. Methods of the Hyperlink Object

    Method Description
    AddToFavorites Add to the list of favorite links
    CreateNewDocument Create a new document associated with the specified hyperlink
    Delete Delete the hyperlink
    Follow Follow the specified hyperlink

    Example: Programmatically Creating a Hyperlink

    • First, prepare two files (save them in a macro-enabled format):
      • 1-Staff Positions and Tariff Rates.xlsm
      • 2-Salary Statement.xlsm.

    • In the ThisWorkbook module of the file , enter the following code.

    Creating a new hyperlink. ThisWorkbook module

    Private Sub Workbook_Open()
    With Worksheets(1)
        .Hyperlinks.Add Anchor:=.Range("A1"), _
        Address:="D:\2-Example of using a hyperlink\" & _
        "2-Salary Statement.xlsm", _
        ScreenTip:="NPO Alpha", TextToDisplay:="Employee Salary Calculation"
    End With
End Sub
    • In the ThisWorkbook module of the file, enter the following code .

    Creating a new hyperlink. ThisWorkbook module

    Private Sub Workbook_Open()
    With Worksheets(1)
        .Hyperlinks.Add Anchor:=.Range("D1"), _
        Address:="D:\2-Example of using a hyperlink\" & _
        "1-Staff Positions and Tariff Rates.xlsm", _
        ScreenTip:="Staff Salaries NPO Alpha", _
        TextToDisplay:="GO BACK"
    End With
End Sub
    • Make sure that when opening the file appears in cell A1 

     which leads to the file.