Excelling

December 14, 2008

Relative Cell Referencing with Offset

Filed under: VBA — fatrunner @ 5:22 pm

There are times when you are recording a macro or writing code that you need to make a reference to a cell and that reference is not absolute, meaning that it’s position is relative to another cell whose position can change.   One example of this I can think of is if you are importing data into a spreadsheet and simply want to total it.  If the number of items in the list never changes the relative reference would not be necessary, but if the number of items can change, then you are better off using the relative reference instead.  In the first illustration if you knew that your total would always be in cell A9, then you could just code the sum formula in cell A9.  But in the second illustration the total is now needed to be in cell A10, and a relative reference is needed.

Sum Formula Resides in Cell A9

Sum Formula Resides in Cell A9

Sum Formula Needs to be Relative to the Last Number in the List

Sum Formula Needs to be Relative to the Last Number in the List

If you are simply recording a macro when you type in the sum formula then the macro recorder will  simply tell Excel to put the sum formula in cell A9 always.  If  you want it to always be at the bottom of the list of numbers then you will need to change the way the macro records (by changing the macro recorder to relative reference) or the way you write the code.  In order to make the code ‘know’ to go to the bottom of the list you will need to use the following code:

Range("A1").Select
    Selection.End(xlDown).Offset(1,0)).Select

For either illustration this formula would get you to the cell where you will need to type in the sum formula. I am stopping here for the simple fact that I am still learning all of this and I don’t know how to code the sum formula yet with a relative reference built into it. I still have to figure that part out and will post it in my next update.

Advertisements

November 2, 2008

Rearranging Life

Filed under: VBA — fatrunner @ 6:39 am

I’m under no illusion that people are lining up to view this site, but I did want to at least explain my reasons for not posting lately for the people that do stumble onto this site.

We recently rearranged the furniture in our living room to expand the area that our one year old daughter can roam around in.  Before the move there was a baby gate between her an my recliner (where most of my computing is done).  Since we’ve moved things around she now has access to my chair and when my computer is in hand she very much wants to tap away at it like she sees daddy doing, which makes typing a much greater challenge.

I just wanted to say I haven’t given up on this site, I’m just having a tough time figuring out when is another good time for me to do my writing.  Stay tuned…more will come.

October 4, 2008

Referring To Named Ranges

Filed under: VBA — fatrunner @ 9:00 am
Tags:

Excel’s VBA help file goes into referring to named ranges without much discussion on how to name a range.  Most people who have used excel for some time know how to name a range. On the off chance you don’t, one of the easiest ways is to select the area that will now be know by the ‘name’ and type that name in the name box which is located just to the left of the formula bar as shown by the arrow in the illustration below.

I’m certainly no expert in writing VBA code and get most of my coding done by recording macros. One thing I’ve noticed about Excel (at least in version 2003) is that when recording macros, it does not seem to like you naming ranges in this way.  So when I need to name a range while recording a macro I will name a range using another method. That way is by selecting the Insert menu and chosing Name –> Define.  The resulting dialog box allows you to name the range and even tweak it somewhat in the ‘refers to’ section of the dialog box. I haven’t ever had much of a need to tweak it, but I’ve seen some pretty cool stuff done to make the range dynamic in nature. Perhaps one day I’ll research that and do a post on it.  By defining a range in this way, Excel writes the following code:


ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R7C3:R17C4"

In this case I’ve used the name test to describe the range C7 through D17.  I’m certain there are plenty of other ways to define a range of cells, but for a beginner this is the easiest, plus you can let the macro do the heavy lifting for you.

Once you’ve defined a range it is easy later in the code to refer to that range by simply referring to the name instead of referring to the actual cells. For example if you wanted to return to the range to make it bold you could use the following code:


Application.Goto Reference:="test"
    Selection.Font.Bold = True

If the range you are referring to is in another workbook you can simply add the workbook name within the quotes like such:


Application.Goto Reference:="Workbook.xls!Test"
     Selection.ClearContents

That’s pretty basic and doesn’t go beyond doing anything with the range once you’ve referred to it, but I’ll save that for another post.

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.

Blog at WordPress.com.