Excelling

September 14, 2008

Referring to Rows & Columns

Filed under: VBA — fatrunner @ 9:53 pm
Tags: , ,

In the previous post I spoke about referring to cells and ranges of cells, but in the instance you need to refer to an entire row or an entire column you can do so with what was learned previously, but it would be rather cumbersome. Visual Basic has a way for us to so simply. The format is just like what we used when referring to a range, but instead of the word Range we’ll use the word Rows or Columns. A couple of examples are:

Rows(1)  'refers to row 1
Columns(1)  'refers to column 1 or you can use...
Columns("A")  'again refers to the 1st column and has to have the quotes
Columns  'no numbers or letters refers to ALL columns on the worksheet

You can also select multiple rows or columns by separating with commas like the following examples:

Rows("1:1,3:3")  'refers to multiple non-contiguous rows, i.e. row 1 AND row 3
Rows("1:3")  'refers to multiple contiguous rows, i.e. rows 1 THROUGH 3

In my next post I’ll show you a shortcut notation for referring to cells.

September 13, 2008

Referring to Cells and Ranges of Cells

Filed under: VBA — fatrunner @ 2:06 pm
Tags: , ,

Now that you’ve got a workbook open, it is very likely that one of the first things you’ll need to do is to refer to a cell or a range of cells in order to put data there or to manipulate the data that is already there. It takes one simple line of code to do so and the format is as follows: Range(“cell range“). Following is a number of variations to select a number of different ranges.

Range("A1")  'select cell A1
Range("A1:B2")   'select cells from A1 through B2
Range("A1:B2,A3:B3")  'select a non-contiguous range of cells 
Range("A:A")  'select column A
Range("1:1")   'select row 1

The hardest part for me to remember is the quotation marks. It is very similar to using a ‘sum’ formula in the way you present the cells in the parenthesis…just remember the quotes. This is the most common way you will see a range of cells or a single cell referred to when looking at VBA code. This is called the A1 notation obviously because it refers to cells using the letter (for the column) and number (for the row).

Sometimes it makes sense to refer to cells in a different way using index numbers. What that means is using numbers to refer to both the column and the row.  The confusing part for me is that the notation gets reversed from what you are used to when using the A1 notation. A1 notation starts with the letter, i.e. the column, and then the number, i.e. the row. Using the index number reverses that, referring to the row first and then the column. Other than only seeing numbers to clue you in that the index numbers are being used as opposed to A1 notation, the word ‘range’ is also not used. Instead the word ‘cell’ is used.

The reason for this type of notation is that it is useful when using a counter or other method, such as looping to increment the cell location you are referring to. Below is an example used in Excel’s help file to show the use of the Index Notation:

Worksheets("Sheet1").Cells(6,1).Value = 10

This line of code tells Excel to find the cell that is in the 6th row and 1st column (Cell A6 using A1 notation)on Sheet1 and make its value equal to 10. Excel’s help file goes on to show an example of how this would be used if it were looped by showing the following code:

Sub CycleThrough()
    Dim Counter As Integer
    For Counter = 1 To 20
        Worksheets("Sheet1").Cells(Counter, 3).Value = Counter
    Next Counter
End Sub

If you were going through the help file this would show you a number of things that would be completely new to you that wouldn’t make any sense but I think it is simple enough that you could get the jist of how it would be used.  Since I’ve put the code out there I will explain the steps that this code is running through in English.

Sub CycleThrough() is the opening of the VBA code. All code starts with the word Sub and then the name of the macro followed with the open and closed partenthesis.

Dim Counter As Integer. This tells Excel to create a variable called Counter and make that variable an integer.

For Counter = 1 To 20. This starts a For Next loop and tells Excel that for every time you go through this loop find the variable named Counter and assign it the value of 1 until you get to the ‘Next’ line. Then use the value of 2, then 3, then 4 and so on until 20 is reached.

Worksheets(“Sheet1”).Cells(Counter, 3).Value = Counter.  This is where the value of the Counter plays its part. The first time through the loop Excel has assigned the value of 1 to the variable Counter.  In essence you can invision this line of code rewritten with the number 1 in the place of the word Counter.  The first time through this loop Excel will find the cell that is in the first row down and third column over (this would be cell C1) on Sheet1 and make it value equal to 1, which is the value currently being held for the variable Counter. The next time the loop runs through it will move to cell C2, the 2nd row down and 3rd column over, and make its value equal to 2.

