Professional Excel Development

Rob Bovey, Stephen Bullen, Dennis Wallentin

Mentioned 10

A guide to the development aspects of Excel covers such topics as building add-ins, creating custom charts, using class modules, handling errors, controlling external applications, and programming with databases.

More on Amazon.com

Mentioned in questions and answers.

I have been developing office solutions in VBA for a while now and have fairly complete knowledge regarding office development in VBA. I have decided it is time to learn some real programming with .Net and am having some teething problems.

Having looked through a bunch of articles and forums (here and elsewhere), there seems to be some mixed information regarding memory management in .Net when using COM objects.

Some people say I should always deterministically release COM objects and others say I should almost never do it.

People saying I should do it:

The book 'Professional Excel Development' on page 861.

This stack exchange question has been answered by saying "every reference you make to a COM object must be released. If you don't, the process will stay in memory"

This blog suggests using it solved his problems.

People saying I should not do it:

This MSDN blog by Eric Carter states "In VSTO scenarios, you typically don't ever have to use ReleaseCOMObject."

The book 'VSTO for Office 2007' which is co-authored by Eric Carter seems to make no mention whatsoever of memory management or ReleaseComObject.

This MSDN blog by Paul Harrington says don't do it.

Someone with mixed advice:

Jake Ginnivan says I should always do it on COM objects that do not leave the method scope. If a COM object leaves the method scope then forget about it. Why can't I just forget about it all the time then?

The blog by Paul Harrington seems to suggest that the advice from MS has changed sometime in the past. Is it the case that calling ReleaseCOMObject used to be best practice but is not anymore? Can I leave the finer details of memory management to MS and assume that everything will be mostly fine?

I'm sure something like this has been asked before but I guess I'm not searching the right keywords because I couldn't find a good answer.

I have created an Excel Add-In used by my entire team. I keep the most recent version on the Network drive and whenever someone re-opens Excel, the add-in checks if there is a new version and updates itself automatically.

What I'd like to do is be able to send commands to the add-ins individually to execute. For instance, if I have an important update to push, rather than waiting for each user to re-open Excel, I'd like to be able to save the command on the Network drive in a text file (i.e. "USER: ALL; COMMAND: UPDATE") and each user's add-in would automatically pick-up that command and process it within a reasonable time frame.

My question is what's the best method for accomplishing this? I can think of two solutions off the top of my head, neither of which I like.

Potential Solution #1 - In 'Worksheet_Calculate' or some similar place, have it check for new commands and process any it finds. However that seems like overkill and would potentially be checking far too often.

Potential Solution #2 - Use an infinite chain of Application.OnTime calls so that every X seconds/minutes it's checking for new central commands and will process any it finds. However I find Application.OnTime to be funky and unreliable.

Any ideas? I feel like doing something with a Class is the way to go but I don't have much experience with those.

Thanks!

OK, I ended up going with Potential Solution #1.

Code in ThisWorkbook

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    If mdtLastCheck = 0 Or DateDiff("s", mdtLastCheck, Now) > miCHECK_FREQUENCY_SECONDS Then
        mdtLastCheck = Now

        CheckForCommandsAndRun

    End If

End Sub

Code in MCentralCommands Note the only reference in this module to other modules is to a couple of global variables like gsAPP_MASTER_PATH. This code uses the MErrorHandler system from this book: Professional Excel Development.

Option Explicit

' Description:  This module contains
'
Private Const msModule As String = "MCentralCommands"

Private Const msCOMMANDS_FOLDER As String = "Commands\"
Private Const msCOMMAND_NAME_FORUSER As String = "CMD_USERNAME_*"
Private Const msCOMMAND_NAME_FORALL As String = "CMD_ALL_*"

Public Const miCHECK_FREQUENCY_SECONDS = 10
Public mdtLastCheck As Date


Sub CheckForCommandsAndRun()

' *********************************************
' Entry-Point Procedure Code Start
' *********************************************
    Const sSource As String = "CheckForCommandsAndRun"
    On Error GoTo ErrorHandler
' *********************************************
' *********************************************

    Dim sCommands() As String
    If Not bGetNewCommands(sCommands) Then Err.Raise glHANDLED_ERROR
    If Not bProcessAllCommands(sCommands) Then Err.Raise glHANDLED_ERROR

