Excel VBA - Range variable

Davew01

New Member
Joined
Sep 17, 2006
Messages
7
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi Dave, welcome to the board!

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

Code:
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

New Member
Joined
Sep 17, 2006
Messages
7
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

Well-known Member
Joined
Jul 6, 2005
Messages
550
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:

Code:
  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

New Member
Joined
Sep 17, 2006
Messages
7

ADVERTISEMENT

Excel Range

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

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Dave

If you must use ActiveCell the syntax is just this.
Code:
Dim rMyCell As Range
Set rMyCell = ActiveCell
But as firefytr and Perry have pointed out you should avoid using it.
 

Forum statistics

Threads
1,136,649
Messages
5,676,991
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top