Next Counter. This moves the code back up to the For Counter line and increments the number up by one.

End Sub. This ends the code.

That gets off the topic a bit, but it does show how and why you would use this variation of notation to refer to a cell.

August 30, 2008

Opening A Workbook

Filed under: VBA — fatrunner @ 2:58 pm
Tags: ,

Last week I talked about adding a new workbook using VBA.  This week I want to discuss opening a workbook via VBA. This is one that I use every day and find useful. In my case I use this when I dump data out of my accounting software and want to add it to an existing spreadsheet that I use to analyize and accumulate that data. It is one simple line of code:

Workbooks.Open("C:\SomeFolder\SomeSubFolder\YourFile.xls")

It doesn’t get much simpler than that. But it is not unusual for spreadsheets to have more than one worksheet.  If you saved “YourFile.xls” when you were viewing sheet2, then it will open on sheet2.  But what if the data you want to view and/or manipulate is on sheet1?  Well unless you specifically tell VBA to manipulate sheet1 it will execute the code on whatever sheet it opens to. So it is always a good practice to tell VBA to select the sheet you want. You would do so by using the following code:

Worksheets(1).Activate

Now although that is pretty straight forward, it does require a little further explanation. When you tell VBA to activate worksheets(1) what you are specifically saying is that you want to activate the first worksheet in the workbook regardless of what it is named.  It could be named sheet4, but if it is the sheet furthest to the left it is worksheets(1) to VBA. So what do you do if you want to make sure it actually selects sheet1 or maybe a sheet that you’ve named Data?

Then you would change the above line of code to this:

Worksheets("Sheet1").Activate

Instead of using the index number (1) you simply put the name of the sheet in quotes inside the parenthesis.

Another tweak to this that I have not personally used is that instead of using the word Worksheets you can use the word Sheets. The difference between the two is that Sheets includes all sheets in the workbook which would include any chart sheets or dialog sheets.  Conversely if you were to use either:

Charts(1).Activate
DialogSheets(1).Activate

It would function the same as Worksheets(1).Activate above.  It would select the very first chart sheet or dialog sheet in the workbook regardless of its name.  So if you wanted to make sure you selected the correct chart when you opened up your YourFile.xls you would want to follow the same principle and write it this way:

Charts("Chart1").Activate

Once you’ve gotten this, you are well on your way to adding data to an existing spreadsheet or retreiving data from an existing spreadsheet and using VBA to do the heavy lifting for you.

August 22, 2008

Adding a new workbook

Filed under: VBA — fatrunner @ 2:15 am
Tags: ,

Adding a new workbook to Excel via Visual Basic can be done with one simple command:

Workbooks.Add

According to Excel’s Visual Basic Help file, the best method for doing so is to assign the workbook to an object variable. Doing so allows you to manipulate various properties of the workbook. Their example looks like this:

Sub AddNew()
  Set  NewBook = Workbooks.Add
    With NewBook
      .Title = "All  Sales"
      .Subject = "Sales"
      .SaveAs  Filename:="Allsales.xls"
    End With
End Sub

What this code does in English is the following:

Sub AddNew()

Creates a new subroutine called “AddNew”

Set NewBook =  Workbooks.Add

Creates a variable named NewBook and makes it equal to the VBA code for adding a workbook.  Until the the variable is changed, any time reference is made to it, it will add a new workbook.

With NewBook
  .Title = "All Sales"
  .Subject = "Sales"
  .SaveAs  Filename:="Allsales.xls"
End With

This chunk of code actually creates the new workbook and tacks on some attributes to it in the process with the ‘With’ statement.  The ‘Title=’ portion adds a title to it which you can see if in Excel you go to File –> Properties.  Excel does not normally put anything here by default and it can be different than the filename of the spreadsheet. The ‘Subject=” portion adds a subject to it in the same way that the Title did above and can be found the same way via File –> Properties. I’ve never used these properties before in my daily use of Excel and at this point I’m not sure what purpose they serve, but that may be a later topic of discussion.

The ‘SaveAs Filename:=” line actually saves the file with the filename you specify within the quotation marks.  As this code is written (with just a filename) it will save the spreadsheet to your My Documents folder. If you wanted to save it to another location you would include the entire path in the quotation marks such as “C:\Docuements and Settings\username\My Documents\Sales\Allsales.xls”

And then of course the last line is

End Sub

which stops the subroutine.

Well…there you go. You’ve just added a new workbook.

Create a free website or blog at WordPress.com.