2012-05-23 05:57:58 8 Comments

I've heard much about the understandable abhorrence of using .Select in Excel VBA, but am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use variables instead of Select functions. However, I am not sure how to refer to things (like the ActiveCell etc.) if not using Select.

I have found this article on ranges and this example on the benefits of not using select but can't find anything on how?


@chris neilsen 2012-05-23 10:23:22

Some examples of how to avoid select

Use Dim'd variables

Dim rng as Range

Set the variable to the required range. There are many ways to refer to a single-cell range

Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = Range("NamedRange")

or a multi-cell range

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1,1), Cells(10,2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10,2)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Set rng = [A1]
Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)
With ws
    Set rng = .Range(.Cells(1,1), .Cells(2,10))
End With

If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbook object to refer to the book containing the running code.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

This is bad:

Sub foo()
    Dim v as Variant
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
End Sub

And would be better like:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
End Sub

Pass ranges to your Subs and Functions as Range variables

Sub ClearRange(r as Range)
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

You should also apply Methods (such as Find and Copy) to variables

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i,1) * 10 'or whatever operation you need to perform
rng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

@Qbik 2015-01-10 20:31:28

why should we use 'Sub ClearRange(r as Range)' instead of direct .ClearContents ?

@MikeD 2015-01-12 18:07:58

adding to this brilliant answer that in order to work wit a range you don't need to know its actual size as long as you know the top left ... e.g. rng1(12, 12) will work even though rng1 was set to [A1:A10] only.

@Logan Reed 2016-07-15 18:20:09

@chrisneilsen Chris, I believe you can also use worksheet prefix before shorthand cell reference notation to save you from typing Range like this: ActiveSheet.[a1:a4] or ws.[b6].

@Andrew Willems 2018-02-06 02:12:01