' *********************************************
' Entry-Point Procedure Code Exits
' *********************************************
ErrorExit:
    Exit Sub

ErrorHandler:
    If bCentralErrorHandler(msModule, sSource, , True) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

End Sub
Private Function bGetNewCommands(sCommands() As String) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bGetNewCommands()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandPath As String, sUser As String
    sCommandPath = gsAPP_MASTER_PATH & msCOMMANDS_FOLDER
    sUser = UCase(Application.UserName)

    Dim iCommandCount As Integer

    Dim vFile As Variant
    vFile = Dir(sCommandPath)
    While (vFile <> "")
        If vFile Like msCOMMAND_NAME_FORALL Or _
           vFile Like Replace(msCOMMAND_NAME_FORUSER, "USERNAME", sUser) Then _

            ReDim Preserve sCommands(0 To iCommandCount)
            sCommands(iCommandCount) = vFile
            iCommandCount = iCommandCount + 1

        End If

        vFile = Dir
    Wend

' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bGetNewCommands = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function
Private Function bProcessAllCommands(sCommands() As String) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bProcessAllCommands()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandPath As String, sUser As String
    sCommandPath = gsAPP_MASTER_PATH & msCOMMANDS_FOLDER
    sUser = UCase(Application.UserName)

    Dim iCmd As Integer
    For iCmd = LBound(sCommands) To UBound(sCommands)
        If Not bProcessCommand(sCommands(iCmd)) Then Err.Raise glHANDLED_ERROR
    Next

' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bProcessAllCommands = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function
Private Function bProcessCommand(sCommand As String, Optional bDeleteIfUserCmd As Boolean = True) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bProcessCommand()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandPath As String, sUser As String
    sCommandPath = gsAPP_MASTER_PATH & msCOMMANDS_FOLDER
    sUser = UCase(Application.UserName)

    Dim bHaveIRun As Boolean, bCommandSuccessful As Boolean
    If Not bHaveIRunCommand(sCommand, bHaveIRun) Then Err.Raise glHANDLED_ERROR

    If Not bHaveIRun Then

        If Not bRunCommand(sCommand, bCommandSuccessful) Then Err.Raise glHANDLED_ERROR
        If bCommandSuccessful Then
            If Not bMarkCommandAsRan(sCommand) Then Err.Raise glHANDLED_ERROR
            MLog.Log "Ran: " & sCommand
        End If

    End If

' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bProcessCommand = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function
Private Function bRunCommand(sCommand As String, bCommandSuccessful As Boolean) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bRunCommand()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandName As String
    sCommandName = Replace(Mid(sCommand, InStrRev(sCommand, "_") + 1), ".txt", "")

    Select Case UCase(sCommandName)
        Case "MSGBOX":
            Dim sMsgBoxText As String
            If Not bGetParameterFromCommand(sCommand, "Msg", sMsgBoxText) Then Err.Raise glHANDLED_ERROR
            MsgBox sMsgBoxText
            bCommandSuccessful = True

        Case "UPDATE":
            CheckForUpdates False
            bCommandSuccessful = True

        Case "OLFLDRS":
            UpdateSavedOutlookFolderList
            bCommandSuccessful = True

    End Select



' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bRunCommand = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function
Private Function bGetParameterFromCommand(sCommand As String, sParameterName As String, sParameterReturn As String) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bGetParameterFromCommand()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandPath As String, sUser As String
    sCommandPath = gsAPP_MASTER_PATH & msCOMMANDS_FOLDER
    sUser = UCase(Application.UserName)

    Dim sFilePath As String, sParameterText() As String, sTextLine As String
    Dim iLineCount As Integer
    sFilePath = sCommandPath & sCommand

    Dim bBegin As Boolean

    Open sFilePath For Input As #1
    Do Until EOF(1)
        Line Input #1, sTextLine

        If bBegin Then If Left(sTextLine, 1) = ":" Then bBegin = False
        If sTextLine Like "*:Parameters:*" Then
            bBegin = True
        End If

        If bBegin Then
            ReDim Preserve sParameterText(0 To iLineCount)
            sParameterText(iLineCount) = sTextLine
            iLineCount = iLineCount + 1
        End If
    Loop
    Close #1

    Dim iParameterCounter As Integer
    For iParameterCounter = LBound(sParameterText) To UBound(sParameterText)
        If sParameterText(iParameterCounter) Like sParameterName & ": *" Then _
            sParameterReturn = Mid(sParameterText(iParameterCounter), InStr(1, sParameterText(iParameterCounter), " ") + 1)
    Next


' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bGetParameterFromCommand = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function
Private Function bHaveIRunCommand(sCommand As String, bHaveIRun As Boolean) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bHaveIRunCommand()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandPath As String, sUser As String
    sCommandPath = gsAPP_MASTER_PATH & msCOMMANDS_FOLDER
    sUser = UCase(Application.UserName)

    Dim sFile As String, sText As String, sTextLine As String
    sFile = sCommandPath & sCommand

    Dim bBegin As Boolean

    Open sFile For Input As #1
    Do Until EOF(1)
        Line Input #1, sTextLine

        If bBegin Then If Left(sTextLine, 1) = ":" Then bBegin = False
        If sTextLine Like "*:Run By Users:*" Then bBegin = True

        If bBegin Then
            sText = sText & sTextLine
        End If
    Loop
    Close #1

    bHaveIRun = sText Like "*" & sUser & "*"

' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bHaveIRunCommand = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function
Private Function bMarkCommandAsRan(sCommand As String) As Boolean

' *********************************************
' **** Function Code Start
' *********************************************
    Dim bReturn As Boolean
    Const sSource As String = "bMarkCommandAsRan()"

    On Error GoTo ErrorHandler
    bReturn = True
' *********************************************
' *********************************************

    Dim sCommandPath As String, sUser As String
    sCommandPath = gsAPP_MASTER_PATH & msCOMMANDS_FOLDER
    sUser = UCase(Application.UserName)

    Dim sFilePath As String, sRanText As String, sTextLine As String, bHaveIRun As Boolean
    Dim sFullText() As String, iLineCount As Integer, iRunBy As Integer
    sFilePath = sCommandPath & sCommand

    Dim bBegin As Boolean

    Open sFilePath For Input As #1
    Do Until EOF(1)
        Line Input #1, sTextLine

        ReDim Preserve sFullText(0 To iLineCount)
        sFullText(iLineCount) = sTextLine
        iLineCount = iLineCount + 1

        If bBegin Then If Left(sTextLine, 1) = ":" Then bBegin = False
        If sTextLine Like "*:Run By Users:*" Then
            bBegin = True
            iRunBy = iLineCount - 1
        End If

        If bBegin Then
            sRanText = sRanText & sTextLine
        End If
    Loop
    Close #1

    bHaveIRun = sRanText Like "*" & sUser & "*"

    If Not bHaveIRun Then
        Dim iCounter As Integer

        Open sFilePath For Output As #1
        For iLineCount = LBound(sFullText) To UBound(sFullText)
            Print #1, sFullText(iLineCount)
            If iLineCount = iRunBy Then _
                Print #1, sUser
        Next
        Close #1
    End If


' *********************************************
' Function Code Exits
' *********************************************
ErrorExit:
    bMarkCommandAsRan = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msModule, sSource) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If


End Function

Is there a way to do global error handling?

Can I put some code in the Workbook code that will catch any errors that occur within all modules?

I could put the same error handler in each module but I'm looking for something more general.

I ask because I have sheet names that are stored as global variables like this Sheets(QuoteName). If there is an error then these global variables are lost. I have a macro that will rename the global variables but I put this within Workbook_BeforeSave.

I want it to go to the global error handler and rename the global variable if I get a Subscript out of range error for Sheets(QuoteName)

As Sid already mentioned in the comment, there is no central error handler.

Best practice is to have a central error handling routine that gets called from the local error handlers. Take a look at the great MZ-Tools: it has the possibility to define a default error handler at the press of a button (Ctrl-E). You can customize this error handler - and it can also contain module and/or sub name!

Additionally, check out this post at Daily Dose of Excel. It is Dick Kusleika's OO version of the error handler proposed in this book (which I can highly recommend).

I worked somewhere before where they had an xll that allowed one to track the amount of memory being used in Excel. Also when calls were made to other dlls and xlls these were logged. This was all spewed out to a logfile based on the Excel PID. There was then a solution that could be used to read the log into a format that could be used in a Pivot Table.

