Excel VBA - Range variable

[Davew01]

I am fairly new to Excel macro programming and need some help. I am trying to set up a variable as an object type to store a range. I know the following works

Dim rMyCell As Range
Set rMyCell = Range("A1")
rMyCell = rMyCell + Range("a2")
where as rMyCell will = the contents of cell A1 which I then add to cell A2.

What I would like to do is set the range to the activecell in the spreadsheet.
I tried to code below by I get an error.

Dim rMyCell As Range
Set rMyCell = ActiveCell.Select

I also tried this and still get an error.

Set rMyCell = Worksheets("Notes").ActiveCell.Select

 

[Zack Barresse]

Hi Dave, welcome to the board!

You're so very close with your syntax. It should be...

Dim rngMyCell as Range
Set rngMyCell = Sheets("Notes").Range("A1")
rngMyCell.Value = rngMyCell.value + sheets("Notes").Range("A2").Value

I would advise against using the Activecell unless you really have to (which generally is not the case). The same goes with Selecting and/or Activating a cell or worksheet in Excel. As long as you explicitly define where the range is (worksheet, workbook), then you should be good.

HTH

 

[Davew01]

Excel Questions

Thanks for the feedback. I will give it a try this evening.

BTW - You indicated that you would "advise against using the Activecell unless you really have to (which generally is not the case). The same goes with Selecting and/or Activating a cell or worksheet in Excel."

For my future knowledge what is the downside of using Activecell.select and Activating a cell, speed or performance issues?

 

[pfarmer]

Re: Excel Questions

Thanks for the feedback. I will give it a try this evening.

BTW - You indicated that you would "advise against using the Activecell unless you really have to (which generally is not the case). The same goes with Selecting and/or Activating a cell or worksheet in Excel."

For my future knowledge what is the downside of using Activecell.select and Activating a cell, speed or performance issues?

I see an issue with using 'Active' and another with using 'Activate', 'Select' etc. In the first case it is possible that a macro that uses 'Active' could try to operate on the 'Active' cell, sheet, etc. of a different workbook operating in the same instance of Excel, depending on what triggers the macro. I generally define the 'Active' part as the workbook the macro sits in and the intended sheet in that workbook.

Second, and this is what I think was being alluded to, is that there may be performance issues with 'Activate', 'Select' etc. Typically it is not necessary to use 'Activate', 'Select', so these issues can be avoided.

Consider for example picking up values from many cells located in many sheets of a workbook. During a loop if you select the cells, a macro would select a cells on each sheet, which can make the macro run slowly. If you want the value of a cell, you don't usually have to select the cell.

On the other hand I sometimes will select a cell to force a sheet to scroll to that location. There are other ways of doing this, but this is a cheap and dirty way that works.

An example of not using 'Select' is when you use sorting. If you record a macro for sorting you would select the range, then select sort. The macro will show that you selected a range, then sorted on 'Selection'.

Example:

  Range("A1:H28").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

The start could be shortened to:

Range("A1:H28").Sort

When this macro is run you will not see the range selected, but it will be sorted.

Perry

 

[Davew01]

Excel Range

Thanks for the additional information. I am learning everyday. Again I appreciate your quick response and help. Great forum!

 

[Zack Barresse]

Sorry for getting back late here. As Perry has stated most of the issue(s) with using Activate/Select, let me hit one last point on the matter. You never know what users will do. This basically means that no two situations are ever alike (99% of the time). So if you count on something in your code such as basing it on the Activecell, or the Selection, then you may be brewing a recipe for disaster. Instead, base your code off logic, this will make your code *intuitive* and be able to grow with your spreadsheet.

HTH

 

[Norie]

Dave

If you must use ActiveCell the syntax is just this.

Dim rMyCell As Range
Set rMyCell = ActiveCell

But as firefytr and Perry have pointed out you should avoid using it.