Your excellent answer misses one part of the OP's question: How do you refer to the active cell? e.g. When creating/editing a sophisticated macro for use with a user-defined cell I don't understand how it can be avoided (but correct me if I'm wrong). So, e.g. MsgBox (ActiveCell.Value) works but Dim ac As Range ac = ActiveCell & MsgBox (ac.Value) does not. I've also tried dim'ing as Excel.Range, Object & Variant. So, can you dimension a variable that will eventually contain the active cell, and if so, how? Or is one forced to always use ActiveCell directly?

@Andrew Willems 2018-02-06 02:44:33

OK, humility check: Your answer as it currently stands mentions the keyword Set 17 times, but I still missed it. However in case others are wondering the same thing the short answer is: To refer to the active cell without Select, do the following: Dim ac as Range and Set ac = ActiveCell (and don't forget Set).

@ashleedawg 2018-04-07 11:38:57

@AndrewWillems ...or 48 times in this post, but who's counting. ☺ ...but seriously, it's an easy one to forget when working with variables holding objects. A variant variable doesn't require Set until you assign an object to it. For example, Dim x: x = 1 is okay, but Dim x: x = Sheets("Sheet1") will generate Error 438. However just to confuse/clarify Dim x: x = Range("A1") will not create an error. Why? ...because it's assigned the value of the object to the variable, not a reference to the object itself (since it's the equivalent of Dim x: x = Range("A1").Value)

@ashleedawg 2018-04-07 11:52:23

@chrisneilsen - I would link to this detailed answer when I see people using multiple lines of inefficient code to do something that could be accomplished in one line, except I don't want more people getting into the bad habit of using [sʜᴏʀᴛᴄᴜᴛ ɴᴏᴛᴀᴛɪᴏɴ]. I suspect you know to only use it for debugging, etc (such as in your examples) but I know of at least a couple users that saw it for the first time here and went, "Cool, I'm using that all the time from now on!"

@Zev Spitz 2018-05-03 05:37:32

@ashleedawg What's wrong with shortcut notation?

@Zev Spitz 2018-05-03 05:45:58

@Mertinc Range.Find returns a Range object with the first matching cell. This means you should be able to do: Dim match As Range: Set match = Cells.Find(...): match.Value=match.Value to set the value of the cell to the formula result without the formula.

@TylerH 2018-12-13 15:33:52

@user3932000 I'm not aware of a scenario where sheetnames change automatically. As for filenames, it would only do that if there is already a file of that name in the folder. Just use Save... or hard code the file name to save it under as a string. If you can't solve this problem, you should ask a separate question about it instead of commenting.

@user3932000 2018-12-17 22:58:05

@TylerH I have a read-only template xlsx file, which I enter data into then export as tab-delimited text files. Each "Save As" changes the sheet name.

@TylerH 2018-12-17 23:00:26

@user3932000 OK, well changing the file format would necessitate that, but then it's not really an xlsx file anymore, but rather a bunch of text files.

@chris neilsen 2018-12-18 03:42:40

@user3932000 that would make an interesting Q. I'm sure there are ways to handle it.. you've been on SO long enough to know hijacking a comment thread on an old Q isn't the way to go

@PGCodeRider 2018-08-16 20:07:44

I noticed that none of these answers mention the .Offset Property. This also can be used to avoid using the Select action when manipulating certain cells, particularly in reference to a selected cell (as the OP mentions with ActiveCell).

Here are a couple examples.

I will also assume the "ActiveCell" is J4.

ActiveCell.Offset(2, 0).Value = 12

  • This will change the cell J6 to be a value of 12
  • A minus -2 would have referenced J2

ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)

  • This will copy the cell in k4 to L4.
  • Note that "0" is not needed in the offset parameter if not needed (,2)
  • Similar to the previous example a minus 1 would be i4

ActiveCell.Offset(, -1).EntireColumn.ClearContents

  • This will clear values in all cells in the column k.

These aren't to say they are "better" than the above options, but just listing alternatives.

@LFB 2018-06-16 14:18:51

These methods are rather stigmatized, so taking the lead of @Vityata and @Jeeped for the sake of drawing a line in the sand:

Why not call .Activate, .Select, Selection, ActiveSomething methods/properties

Basically because they're called primarily to handle user input through the Application UI. Since they're the methods called when the user handles objects through the UI, they're the ones recorded by the macro-recorder, and that's why calling them is either brittle or redundant for most situations: you don't have to select an object so as to perform an action with Selection right afterwards.

However, this definition settles situations on which they are called for:

When to call .Activate, .Select, .Selection, .ActiveSomething methods/properties

Basically when you expect the final user to play a role in the execution.

If you are developing and expect the user to choose the object instances for your code to handle, then .Selection or .ActiveObject are apropriate.

On the other hand, .Select and .Activate are of use when you can infer the user's next action and you want your code to guide the user, possibly saving him some time and mouse clicks. For example, if your code just created a brand new instance of a chart or updated one, the user might want to check it out, and you could call .Activate on it or its sheet to save the user the time searching for it; or if you know the user will need to update some range values, you can programatically select that range.

@Vityata 2016-03-08 10:04:12

Avoiding Select and Activate is the move that makes you a bit better VBA developer. In general, Select and Activate are used when a macro is recorded, thus the Parent worksheet or range is always considered the active one.

This is how you may avoid Select and Activate in the following cases:

Adding a new Worksheet and copying a cell on it:

From (code generated with macro recorder):

Sub Makro2()
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Application.CutCopyMode = False
End Sub


Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

When you want to copy range between worksheets:




Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

Using fancy named ranges

You may access them with []. Which is really beautiful, compared to the other way. Check yourself:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

The example from above would look like this:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

Not copying values, but taking them

Usually, if you are willing to select, most probably you are copying something. If you are only interested in the values, this is a good option to avoid select:

Range("B1:B6").Value = Range("A1:A6").Value

Try always to refer the Worksheet as well

This is probably the most common mistake at the . Whenever you copy ranges, sometimes the worksheet is not referred and thus VBA considers the ActiveWorksheet.

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

Can I really never use .Select or .Activate for anything?

The only time when you could be justified to use .Activate and .Select is when you want make sure, that a specific Worksheet is selected for visual reasons. E.g., that your Excel would always open with the cover worksheet selected first, disregading which which was the activesheet when the file was closed. Thus, something like this is absolutely ok:

Private Sub Workbook_Open()
End Sub

@MattB 2014-02-27 21:09:40

I'm going to give the short answer since everyone else gave the long one.

You'll get .select and .activate whenever you record macros and reuse them. When you .select a cell or sheet it just makes it active. From that point on whenever you use unqualified references like Range.Value they just use the active cell and sheet. This can also be problematic if you don't watch where your code is placed or a user clicks on the workbook.

So, you can eliminate these issues by directly referencing your cells. Which goes:

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

Or you could

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

There are various combinations of these methods, but that would be the general idea expressed as shortly as possible for impatient people like me.

@marionffavp 2017-05-05 14:30:22

This is an example that will clear the contents of cell "A1" (or more if the selection type is xllastcell, etc). All done without having to select the cells.

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")

I hope this helps someone.

@Eleshar 2016-11-20 15:02:00

IMHO use of .select comes from people, who like me started learning VBA by necessity through recording macros and then modifying the code without realizing that .select and subsequent selection is just an unnecessary middle-men.

.select can be avoided, as many posted already, by directly working with the already existing objects, which allows various indirect referencing like calculating i and j in a complex way and then editing cell(i,j), etc.

Otherwise, there is nothing implicitly wrong with .select itself and you can find uses for this easily, e.g. I have a spreadsheet that I populate with date, activate macro that does some magic with it and exports it in an acceptable format on a separate sheet, which, however, requires some final manual (unpredictable) inputs into an adjacent cell. So here comes the moment for .select that saves me that additional mouse movement and click.

@vacip 2016-11-22 13:25:33

While you are right, there is at least one thing implicitly wrong with select: it is slow. Very slow indeed compared to everything else happening in a macro.

@FinPro.Online 2016-09-08 06:36:33

Quick Answer:

To avoid using the .Select method you can set a variable equal to the property that you want.

► For instance, if you want the value in Cell A1 you could set a variable equal to the value property of that cell.

  • Example valOne = Range("A1").Value

► For instance, if you want the codename of 'Sheet3` you could set a variable equal to the codename property of that worksheet.

  • Example valTwo = Sheets("Sheet3").Codename

I hope that helps. Let me know if you have any questions.

@user4039065 2015-02-24 15:41:21

"... and am finding that my code would be more re-usable if I were able to use variables instead of Select functions."

While I cannot think of any more than an isolated handful of situations where .Select would be a better choice than direct cell referencing, I would rise to the defense of Selection and point out that it should not be thrown out for the same reasons that .Select should be avoided.

There are times when having short, time-saving macro sub routines assigned to hot-key combinations available with the tap of a couple of keys saves a lot of time. Being able to select a group of cells to enact the operational code on works wonders when dealing with pocketed data that does not conform to a worksheet-wide data format. Much in the same way that you might select a group of cells and apply a format change, selecting a group of cells to run special macro code against can be a major time saver.

Examples of Selection-based sub framework:

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

The actual code to process could be anything from a single line to multiple modules. I have used this method to initiate long running routines on a ragged selection of cells containing the filenames of external workbooks.

In short, don't discard Selection due to its close association with .Select and ActiveCell. As a worksheet property it has many other purposes.

(Yes, I know this question was about .Select, not Selection but I wanted to remove any misconceptions that novice VBA coders might infer.)

@L42 2015-05-19 22:19:24

Selection can be anything in the worksheet so might as well test first the type of the object before assigning it to a variable since you explicitly declared it as Range.

@user1644564 2014-08-25 09:54:22

Always state the workbook, worksheet and the cell/range.

For example:


Because end users will always just click buttons and as soon as the focus moves off of the workbook the code wants to work with then things go completely wrong.

And never use the index of a workbook.


You don't know what other workbooks will be open when the user runs your code.

@Rick Teachey 2014-11-23 14:33:00

The names of worksheets can change, too, you know. Use codenames instead.

@Rick Teachey 2014-05-28 14:04:33

One small point of emphasis I'll add to all the excellent answers given above:

Probably the biggest thing you can do to avoid using Select is to as much as possible, use named ranges (combined with meaningful variable names) in your VBA code. This point was mentioned above, but glossed over a bit; however, it deserves special attention.

Here are a couple additional reasons to make liberal use of named ranges though I am sure I could think of more.

Named ranges make your code easier to read and understand.


Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
'e.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
'e.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

It is pretty obvious what the named ranges Months and MonthlySales contain, and what the procedure is doing.

Why is this important? Partially because it is easier for other people to understand it, but even if you are the only person who will ever see or use your code, you should still use named ranges and good variable names because YOU WILL FORGET what you meant to do with it a year later, and you will waste 30 minutes just figuring out what your code is doing.

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

Consider, if the above example had been written like this:

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1 
    Debug.Print rng2(rng3.Row)
Next rng3

This code will work just fine at first - that is until you or a future user decides "gee wiz, I think I'm going to add a new column with the year in Column A!", or put an expenses column between the months and sales columns, or add a header to each column. Now, your code is broken. And because you used terrible variable names, it will take you a lot more time to figure out how to fix it than it should take.

If you had used named ranges to begin with, the Months and Sales columns could be moved around all you like, and your code will continue working just fine.

@brettdj 2015-02-27 08:15:31

The debate about whether named ranges are good or bad spreadsheet design continues - I'm firmly in the no camp. In my experience they increase errors (for standard users who have no need of code).

@Rick Teachey 2015-02-27 15:24:42

I wasn't even aware there is a debate.

@brettdj 2015-02-28 07:23:52

@Rick Teachey 2015-02-28 20:17:08

Interesting. Though it seems like the point of that paper isn't so much that names cause problems, but bad use of names causes problems. In civil engineering (my field), names are absolutely essential and in my experience not using them leads in general to more problems than using them. But as with anything they need to be used correctly.

@DeanOC 2015-03-24 23:08:57

brettdj has totally missed the point on this one. The paper he refers to is concerned with the use of range names in a spreadsheet. This has nothing to do with Rick's recommendation which is basically that when writing code give your variables a name reflecting their purpose (which is best practice in other languages such as VB.NET and C#).

@Rick Teachey 2015-03-25 01:04:33

Well to be fair, I was recommending both. I still think using them in tandem is usually a good idea but the paper makes some good points worth considering.

@DeanOC 2015-03-25 01:18:53

I agree with your development philosophy; however I think the paper is nonsense. It talks about how range names can confuse novices who are debugging spreadsheets, but anyone who uses novices to look at complex spreadsheets gets what they deserve! I used to work for a firm who reviewed financial spreadsheets, and I can tell you that it is not the sort of job you give to a novice.

@Excel Hero 2015-08-21 00:28:31

There is no meaningful debate. Anyone who argues against defined names has not taken the time to fully understand their ramifications. Named formulas may be the single most profound and useful construct in all of Excel.

@brettdj 2016-07-14 08:11:23

From the FAST Standard, Section 3.03-08 Do not use Excel Names. "The FAST Standard advocates a very limited and precise use of Named Ranges."

@Marcus Mangelsdorf 2017-11-03 10:35:25

@brettdj: Your citation is correct, but you forgot to mention that it is followed by six "Except..." phrases. One of them being: "Except as a substitute for cell references in macro coding Always use Excel Names as a substitute for cell references when constructing macros. This is to avoid errors arising from the insertion of additional rows or columns whereby the macro coding no longer points to the intended source data."

@brettdj 2017-11-05 05:34:36

@MarcusMangelsdorf Given I did say "The FAST Standard advocates a very limited and precise use of Named Ranges." I don't think I misrepresented this. FWIW I think macros should have even more limited used than Names. :)

@nateAtwork 2017-11-29 19:39:49

@brettdj I like the paper you cited (How do Range Names Hinder Novice Debugging Performance?), and I may take that into account in the future, but in my experience it doesn't address the main reason for using named ranges. I typically don't use named ranges to ease debugging, but to prevent it. Named ranges update automatically when rows are added or if cells are cut and pasted, so if you are using a spreadsheet that may get reformatted, you would be crazy not to use named ranges. You would need to go through line by line after cosmetic updates to a form!

@Siddharth Rout 2012-05-23 10:33:36

Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... should be avoided

  1. It slows down your code.
  2. It is usually the main cause of runtime errors.

How do we avoid it?

1) Directly work with the relevant objects

Consider this code

Selection.Value = "Blah"
Selection.NumberFormat = "@"

This code can also be written as

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) If required declare your variables. The same code above can be written as

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