I have googled for nearly an hour coming at it from all angles in search terms, but I am just not having any luck. I cannot remember the provider but I am pretty sure it wasn't in house.

Hope people can help.

No responses. Oh well.

In the end I managed to find the answer. It is a utility called Performance Monitor. It is talked about here :

http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/

It is from the book Professional Excel Development :

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET: The Definitive Guide to ... and VBA (Addison-Wesley Microsoft Technology)

I hope this helps future people searching for this.

I am Generating a New Sheets using macros. For a New Sheet generation , Data is retrieved from more than 4 MS Access DB. Each DB had minimum 200 field. My Macro code includes

  1. Cell locking
  2. Alignment and formatting
  3. One third of the cells in the sheet had a formulas
  4. Cell reference with other Workbooks

My problem is every sheet generation it takes minimum one hour to complete the hole process. But it seems to me it's taking way too long.

I am already added the Application.ScreenUpdating = True to speed up the code but still it takes same time. How to do speed up the code , If you have any idea please guide me.

     `For Ip = 5 To  150
     resp = Range("B" & Ip).Value
     With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DSN=henkel2;DBQ=C:\Hl-RF\RSF-Temp.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;" _
    , Destination:=Range("IV4"))
    .CommandText = "select Vles from " & Shtname & " where cint(PrductID)='" & resp & "' and cint(DepotID) = '" & cnt1 & "' and Mnth = '" & mnths & "' and Type='" & typs & "'"
    .Name = "tab product"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceConnectionFile = _
    "C:\Hl-RF\tabct.odc"
    .Refresh BackgroundQuery:=False
    End With`


    Is There Is any way to Reduce the loop iteration time

Thanks In advance

Get hold of a copy of Professional Excel Development which includes an excellent profiling utility called PerfMon. It will allow you to see which parts of the report are taking all the time so you can analyse and rewrite

I've call a stored procedure (SQL Server 2005) in Excel 2007. This SP has two select statements, however in excel worksheet, result of first statement is showing. Any thoughts?

If your stored procedure has two select statements, you will have two recordsets returned. Make sure you are writing both to the worksheet. You should also verify that both select statements are returning rows.

NOTE: If you'd like to try this using VBA, chapter 19 of the book Professional Excel Development is devoted to interacting with SQL Server. The chapter walks you through connecting to the database, executing stored procedures, and writing the results back to your workbook. All of the code you need is provided, along with good explanations of what it does. And if you're new to VBA, this book is on the short-list of ones you should pick up. Check out good books to learn Excel VBA for others.

Here is an example of part of a sub that returns 5 record sets from a stored procedure:

     If Not rsData.EOF Then

        ' The first recordset contains prices
        Sheet39.Range("a1").CopyFromRecordset rsData
        Set rsData = rsData.NextRecordset

        ' The second recordset contains products
        Sheet39.Range("a20").CopyFromRecordset rsData
        Set rsData = rsData.NextRecordset

        ' The third recordset contains stores
        Sheet39.Range("a35").CopyFromRecordset rsData
        Set rsData = rsData.NextRecordset

        ' The fourth recordset contains payment types
        Sheet39.Range("j1").CopyFromRecordset rsData
        Set rsData = rsData.NextRecordset

        ' The fifth recordset contains customers
        Sheet39.Range("j8").CopyFromRecordset rsData
        Set rsData = rsData.NextRecordset

    Else
        MsgBox "No data located.", vbCritical, "Error!"
    End If

So I'm having an issue where any long string I try to set as a SQL query returns an error when trying to copy from the recordset into Excel

For example, if i use a query like this

