VBA range assignment

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Can I accomplish the following through other means? This is an invalid assignment, and Set fails too:
Function foo()
Dim c As Range
c.Row = 5
End Function
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
This will fail. You haven't set c as anything, remember, it's an Object. You cannot set the Row property of a Range Object, it will already have said property. You can use the property attributes as variables if you'd like. Although as not assigning c to anything makes it fairly hard to retreive any properties from it.

What is it that you're trying to do anyway?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
That would work as it's a Range Object. Although I still don't understand why you would try to set the row property, and not assign it's (property) value to something (e.g. variable, cell value, etc.).
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Thanks. The odd approach is because existing code will be called, which presupposes a particular range has been set. (The actual code is more complex than just "5" also; this was a bare simplification to get the syntax)
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
I figured. ;)

I might suggest, if this function shall be called from another routine, and you wish to pass a range object to the function, use something like this ...

Code:
Function foo(myRange As Range)
And call from your routine like this ...

Code:
myVariable = foo(Range("A1"))
Then you do have the Range Object set, and it passes the variable to the function which you can also make variable instead of 'static'.

Hope this helps, if you're using it like this.
 

Forum statistics

Threads
1,148,525
Messages
5,747,194
Members
424,068
Latest member
Salim khamis

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