CODEWITHSUNDEEP
excelvbasyntax
mburn23
mburn23

Reputation: 17

VBA .Range Two Brackets or Parentheses

Trying to work with an existing piece of VBA code and trying to figure out what this piece is doing. It is referring to certain cells but I would like to know the syntax please.

This is from an existing piece of code I'm trying to update.

FilePath = ActiveSheet.Range("rng_File_Path")(iRow, 1).Value

What do the side by side parentheses do in the .Range function?

Upvotes: 0

Views: 699

Answers (3)

Error 1004
Error 1004

Reputation: 8230

  • ActiveSheet: Refer to the sheet which is activated at the point the code executed
  • Range("rng_File_Path"): Refer to a range which is called "rng_File_Path". You could find the exact range address in: Formulas Tab - Defined Names - Name Manager
  • .Cells(iRow, 1): Refer to the cell with row - iRow (iRow most propably takes its value from a loop) & column 1 from "rng_File_Path"

In my opinion, looping a named range using this approach is wrong. Let's say the named range has 4 rows and 3 columns and you refer to .Range("rng_File_Path").Cells(20, 10).Value. The answer will be Nothing instead of error. The named range has only 4 rows (you are looking for row no. 20) and 3 columns (you are looking for column no. 10)

Upvotes: 0

RobertBaron
RobertBaron

Reputation: 2854

It is equaivalent to:

FilePath = ActiveSheet.Range("rng_File_Path").Cells(iRow, 1).Value

Upvotes: 2

Mikku
Mikku

Reputation: 6664

To access the (irow,1) th Value of the Range("rng_File_Path")

For example if I have a Range that refers as:

 rng_File_Path = Range("A1:A4")

then:

Range("rng_File_Path")(2,1) will give me A2

Upvotes: 1

Related Questions