select 
cdf.DAY_STAMP Day, b.ACCOUNT_NUMBER Acct#, cdf.CM_DESC MAC, 
b.STREET_NUMBER St#, b.STREET_NAME StName, b.CITY City,
b.TRANSPORT_ELEMENT_2 census,
i.ifalias node,
cdf.SUM_BYTES_UP, cdf.SUM_BYTES_DOWN, cdf.SUM_RESET_COUNT, cdf.AVG_TXPOWER_UP,
cdf.MAX_TXPOWER_UP, cdf.MIN_TXPOWER_UP, cdf.AVG_RXPOWER_DOWN, cdf.MAX_RXPOWER_DOWN,
cdf.MIN_RXPOWER_DOWN, cdf.AVG_RXPOWER_UP, cdf.MAX_RXPOWER_UP, cdf.MIN_RXPOWER_UP,
cdf.AVG_PATH_LOSS_UP, cdf.AVG_CER_DOWN, cdf.MAX_CER_DOWN, cdf.MIN_CER_DOWN,
cdf.AVG_CCER_DOWN, cdf.MAX_CCER_DOWN, cdf.MIN_CCER_DOWN, cdf.AVG_SNR_DOWN,
cdf.MAX_SNR_DOWN, cdf.MIN_SNR_DOWN, cdf.US_CER_MAX, cdf.US_CCER_MAX, cdf.US_SNR_MIN,
cdf.STATUS_VALUE_MIN, cdf.TIMING_OFFSET_LAST, cdf.ROWCOUNT, cdf.T3_TIMEOUTS,
cdf.T4_TIMEOUTS, cdf.SYSUPTIME
from cm_day_facts cdf
inner join int_attributes i 
on i.ifalias like '72F007%' and i.topologyid = cdf.up_id
inner join billing_data b
on b.equipment_mac = cdf.cm_desc
where cdf.DAY_stamp >= '12-MAY-12 00:00'

I get an ERROR: Operation is not allowed when the object is closed

But if I use a query like: Select * from "DWRVWR"."CSOC_M_WIPMASTER"

I get a valid response with no issues.

.

Full code:

Sub CableData_SQLconn()

'Connect to Oracle server begin
Set sqlCon = New ADODB.Connection
Set sqlCommand = New ADODB.Command
Set sqlRecordSet = New ADODB.Recordset
Dim Conn As String
Dim sqlQuery As String
Dim sqlQuery2 As String
Dim sqlQuery3 As String

'grab node from user input box
node = InputBox("Node")

'Connection string
Conn = "Provider=MSDASQL; Driver={Microsoft ODBC for Oracle}; " & _
    "CONNECTSTRING=(DESCRIPTION=" & "(ADDRESS=(PROTOCOL=TCP)" & _ 
    "(HOST=myhost)(PORT=myport))" & "(CONNECT_DATA=(SID=mysid))); uid=user; pwd=pass;"

'Build SQL Query
sqlQuery = "A REALLY LONG STRING"
sqlQuery2 = sqlQuery & "A REALLY LONG STRING"
sqlQuery3 = sqlQuery2 & "A REALLY LONG STRING"

'This output is confirmed to have the full query - **VERIFIED OUTPUT IS CORRECT**
Range("A1").Select
ActiveCell.FormulaR1C1 = sqlQuery3

'---------------------------------------------------------------
' Set file details for SQL query
fileDir = "C:\temp\"
filePath = "C:\temp\" & node & "_SRO_TCs.sql"

'check if directory exists, if not create it
If Dir(fileDir, cbDirectory) = "" Then
MkDir fileDir
End If

' open the file output
Open filePath For Output As #1

'Write full SQL query to file - VERIFIED
outputText = sqlQuery3
Print #1, outputText
'-----------------------------------------------------------    


'open connection
sqlCon.ConnectionString = Conn
  'Cn.CursorLocation = adUseClient
sqlCon.Open

'set and execute sql command
Set sqlCommand.ActiveConnection = sqlCon
sqlCommand.CommandText = sqlQuery3
sqlCommand.CommandType = adCmdText
sqlCommand.Execute sqlQuery3

'open recordset
Set sqlRecordSet.ActiveConnection = sqlCon
sqlRecordSet.Open sqlCommand

'copy data to excel
'ActiveSheet.Range("A1").CopyFromRecordset (sqlRecordSet)
If Not sqlRecordSet.EOF Then    '<<<<<<<<<<<<<<<<<<<<<<<<<<< ERROR: Operation is not allowed when the object is closed
    ActiveSheet.Range("A1").CopyFromRecordset sqlRecordSet
Else: MsgBox "No records returned!"
End If

'close connections
sqlRecordSet.Close
sqlCon.Close

'Close file
Close #1

End Sub

Convert your query to a stored procedure with whatever parameters you want, then call the stored procedure from Excel and paste the results in your workbook. In the example I've listed below, I'm connecting to SQL Server, but it will work the same if you are connecting to Oracle.