@user3032689 2016-02-02 10:04:14

That's a good answer, but what I am missing on this topic is when we actually need Activate. Everyone says it is bad, but no one explains any cases where it makes sense to use it. For example I was working with 2 workbooks and could not start a macro on one of the workbooks without activating it first. Could you elaborate a bit maybe? Also if for example I do not activate sheets when copying a range from one sheet to another, when I execute the program, it seems to activate the respective sheets anyways, implicitly.

@Nick 2016-11-25 14:34:57

I find that you may sometimes need to activate a sheet first if you need to paste or filter data on it. I would say its best to avoid activating as much as possible but there are instances where you need to do it. So keep activating and selecting to an absolute minimum as per the answer above.

@robotik 2017-06-20 12:52:00

i think the point is not to completely avoid using them, but just as much as possible. if you want to save a workbook, so that when someone opens it a certain cell in a certain sheet is selected, then you have to select that sheet and cell. copy/paste is a bad example, at least in the case of values, it can be done faster by a code such as Sheets(2).[C10:D12].Value = Sheets(1).[A1:B3].Value

@phrebh 2018-01-09 13:30:59

@Nick You don't need to Activate sheets to paste to them or filter them. Use the sheet object in your paste or filter commands. It becomes easier as you learn the Excel object model through practice. I believe the only time I use .Activate is when I create a new sheet, but I want the original sheet to appear when the code is done.

@GMalc 2019-02-28 18:44:25

@phrebh You don't need to use .Activate to move to the original sheet, just use Application.Goto

@Francesco Baruchelli 2012-05-23 06:11:28

Please note that in the following I'm comparing the Select approach (the one that the OP wants to avoid), with the Range approach (and this is the answer to the question). So don't stop reading when you see the first Select.

It really depends on what you are trying to do. Anyway a simple example could be useful. Let's suppose that you want to set the value of the active cell to "foo". Using ActiveCell you would write something like this:

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

If you want to use it for a cell that is not the active one, for instance for "B2", you should select it first, like this:

Sub Macro2()
End Sub

Using Ranges you can write a more generic macro that can be used to set the value of any cell you want to whatever you want:

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

Then you can rewrite Macro2 as:

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

And Macro1 as:

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

Hope this helps to clear things up a little bit.

@BiGXERO 2012-05-23 06:33:06

Thanks for the excellent response so quickly. So does that mean that if i would normally add cells to range, name the range, and iterate through it, i should jump straight to creating an array?

@Francesco Baruchelli 2012-05-23 06:50:20

I'm not sure I understand what you mean, but you can create a Range with a single instruction (e.g. Range("B5:C14")) and you can even set its value at once (if it has to be the same for every cell in the range), e.g. Range("B5:C14").Value = "abc"

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1005802 View
  • 1745 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

22 Answered Questions

[SOLVED] Is there a way to crack the password on an Excel VBA Project?

  • 2009-06-22 10:37:13
  • Jonathan Sayce
  • 1017664 View
  • 442 Score
  • 22 Answer
  • Tags:   excel vba passwords

3 Answered Questions

1 Answered Questions

[SOLVED] Passing object Variables from cells into (Excel) VBA

5 Answered Questions

[SOLVED] "Can't find Project or Library" for standard VBA functions

1 Answered Questions

Excel VBA Connection to Oracle

1 Answered Questions

[SOLVED] Avoiding Select in VBA Excel macros with variable ranges

  • 2015-07-01 15:41:48
  • Anton Gusev
  • 462 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

2 Answered Questions

[SOLVED] Avoiding the use of Activate and Select when working with charts (Excel)

  • 2013-12-09 19:50:52
  • Mr.Kinn
  • 4564 View
  • 5 Score
  • 2 Answer
  • Tags:   excel excel-vba vba

2 Answered Questions

[SOLVED] Can't see drop down list in Excel VBA

  • 2010-03-25 18:48:08
  • Ten Ton Gorilla
  • 3024 View
  • 4 Score
  • 2 Answer
  • Tags:   excel excel-vba vba

Sponsored Content