Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)-VBForums
Results 1 to 15 of 15

Thread: Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)

    Do you want to create or read an Excel file from your program?

    Perhaps you want to create a report with some data, a few formulas, and some graphs, or maybe get some particular data out of a spreadsheet?

    The good news is that Excel has been designed to let you do all of this and more; you can treat it like other objects that you would use from VB, and you can perform programmatically all of the tasks that you can manually. In the rest of this tutorial you will find out how.

    Please note that you must have Excel installed to do any of this, and so must your users!


    Note that as an alternative to automation you can use an Excel file as a database, which does not require Excel to be installed - however it is not as easy to use as other database systems, due to unusual SQL syntax (eg: "SELECT * from `Sheet1$A1:`"). This method is not discussed in this document, please search the Database Development forum for previously asked questions which cover this.



    Contents
    2 - In the beginning - Adding a Reference to Excel
    3 - Opening Excel
    4 - Creating/opening a Workbook
    5 - Preparing to work with data (setting and closing App/Book/Sheet references)
    6 - Reading and writing data
    7 - Basic formatting
    8 - Finding "special" cells (bottom row etc)
    9 - Installation Issues
    10 - Using a Late-Bound connection to Excel
    11 - "Excel doesn't close"
    12 - How do I write code to … ? [a.k.a. recording macro's]
    13 - Useful functions and downloads
    14 - Frequently Asked Questions


    .
    Share on Google+

  2. #2

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    In the beginning - Adding a Reference to Excel

    To start your journey into the world of automating Excel, you first need to tell your program that you want to reference Excel, to do this:

    Go to the "Project" menu, and select "References". You will be presented with a long list of available references, just scroll down to "Microsoft Excel X.X Object Library" (where X.X is a version number - see post #14 for a list), then tick it and press OK.

    NB: if it isn't in the list, there is probably an error with your installation of Excel. You can try to select the reference file manually by clicking the "browse" button, the file you need it is likely to be called something like "ExcelX.olb" (for Excel XP or later it seems to be "Excel.exe" instead).


    As an aside, if you are releasing your software to various users then having a reference isn't the method you should use, but don't worry about it for now (this is the method you should use whilst developing your software, as it provides auto-complete features and Help while you are writing your code).


    .
    Share on Google+

  3. #3

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Opening Excel

    Once you have a reference set up, you can create an instance of Excel. To do this you declare an object variable with a data type of Excel.Application, and then you can do whatever you want with it, before closing/disconnecting from it.

    In this simple example we will start a new instance of an Excel application, display it, and then disconnect from it. You can copy this code and run it as it is:
    VB Code:
    1. Dim oXLApp as Excel.Application       'Declare the object variable
    2.  
    3.   Set oXLApp = New Excel.Application  'Create a new instance of Excel
    4.  
    5.   oXLApp.Visible = True               'Show it to the user
    6.  
    7.   Set oXLApp = Nothing                'Disconnect from Excel (let the user take over)

    NB: The final line is something that several people forget in this situation, but it is important - without it your program will take up memory which it isn't actually using, and after this part of your program has been run several times your computer may crash.


    You can connect to an already open Excel application instead of opening a new one (using GetObject - as we will see later), but for now it is better to just create a new instance, as it is more reliable and less confusing.

    You can also use CreateObject as a variation to "New ..", but we will also get to this later (post #10).


    .
    Last edited by si_the_geek; Aug 19th, 2006 at 11:23 AM.
    Share on Google+

  4. #4

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Creating/opening a Workbook

    The example in the previous section was very basic, and to be honest pretty pointless too. In order to actually do anything you need to have a Workbook to interact with.

    There are times when you want to create a new Workbook, and times when you want to work with an existing one.

    To create a new Workbook:
    VB Code:
    1. Dim oXLApp as Excel.Application       'Declare the object variables
    2. Dim oXLBook as Excel.Workbook      
    3.  
    4.   Set oXLApp = New Excel.Application  'Create a new instance of Excel
    5.  
    6.   Set oXLBook = oXLApp.Workbooks.Add  'Add a new workbook
    7.  
    8.   oXLApp.Visible = True               'Show it to the user
    9.  
    10.   Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
    11.   Set oXLApp = Nothing

    To open an existing workbook, just change the " Set oXLBook =" line in the above example to this:
    VB Code:
    1. Set oXLBook = oXLApp.Workbooks.Open("c:\my folder\my workbook.xls") 'Open an existing workbook

    By default a new Workbook has the number of sheets which are specified in the users options. You can set this option via code, but should change it back afterwards (else the user will have the option that you set). To do this you need a few extra lines around the "'Add a new workbook" line:
    VB Code:
    1.   Set oXLApp = New Excel.Application  'Create a new instance of Excel
    2.  
    3. Dim iSheetsPerBook as Integer         'Add a new workbook (with one sheet)
    4.   iSheetsPerBook = oXLApp.SheetsInNewWorkbook
    5.   oXLApp.SheetsInNewWorkbook = 1
    6.   Set oXLBook = oXLApp.Workbooks.Add  
    7.   oXLApp.SheetsInNewWorkbook = iSheetsPerBook
    8.  
    9.   oXLApp.Visible = True               'Show it to the user

    .
    Share on Google+

  5. #5

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Preparing to work with data (setting and closing App/Book/Sheet references)

    So far, we have got as far as having a Workbook open within the Excel application, which is getting better but doesn't let us do what we want - interact with the data!

    For those of you who have experience in this area, you will hopefully know already that we are missing a vital ingredient in our examples so far - the Worksheet. All data in a workbook is held in specific worksheets, so we should be using them.

    Now it is time to start with the proper examples. There are a few different ways of modifying/reading data, but all of them start with the same basic block of code, which we will call "Part A":
    VB Code:
    1. Dim oXLApp as Excel.Application         'Declare the object variables
    2. Dim oXLBook as Excel.Workbook
    3. Dim oXLSheet as Excel.Worksheet
    4.  
    5.   Set oXLApp = New Excel.Application    'Create a new instance of Excel
    6.   Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
    7.   Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
    Note that you could open an existing workbook instead of creating a new one, by using the code in the previous post.

    This should be followed by some sort of modification/reading code, which we will come to in the next section - the important part for now is to finish properly, because if you don't then you won't see any of the changes you have made!

    When you have finished working with the data you can show, save, save as, or just close the workbook. There are different methods for each of these options, all of which we will call "Part B", which one of these you use is up to you (and will probably vary depending on the project you are writing at the time).

    Part B(1) - Show the workbook: (best while you are writing or testing!)
    VB Code:
    1. oXLApp.Visible = True                'Show it to the user
    2.   Set oXLSheet = Nothing               'Disconnect from all Excel objects (let the user take over)
    3.   Set oXLBook = Nothing
    4.   Set oXLApp = Nothing
    or, Part B(2) - Save the existing workbook you opened:
    VB Code:
    1. Set oXLSheet = Nothing             'disconnect from the Worksheet
    2.   oXLBook.Close SaveChanges:= True   'Save (and disconnect from) the Workbook
    3.   Set oXLBook = Nothing
    4.   oXLApp.Quit                        'Close (and disconnect from) Excel
    5.   Set oXLApp = Nothing
    or, Part B(3) - Save the workbook to a new file:
    VB Code:
    1. Set oXLSheet = Nothing                        'disconnect from the Worksheet
    2.   oXLBook.SaveAs "C:\My Documents\My File.xls"  'Save (and disconnect from) the Workbook
    3.   oXLBook.Close SaveChanges:= False
    4.   Set oXLBook = Nothing
    5.   oXLApp.Quit                                   'Close (and disconnect from) Excel
    6.   Set oXLApp = Nothing
    or, Part B(4) - Close the workbook (but don't save):
    VB Code:
    1. Set oXLSheet = Nothing             'disconnect from the Worksheet
    2.   oXLBook.Close SaveChanges:= False  'Close (and disconnect from) the Workbook
    3.   Set oXLBook = Nothing
    4.   oXLApp.Quit                        'Close (and disconnect from) Excel
    5.   Set oXLApp = Nothing

    Once you have these two chunks of code you are ready to start the fun.


    .
    Share on Google+

  6. #6

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Reading and writing data

    This code needs to be run on an open worksheet, so should be placed between "Part A" and "Part B" from the previous post. It is probably best to use B(1) for now, so that you can see what is going on more easily.


    a) using Cells, Range (and UsedRange)
    There are a few methods of working with the data depending on what you want to achieve.

    The simplest method is to read/write a single cell, which can be done using the Cells collection of the WorkSheet like this:
    VB Code:
    1. Dim my_variable As String
    2. my_variable = oXLSheet.Cells(2, 1).Value
    This copies the text in the cell A2 (specified in the brackets: row 2, column 1) to the variable "my_variable".

    To write a single cell you just need to change the order of the expression like this:
    VB Code:
    1. Dim my_variable As String
    2. my_variable = "hello"
    3. oXLSheet.Cells(2, 1).Value = my_variable
    4. 'or just this:
    5. oXLSheet.Cells(2, 1).Value = "hello"

    To read or write the Formula of the cell rather than the displayed text, just change the ".Value" to ".Formula", e.g.:
    VB Code:
    1. oXLSheet.Cells(3, 1).Formula = "=A1+2"

    All of the methods so far can also be used with the Range object instead of Cells, which allows you to specify the cell in the standard Excel "A1" address format, e.g.:
    VB Code:
    1. oXLSheet.Range("B4").Value = "range test"

    There is an advantage to using the Range object, in that you can work with multiple cells at the same time, for example you could set the value of all cells in the range D3:E5 to a single value like this:
    VB Code:
    1. oXLSheet.Range("D3:E5").Value = 3
    This may seem a little pointless, but with a little alteration you can see the power of this, as you can set all the values differently by using an array like this:
    VB Code:
    1. Dim lMyArray(2, 1) As Long
    2. lMyArray(0, 0) = 1
    3. lMyArray(0, 1) = 2
    4. lMyArray(1, 0) = 3
    5. lMyArray(1, 1) = 4
    6. lMyArray(2, 0) = 5
    7. lMyArray(2, 1) = 6
    8. oXLSheet.Range("D7:E9").Value = lMyArray
    The location of the cells within the range can be deduced from the array positions in the same way as for the Cells collection, i.e.: (row, column).

    Unfortunately you can't quite do this the other way around (i.e.: you can't set lMyArray equal to the range), as VB will not allow you to assign to an array. Instead you need to use a Variant to hold the array instead, e.g.:
    VB Code:
    1. Dim vArray As Variant
    2. vArray  = oXLSheet.Range("D7:E9").Value
    You can copy the entire used range of the spreadsheet simply using a special built-in range called UsedRange, like this:
    VB Code:
    1. Dim vArray As Variant
    2. vArray  = oXLSheet.UsedRange.Value
    3. 'vArray now contains the values of ALL the cells in used range of the worksheet.
    4.  
    5.  
    6. 'example usage of the array:
    7. Dim lngCol as Long, lngRow as Long
    8.   For lngRow = 1 To UBound(vArray,1)
    9.     For lngCol = 1 To UBound(vArray,2)
    10.       MsgBox vArray(lngRow, lngCol)
    11.     Next lngCol
    12.   Next lngRow

    Setting values in the spreadsheet using an array has the advantage of being far quicker, as each time you enter data Excel does some work like re-calculating formulas. Using an array means that this extra work is only done once for the entire array, rather than once per cell.

    b) using a RecordSet
    There is one more way of putting data into Excel that is extremely useful, which is to copy data directly from a recordset that you have gotten from a database. You simply say which is the first cell to put the data into, and Excel works out the rest of the cells that are required (although you can specify the maximum rows/columns to use in two optional parameters).

    I have not created the recordset in these examples, as there are many ways in which you can do it. If you need help with this, please see the tutorials (or post a new thread) in the Database Development forum on this site.
    VB Code:
    1. 'create and fill a recordset here, called oRecordset
    2. oXLSheet.Range("B15").CopyFromRecordset oRecordset
    Note that you can also fill in the field names using an array like this:
    VB Code:
    1. Dim iCount As Integer
    2.     With oXLSheet                    'Fill with data
    3.       For iCount = 0 To (oRecordset.Fields.Count -1)
    4.         .Cells(1, iCount+ 1) = oRecordset.Fields(iCount).Name
    5.       Next iCount
    6.       .Range("A2").CopyFromRecordset oRecordset
    7.     End With
    "But I thought setting individual Cells was slow?" It is, but to use Range you need to know the target cells, in order to do that you need to know the column address of "B" + oRecordset.Fields.Count, which requires a bit more work. Luckily I have provided a function in section 13 "Useful functions and downloads" called xl_Col, which returns the column name for the column number that you specify (there is also xl_ColNo, which converts the name back into a number). This function allows you to do use the Range object like this:
    VB Code:
    1. Dim iCount As Integer
    2. Dim sFieldNames() As String
    3.   ReDim sFieldNames(oRecordset.Fields.Count - 1) As String
    4.   For iCount = 0 To (oRecordset.Fields.Count -1)
    5.     sFieldNames(iCount) = oRecordset.Fields(iCount).Name
    6.   Next iCount
    7.   oXLSheet.Range("A1:" & xl_Col (1 + oRecordset.Fields.Count) & "1")

    .
    Last edited by si_the_geek; May 12th, 2006 at 08:04 AM. Reason: corrected "example usage of the array" (previously read rows as columns & vice-versa)
    Share on Google+

  7. #7

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Basic formatting

    You can format cells by code in a similar way to how you would manually. You can set things such as the font, the font style (bold, underline, etc), the alignment (left, right, etc) and the text colour. You can apply these formats to any kind of range, the two we have looked at so far (Cells and Range) and a few new ones.


    For example, to set the cell "A4" to be bold you can do the following:
    VB Code:
    1. oXLSheet.Range("A4").Font.Bold = True  '(False to turn bold off)
    Or to set it bold, italic, and underlined, you can do this:
    VB Code:
    1. With oXLSheet.Range("A4")
    2.        .Font.Bold = True      '(False to turn bold off)
    3.        .Font.Italic = True    '(False to turn italic off)
    4.        .Font.Underline = xlUnderlineStyleSingle
    5.     End With
    Note that Underline does not use Boolean values, as there are various options for underlining (such as single or double underline). When you type in the = after Font.Underline a list of the values should appear, so that you can simply select the appropriate one (the value to turn underlining off is xlUnderlineStyleNone).

    To change the text colour of the same cell to green you can do this:
    VB Code:
    1. With oXLSheet.Range("A4")
    2.        .Font.ColorIndex = 50
    3.     End With
    To align the text within cells you can use a couple of extra properties, one for horizontal and one for vertical:
    VB Code:
    1. With oXLSheet.Range("A4")
    2.        .HorizontalAlignment = xlRight    '(other options include xlCenter and xlLeft)
    3.        .VerticalAlignment = xlBottom     '(other options include xlCenter and xlBottom)
    4.     End With


    The alternative range objects we are interested in are Rows and Columns. Both of these take a single parameter, which is the row or column number we want to work with, and can be used in the same way as Cells or Range, eg:
    VB Code:
    1. oXLSheet.Rows(7).Font.Bold = True     '(False to turn bold off)
    2.     oXLSheet.Columns(3).Font.Bold = True  '(False to turn bold off)
    You can also select multiple adjacent columns/rows by providing a string parameter instead of a number, which contains the first and last row/column separated by a colon (if you provide a string for Columns, it must be the column name rather than number), eg:
    VB Code:
    1. oXLSheet.Rows("6:7").Font.Bold = True     '(False to turn bold off)
    2.     oXLSheet.Columns("C:E").Font.Bold = True  '(False to turn bold off)

    .
    Share on Google+

  8. #8

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Finding "special" cells (bottom row etc)

    As you may already know, when you are editing a spreadsheet manually you can press Ctrl and an arrow key to go to the next/last cell in that direction which contains text. There are also methods of performing this via code.

    Here is one method to find the last used row/column in the sheet:
    VB Code:
    1. LastRow = oXLSheet.UsedRange.Rows.Count
    2.     LastCol = oXLSheet.UsedRange.Columns.Count
    NB: UsedRange is a special pre-defined range which contains the entire used area of the sheet.


    Here is an alternative - but note that this method assumes that every row has data in all columns (and vice versa). If this is not the case, it is safer to use the options above/below instead.
    VB Code:
    1. LastRow = oXLSheet.Range("A1").End(xlDown).Row
    2.     LastCol = oXLSheet.Range("A1").End(xlToRight).Column

    There is also another built-in function in Excel for detecting used ranges, the .SpecialCells function retrieves a number of "special range" values depending on the constant value passed to the function. Here is the equivalent to the code above (works by going to the 'last' used cell):
    VB Code:
    1. LastRow = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    2.    LastCol = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

    Some of the other parameters for the SpecialCells method are:
    • xlCellTypeAllValidation - Cells having validation criteria
    • xlCellTypeBlanks - Empty cells
    • xlCellTypeComments - Cells containing notes
    • xlCellTypeFormulas - Cells containing formulas
    The full list can be seen in the Help or the Object Browser.


    .
    Last edited by si_the_geek; Jun 24th, 2006 at 06:29 PM.
    Share on Google+

  9. #9

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Installation Issues

    As you are probably aware there are several versions of Excel, all of which have subtle variations in the way they work, and the features that they provide.

    In the vast majority of cases this will not affect the code you need to write to perform the tasks required on the document (unless you use 'new' features which aren't available in the version of Excel which is installed). There can however be a problem when your program tries to start Excel.

    The trouble is that with your installation package you installed a specific version of the Excel type library, which could easily be different from the version of Excel which is installed on the users computer. Even though the installed version provides essentially the same functionality as the one on your development computer, Windows may realise that there are version differences and stop your program from using something that it thinks is likely to cause errors.

    This has become even more pronounced recently, as versions of Excel now get you to install an "Excel.exe" file along with your program, rather than the traditional "ExcelX.olb" file

    Another issue is that Excel may not even be installed, which can stop your program from even starting!

    The best way around these issues is to use a Late-Bound connection to Excel (see next section), with extra error handling around the first line (CreateObject/GetObject) to deal with the possibility that it isn’t installed.


    .
    Share on Google+

  10. #10

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Using a Late-Bound connection to Excel

    The code so far in this tutorial has been Early-Bound (i.e.: VB knows in advance what Excel functions are available to you), which as seen in the previous section can cause serious issues.

    Unless you can guarantee that all of your users have the same version of Excel installed as you do, I would strongly recommend converting to Late-bound for software that you release "into the wild".

    The down-side to using this is that you no longer get some of the nice features of the VB IDE (like the drop-down lists of properties and methods that appear when you type oXLApp. ), so it may be a good idea to convert to this method once your code is finished.

    There are four steps to convert your code:
    1) Replace Excel data types with Object.
    If you have:
    VB Code:
    1. Dim oXLApp as Excel.Application
    2. Dim oXLBook as Excel.Workbook
    3. Dim oXLSheet as Excel.Worksheet
    4. ...

    You need to change it to:
    VB Code:
    1. Dim oXLApp as Object
    2. Dim oXLBook as Object
    3. Dim oXLSheet as Object
    4. ...
    Note that you should also do this with any other variables that you have declared as Excel.something


    2) Change the initialisation of the application object.

    If you have:
    VB Code:
    1. Set oXLApp = New Excel.Application

    You should replace it with:
    VB Code:
    1. Set oXLApp = CreateObject("Excel.Application")
    Note: if Excel isn’t installed, this line of code will cause an error – you should deal with this by using error handling in an appropriate way for your project.


    3) Define the constants that you have used.
    Excel constants (e.g.: xlLeft) are defined in the object library; however you will no longer have a link to this, so they wont be defined any more!

    There are two main options here; the first is to use the Object Browser to find the values/declarations for each one you have used (very slow, and prone to errors!), and the other (much simpler) is to add a pre-made module to your project which declares them all for you. You can find a link to one which Microsoft produced in the “useful functions and downloads” section (post #13).


    4) Remove "Excel object Library" from the list in "Project" -> "References"


    Your code is now late-bound, and should work with all versions of Excel that support automation (as long as you haven’t used special functionality which wasn’t available in earlier versions).


    .
    Share on Google+

  11. #11

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    "Excel doesn't close"

    If you find that after running your program you still have an open copy of Excel that you weren't expecting (either visible, or shown in Task Manager) then I'm afraid that you have made a mistake somewhere in your code.

    The usual cause is using objects that are ok in Excel VBA code, but not in a VB application. Common examples of this are:
    • Application (which needs to be oXLApp)
    • Cells (which should be oXLSheet.Cells)
    • Range (which should to be oXLSheet.Range)
    • WorkSheets (which should be oXLApp.WorkSheets)
    • Selection* (which needs to be oXLApp.Selection)
    • ActiveSheet* (which should be a variable like oXLSheet)
    For any other Excel based items you use, you should do the same as all of the items in this list - specify the parent object (usually oXLSheet or oXLBook or oXLApp) that you want to refer to.

    * As a side note, it is advisable to avoid using Selection and ActiveSheet altogether (even in Excel VBA code), as this can be changed by the user if they are working in Excel while your code is running - which means you could well be working in the wrong worksheet/workbook!


    It may be that you haven't used appropriate code to close the sheet/workbook/application (see post #5 for valid examples). This is particularly true if you have not used a variable to store the WorkSheet (as several people seem to do), as it is easy to refer to the WorkBook instead (which isn't technically valid, even tho it works); I would therefore strongly recommend using variable for it.

    If as part of "closing down" you miss a child object variable (which is basically what happens in the situation described above) and destroy the application object, it will orphan the child object variable and cause the instance of Excel to remain because not all resources associated with the application instance have been closed and destroyed.

    .
    Last edited by si_the_geek; Feb 6th, 2009 at 01:07 PM. Reason: added more text
    Share on Google+

  12. #12

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    How do I write code to … ? [a.k.a. recording macro's]

    There are many things which can be done with Excel that haven't been listed here, and there is a good reason - Excel will tell you the code you need!

    If you record a macro in Excel, it is 'written' in VBA (a subset of VB), and this code can be copied almost directly into your VB program, with only a few modifications to use the objects you have created rather than the default Application/Sheet/etc objects [failure to do this part will most likely cause your program to have bugs, or even cause your computer to crash].

    To record a macro:
    • Go to "Tools" -> "Macro" -> "Record new macro".
    • Manually do the things that you want your program to do.
    • Press the "stop recording" button (a blue square).
    • Go to "Tools" -> "Macro" -> "Macros...", which will show a list of macros.
    • Select the macro you recorded, and click on "Edit" to see the code.



    There are 4 steps you should perform when taking code from a macro to use in your program, they are:
    1. Remove un-needed code (as Excel often does extra things, such as writing all values from an options screen).
    2. Change ActiveDocument, Application etc to suit the variables in your code.
    3. Change Selection to oXLApp.Selection.
    4. Prefix all instances of Range/Cells/Rows/Columns/etc with your sheet object.


    Example 1:
    The following code is created if you select a range, then set it to Bold and Italic:
    VB Code:
    1. Range("C9:F16").Select
    2.     Selection.Font.Bold = True
    3.     Selection.Font.Italic = True
    In this case there is no un-needed code, so we can skip step 1.

    There is no use of ActiveDocument etc, so we can also skip step 2.

    Following step 3 gives us this:
    VB Code:
    1. Range("C9:F16").Select
    2.     oXLApp.Selection.Font.Bold = True
    3.     oXLApp.Selection.Font.Italic = True
    And step 4 gives us this:
    VB Code:
    1. oXLSheet.Range("C9:F16").Select
    2.     oXLApp.Selection.Font.Bold = True
    3.     oXLApp.Selection.Font.Italic = True
    As mentioned in the previous post, it is best to avoid Selection altogether. This can be done by changing the code to this:
    VB Code:
    1. oXLSheet.Range("C9:F16").Font.Bold = True
    2.     oXLSheet.Range("C9:F16").Font.Italic = True
    ..or you can do it like this instead (a bit more efficient, and easier to read):
    VB Code:
    1. With oXLSheet.Range("C9:F16")
    2.       .Font.Bold = True
    3.       .Font.Italic = True
    4.     End With


    Example 2:
    As another example, here is part of the code created if you go into "File"->"Page Setup", then set the orientation to Landscape, and set the "rows to repeat at top" to $1:$2
    VB Code:
    1. With ActiveSheet.PageSetup
    2.         .PrintTitleRows = "$1:$2"
    3.         .PrintTitleColumns = ""
    4.     End With
    5.     ActiveSheet.PageSetup.PrintArea = ""
    6.     With ActiveSheet.PageSetup
    7.         .LeftHeader = ""
    8.         .CenterHeader = ""
    9. ... lots of lines removed! ...
    10.         .CenterVertically = False
    11.         .Orientation = xlLandscape
    12.         .Draft = False
    13.         .PaperSize = xlPaperA4
    14.         .FirstPageNumber = xlAutomatic
    15.         .Order = xlDownThenOver
    16.         .BlackAndWhite = False
    17.         .Zoom = 100
    18.     End With

    Following step 1 gives us this:
    VB Code:
    1. With ActiveSheet.PageSetup
    2.         .PrintTitleRows = "$1:$2"
    3.     End With
    4.     With ActiveSheet.PageSetup
    5.         .Orientation = xlLandscape
    6.     End With
    Which can be shortened to this:
    VB Code:
    1. With ActiveSheet.PageSetup
    2.         .PrintTitleRows = "$1:$2"
    3.         .Orientation = xlLandscape
    4.     End With
    From there, following step 2 gives us this:
    VB Code:
    1. With oXLSheet.PageSetup
    2.         .PrintTitleRows = "$1:$2"
    3.         .Orientation = xlLandscape
    4.     End With
    In this case, there is nothing to do for steps 3 and 4.


    .
    Last edited by si_the_geek; Mar 1st, 2007 at 02:55 PM.
    Share on Google+

  13. #13

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Useful functions and downloads

    Excel constants module
    This is a pre-written module which contains all of the Excel constants (e.g.: xlLeft), to enable you to keep the constant names in your code when you are using Late-binding. Just add the module to your project! (NB: this download also contains similar modules for the other Office products).

    This is provided by Microsoft, and can be downloaded here: http://support.microsoft.com/kb/112671 (alternative)

    Note that the above file is designed for Excel 97, so it does not contain constants which have been added more recently (it does however contain almost everything you need). You can see a list of constants in the Excel 2003 constants page, which contains those for Excel 97 plus newer ones.


    xl_col and xlColName
    Functions for converting column 'name' to/from column number.

    Simply add these functions to your code (in a module if you want to use them from multiple forms).
    VB Code:
    1. Function xl_Col(ByRef Col_No) As String
    2. 'returns Excel column name from numeric position (e.g.: col_no 27 returns "AA")
    3. 'by Si_the_geek (VBForums.com)
    4.  
    5.                                       'Only allow valid columns
    6.   If Col_No < 1 Or Col_No > 256 Then Exit Function
    7.  
    8.   If Col_No < 27 Then                  'Single letter
    9.     xl_Col = Chr(Col_No + 64)
    10.   Else                                 'Two letters
    11.     xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & _
    12.              Chr(((Col_No - 1) Mod 26) + 1 + 64)
    13.   End If
    14.  
    15. End Function
    16.  
    17. 'example usage:
    18. sColName = xl_Col(7)
    VB Code:
    1. Function xl_ColNo(Col_Name) As Integer
    2. 'returns an Excel column number from its name (e.g.: col_name "AA" returns  27)
    3. 'by Si_the_geek (VBForums.com)
    4.  
    5.   Col_Name = UCase(Trim(Col_Name))
    6.   Select Case Len(Col_Name)
    7.   Case 1:     xl_ColNo = Asc(Col_Name) - 64
    8.   Case 2:     xl_ColNo = ((Asc(Left(Col_Name, 1)) - 64) * 26) _
    9.                        + (Asc(Right(Col_Name, 1)) - 64)
    10.   End Select
    11.  
    12. End Function
    13.  
    14. 'example usage:
    15. iColNo = xl_ColNo("Z")

    .
    Last edited by si_the_geek; Nov 4th, 2010 at 04:47 AM. Reason: added link to Excel 2003 constants
    Share on Google+

  14. #14

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Frequently Asked Questions

    Does the user need Excel installed?
    Yes (how can you automate something that isn't there?), and you cannot install it without them having a valid licence for it.


    What library version number should I use?
    To be honest any which you have installed will normally be fine, however if you want to use "new" functionality that has been added in newer versions of Excel, you need to select the one which provides that functionalility.

    Since the introduction of Excel 95, the versions haven't been obvious (until then there were "proper" version numbers like 5.0). The reference that you use in your program reflects the internal version number, rather than the displayed number.

    The version numbers are:
    • 7.0 - Excel 95
    • 8.0 - Excel 97
    • 9.0 - Excel 2000
    • 10.0 - Excel XP
    • 11.0 - Excel 2003


    How do I work with a copy of Excel that is already open?
    This will connect the application object to an open copy of Excel:
    VB Code:
    1. Set oXLApp = GetObject(,"Excel.Application")
    Please note that there is no way to specify which open copy of Excel to work with, so if there is more than one open you cannot predict which one will be referenced.

    Note also that if there are no instances of Excel running, this line of code will cause an error.


    How do I call an Excel Macro from VB?
    See this example in the Microsoft Knowledge base:
    http://support.microsoft.com/default...b;en-us;194611


    Do I really need a specific WorkBook/Worksheet/etc object?
    Yes you do. When you are testing it might not be a problem, but when your program is being used you may notice strange errors coming up, or your program failing, or (even worse) modifying the wrong documents.

    Why would this happen? Excel is capable of having multiple documents open, and can also be manipulated by the user. If you don't reference specific WorkBooks/Worksheets/etc then you are at the mercy of which one Excel thinks you want - it usually assumes the one that is currently active. If a user opens/creates a workbook then their workbook/sheet becomes active, rather than the one that you intended to work with.


    How do I detect events that happen in a visible workbook?
    See this thread: http://www.vbforums.com/showthread.php?t=305203


    .
    Last edited by si_the_geek; Aug 17th, 2006 at 05:11 PM.
    Share on Google+

  15. #15

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    The End

    Well folks, thats all of it!

    Not everything is covered, so if you need any further help feel free to ask in the forums (Classic VB or Office Development would be best), as there are several people around (including myself) who can help you.

    If you have anything to add to this tutorial, or anything is not clear enough, please let me know via PM.



    Thanks to RobDog888 for his comments, as well as the details/example of .SpecialCells, and some of the text for "Excel doesn't close".


    .
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.