Here's some code you can modify to work with your application:

    Public Sub RunStoredProcedureFromExcel()
    Dim rsData As ADODB.Recordset

    Dim ProductID, CategoryID, StoreNumber As Integer
    ProductID = Sheets("Variables").Range("nrProductID").Value
    CategoryID = Sheets("Variables").Range("nrCategoryID").Value
    StoreNumber = Sheets("Variables").Range("nrSelectedStore").Value

    ' Clear the destination worksheet
    Sheet39.UsedRange.Clear

    ' Set the ADODB Connection
    ConnectToSQLServer

    ' Create the Recordset object.
    Set rsData = New ADODB.Recordset

    ' Open the pooled connection
    mcnSQLServer.Open
    mcnSQLServer.espGetLookupData ProductID, CategoryID, StoreNumber, rsData

    If Not rsData.EOF Then

        ' Paste the results into workbook
        Sheet39.Range("a1").CopyFromRecordset rsData

    Else
        MsgBox "No data located.", vbCritical, "Error!"
    End If

    ' Close the pooled connection
    mcnSQLServer.Close

    End Sub

There is a great chapter on this topic in the book Professional Excel Development which was the starting point for the code above.

Please use the synonym tag .

Visual Basic for Applications (VBA) is an event-driven programming language which was first introduced by Microsoft in 1993 to give Excel 5.0 a more robust object-oriented language for writing macros and automating the use of Excel. The language and its runtime quickly matured and began being used in products beyond Microsoft Office applications.

VBA 6, was shipped in 1998 and includes a myriad of licensed hosts, among them: Office 2000 - 2010, AutoCAD, PI Processbook, and the stand-alone Visual Basic 6.0. VBA 6 code will run equally well on any host, though the underlying objects native to each host will vary. Though still built into Microsoft Office applications, VBA ceased to be an integral of part of Microsoft's development platform when Visual Basic .NET shipped with the first version of the .NET framework in 2002. Additionally, in some Starter editions of Office applications, the VBA Editor (VBE) is not available, and programming in VBA cannot be done.

VBA has been rarely updated since, only including new features to allow it to remain compatible with x64 versions of Windows and Office. VBA 7 was released in 2010 to address the new 64-bit version of Microsoft Office, which shipped in 2010. There are several important changes made to VBA 7 that make it different from VBA 6, namely compatibility with both 32 and 64-bit versions of Office. Applications using VBA 7 must address both backwards compatibility and 64-bit-safe issues.

It was removed from Office for Mac 2008, however Microsoft returned VBA to Office 2011. Microsoft has continually been questioned about whether or not VBA will be removed altogether from Office and has repeatedly replied "no".

VBA inherits much of its syntax from the BASIC programming language, where language features tend to be explicitly expressed in words (e.g. If ... Then ... End If, Function ... End Function). It also has many object-oriented features (such as classes and interfaces) and even has some dynamic features (Variant). Below is a simple subroutine, which generates a message box and prints a message to the Immediate window:

Sub HelloWorld()
  ' This is a comment
  Dim strMessage As String
  strMessage = "Hello World"
  MsgBox strMessage
  Debug.Print "I just made a message box that says """ & strMessage & """!"
End Sub

It is expected that questions tagged should either contain VBA code or ask how to use specific VBA code. Also, while code written in VBA is typically called a 'macro', the tag should not be used for general VBA questions. Please see the wiki entry page for more information about correct usage.

If your code is specific to an Office application (Excel, Word, Outlook, etc.) then tag it with one of the application-specific tags found below, in addition to this one.

Related Tags:

Frequently Asked Questions:

Beginner Resources:

References:

Additional Reading:

General VBA Information and History:

VBA (Visual Basic for Applications) was introduced in Excel 5.0 in 1993 as an alternative to the Excel version 4.0 macro language (XLM). While XLM is currently still supported in later Excel versions (including Excel 2013), VBA is overwhelmingly the dominant language.

Microsoft has been encouraging users to migrate XLM macros to VBA. With Office 2010, they have added new features to make this process easier. A partial migration roadmap may be found on the official Excel blog.

VBA is an object-oriented version of Basic derived from VB6, it provides complete control of the Office environment, anything that can be done by the user in Office can be done with VBA code. Automation of charts, pivot tables, data entry, filtering and workbook creation are just some examples. Hence there's more to VBA than simply recording a macro. But you can use macro recording as a tool to help you find out certain properties, worksheet methods available in VBA IDE.

Almost all versions of Office include a comprehensive and reasonably modern IDE for Visual Basic including a code editor, dialog editor, object browser, and a source debugger. However, in some Starter editions, the VBA Editor (VBE) is not available, and programming in VBA cannot be done.

Getting Started:

In addition to reading the links section below, a good way to get started is to use the Macro Recorder within Excel as follows:

  1. Start recording (Developer->Code->Record Macro). If the developer-tab is not visible, you can show it by going to File->Options->Customize Ribbon, and checking "Developer" in the window on the right.
  2. Manually perform the operations that you want to automate
  3. Stop recording
  4. Analyze the generated code - you'll get hints on the data object model (how to get access on worksheets, ranges, cells) and on common operations on the data.
  5. Write your own code based on these hints ;
  6. Debug : use the debugger (set breakpoints to break execution - a break-point is set by clicking on a code-line until it gets red) ; use the Immediate window to output debug data with Debug.Print-statements. Use Watches to dig into the data model.

Related Tags :

Links:

Reference Books

Visual Basic for Applications (VBA) is an event-driven programming language which was first introduced by Microsoft in 1993 to give Excel 5.0 a more robust object-oriented language for writing macros and automating the use of Excel. It was introduced to Access, PowerPoint and Word in Office 97. The language and its runtime quickly matured and began being licensed and used in products beyond Microsoft Office applications.

Tag usage

Overview

VBA derives from the classic Visual Basic programming language (version 5.0 and later 6.0) and uses a subset of that language's functionality. It is, however, totally dependent on the host application interface for execution and cannot run outside that environment. Unlike classic VB, VBA cannot create independent executables or DLLs. Visual Basic Libraries for interaction with Windows and its services (such as Printers or the Clipboard) are not part of VBA.

VBA 6 was shipped in 1998 and includes a myriad of licensed hosts, among them: Office 2000 - 2007, AutoCAD and PI Processbook. VBA 6 code will run equally well on any host, though the underlying objects native to each host will vary. Though VBA is still built into Microsoft Office applications, its "parent", classic VB, ceased to be an integral of part of Microsoft's development platform when Visual Basic .NET shipped with the first version of the .NET framework in 2002.

VBA has been rarely updated since, only including new features to allow it to remain compatible with x64 versions of Windows and Office. Its application-specific object models are regularly supplemented to support new functionality added to the host Office applilcations. VBA 7 was released in 2010 to address the new 64-bit version of Microsoft Office, which shipped in 2010. There are several important changes made to VBA 7 that make it different from VBA 6, namely compatibility with both 32 and 64-bit versions of Office. Applications using VBA 7 must address both backwards compatibility and 64-bit-safe issues.

In some Starter editions of Office applications, the VBA Editor (VBE) is not available, and programming in VBA cannot be done. VBA support can also be locked out (not installed) by network administrators as a security measure.

It was removed from Office for Mac 2008, however Microsoft returned VBA to Office 2011. Microsoft has continually been questioned about whether or not VBA will be removed altogether from Office and has repeatedly replied "no", the main reason being the large amount of legacy code in use.

This still holds true, even after the introduction of the JavaScript APIs in Office 2013, although VBA is supported only in the desktop application versions of Office.

VBA inherits much of its syntax from the BASIC programming language, where language features tend to be explicitly expressed in words (e.g. If ... Then ... End If, Function ... End Function). It also has many object-oriented features (such as classes and interfaces) and even has some dynamic features (Variant). Below is a simple subroutine, which generates a message box and prints a message to the Immediate window:

Sub HelloWorld()
  ' This is a comment
  Dim strMessage As String
  strMessage = "Hello World"
  MsgBox strMessage
  Debug.Print "I just made a message box that says """ & strMessage & """!"
End Sub

It is expected that questions tagged should either contain VBA code or ask how to use specific VBA code. Also, while code written in VBA is typically called a 'macro', the tag should not be used for general VBA questions. Please see the wiki entry page for more information about correct usage.

If your code is specific to an Office application (Excel, Word, Outlook, etc.) then tag it with one of the application-specific tags found below, in addition to this one.

Related Tags:

Frequently Asked Questions:

Beginner Resources:

References:

Additional Reading:

General VBA Information and History: