Title: VBA – Back to basics

Issue: Spectrum Mar/Apr '99

Author: Nathan Rector

Company: Natec Systems

Email: nater@northcoast.com

http: www.northcoast.com/~nater/

There are several questions that get asked repetitively, in both my training classes and in email. Two of the most common are: How do I make use of a VBA Macro in an Excel cell, and how do I include and use the objects such as Winlink/Objects, D3/Objects, or UVObjects. Most of the other questions have to do with the basics of using Visual Basic for Applications, so in this article we are going to go back to the basics.

Let’s start with how to create a Macro. Macros can be created two different ways. One is using the Macro Recorder found on the Tools | Macros | Record New Macro . The other is creating the Macro by hand as if you were writing a Visual Basic program.

Let me talk about Macro Recorder first. If you use the Macro Recorder it will record each mouse click and menu option that you perform in your spreadsheet or document. Each step or action is then rendered into VBA code that can be edited or added to.

Please note that the code that the Macro Recorder creates will not always be the best way to perform that specific function. The code will always work, but it may not be the fastest and it will remember every command you did, even your mistakes. For example, the Macro Recorder created the following code:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 4/23/99 by Nathan Rector

'

Range("B5").Select

ActiveCell.FormulaR1C1 = "1"

Range("B6").Select

ActiveCell.FormulaR1C1 = "2"

Range("B7").Select

ActiveCell.FormulaR1C1 = "3"

Range("B8").Select

ActiveCell.FormulaR1C1 = "4"

Range("C5").Select

ActiveCell.FormulaR1C1 = "3"

Range("C6").Select

End Sub

Now heres the same code that I have optimized:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 4/23/99 by Nathan Rector

'

'

Range("B5").FormulaR1C1 = "1"

Range("B6").FormulaR1C1 = "2"

Range("B7").FormulaR1C1 = "3"

Range("B8").FormulaR1C1 = "4"

Range("C5").FormulaR1C1 = "3"

End Sub

As you can see, I’ve altered the code that the Macro Recorder has be generated. Now the question is where do I go to do the alterations. The location of the VBA development environment can be found under the Tools | Macros | Visual Basic Editor. This will take you into the development environment. The Macro that you just recorded should be under the Modules section.

Ok, so the Macro Recorder can create a macro that will repeat specific steps and actions that effect the spreadsheet, but now I want to create a VBA program that sets a value in a specific cell. This is no harder than it was to edit an existing Macro.

The first step is to go directly into the development environment and select one of the Modules in the list. If there is no Modules sections, the new module will need to be created. This can be done from the development environment by using the Insert | Module menu.

Now we want to create a new Function. We use a function instead of a Method, or Sub, because we are returning a value. Even though we can use a Method to return a value, it’s easier to use a Function to return a value into the same cell the Function has be included in. I’ll get to how to add the function to the cell in a minute.

Let’s just create a simple function to show how it’s done:

Function GetTestValue()

‘ returns the value of B5 / B6

GetTestValue = Range("B5").FormulaR1C1 / Range("B6").FormulaR1C1

End Function

Now, let’s place the macro in the Cell. First return to the Excel spreadsheet and select the cell you want the macro to be part of. Click on the cell and instead of input a value, type ‘=GetTestValue’. That is all there is to it.

Now the big question is how to do I include the Objects that access your database. Let’s return to the development environment. Under the Tools | References menu, a dialog box will be displayed showing all the objects and DLLs that you can include in your VBA Code.  Select the set of Objects you want to reference.

Once you have the object referenced, you can now create code that accesses your database. The following sample is using Winlink/Objects, but any of the other object can be used without problem.

Function GetTestValue()

Dim wlBase As New WinLink.BaseObject

Dim wlLink As WinLink.Link

Dim wlItem As WinLink.DelimitedString

Dim wlCustFile As WinLink.RemoteFile

' creates the link to the WinLink Host and opens Order File

Set wlLink = wlBase.Links.OpenLink("Default")

Set wlCustFile = wlLink.RemoteFiles.OpenFile("CUSTOMERS")

' Reads the Data and adds data

Set wlItem = wlCustFile.read("5912")

' divides attribute 1 by attribute 2 of the customer file

GetTestValue = Cint(wlItem.Extract(1).Text) / Cint(wlItem.Extract(2).Text)

' Destroys objects

Set wlCustFile = Nothing

Set wlLink = Nothing

Set wlBase = Nothing

End Function

Title: VBA – Back to basics

Issue: Spectrum Mar/Apr '99

Author: Nathan Rector

Company: Natec Systems

Email: nater@northcoast.com

http: www.northcoast.com/~nater/

There are several questions that get asked repetitively, in both my training classes and in email. Two of the most common are: How do I make use of a VBA Macro in an Excel cell, and how do I include and use the objects such as Winlink/Objects, D3/Objects, or UVObjects. Most of the other questions have to do with the basics of using Visual Basic for Applications, so in this article we are going to go back to the basics.

Let’s start with how to create a Macro. Macros can be created two different ways. One is using the Macro Recorder found on the Tools | Macros | Record New Macro . The other is creating the Macro by hand as if you were writing a Visual Basic program.

Let me talk about Macro Recorder first. If you use the Macro Recorder it will record each mouse click and menu option that you perform in your spreadsheet or document. Each step or action is then rendered into VBA code that can be edited or added to.

Please note that the code that the Macro Recorder creates will not always be the best way to perform that specific function. The code will always work, but it may not be the fastest and it will remember every command you did, even your mistakes. For example, the Macro Recorder created the following code:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 4/23/99 by Nathan Rector

'

Range("B5").Select

ActiveCell.FormulaR1C1 = "1"

Range("B6").Select

ActiveCell.FormulaR1C1 = "2"

Range("B7").Select

ActiveCell.FormulaR1C1 = "3"

Range("B8").Select

ActiveCell.FormulaR1C1 = "4"

Range("C5").Select

ActiveCell.FormulaR1C1 = "3"

Range("C6").Select

End Sub

Now heres the same code that I have optimized:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 4/23/99 by Nathan Rector

'

'

Range("B5").FormulaR1C1 = "1"

Range("B6").FormulaR1C1 = "2"

Range("B7").FormulaR1C1 = "3"

Range("B8").FormulaR1C1 = "4"

Range("C5").FormulaR1C1 = "3"

End Sub

As you can see, I’ve altered the code that the Macro Recorder has be generated. Now the question is where do I go to do the alterations. The location of the VBA development environment can be found under the Tools | Macros | Visual Basic Editor. This will take you into the development environment. The Macro that you just recorded should be under the Modules section.

Ok, so the Macro Recorder can create a macro that will repeat specific steps and actions that effect the spreadsheet, but now I want to create a VBA program that sets a value in a specific cell. This is no harder than it was to edit an existing Macro.

The first step is to go directly into the development environment and select one of the Modules in the list. If there is no Modules sections, the new module will need to be created. This can be done from the development environment by using the Insert | Module menu.

Now we want to create a new Function. We use a function instead of a Method, or Sub, because we are returning a value. Even though we can use a Method to return a value, it’s easier to use a Function to return a value into the same cell the Function has be included in. I’ll get to how to add the function to the cell in a minute.

Let’s just create a simple function to show how it’s done:

Function GetTestValue()

‘ returns the value of B5 / B6

GetTestValue = Range("B5").FormulaR1C1 / Range("B6").FormulaR1C1

End Function

Now, let’s place the macro in the Cell. First return to the Excel spreadsheet and select the cell you want the macro to be part of. Click on the cell and instead of input a value, type ‘=GetTestValue’. That is all there is to it.

Now the big question is how to do I include the Objects that access your database. Let’s return to the development environment. Under the Tools | References menu, a dialog box will be displayed showing all the objects and DLLs that you can include in your VBA Code.  Select the set of Objects you want to reference.

Once you have the object referenced, you can now create code that accesses your database. The following sample is using Winlink/Objects, but any of the other object can be used without problem.

Function GetTestValue()

Dim wlBase As New WinLink.BaseObject

Dim wlLink As WinLink.Link

Dim wlItem As WinLink.DelimitedString

Dim wlCustFile As WinLink.RemoteFile

' creates the link to the WinLink Host and opens Order File

Set wlLink = wlBase.Links.OpenLink("Default")

Set wlCustFile = wlLink.RemoteFiles.OpenFile("CUSTOMERS")

' Reads the Data and adds data

Set wlItem = wlCustFile.read("5912")

' divides attribute 1 by attribute 2 of the customer file

GetTestValue = Cint(wlItem.Extract(1).Text) / Cint(wlItem.Extract(2).Text)

' Destroys objects

Set wlCustFile = Nothing

Set wlLink = Nothing

Set wlBase = Nothing

End Function