Shortening VBA

[CVDK]

Hi
In pursuit of making code shorter with fewer lines etc. there's something I don't understand. It's propably easy to explain for a couple of guys in here.

Why does this work?

 Activeworkbook.Sheets("A").Range(Departments, Departments).Copy

While this does not work (I have to split the line below in two in order to make it work)?

 Activeworkbook.Sheets("A").Departments.Copy

I've defined/dimmed "Departments" as range.

 

[pgc01]

I've defined/dimmed "Departments" as range.

Hi

If you've defined Departments as range then you can use:

Departments.Copy

 

[CVDK]

Yes, but that only works if the workbook is active. I just wrote activeworkbook instead of the actual workbook name wich i gues was not the smartest thing to do.

 

[pgc01]

Yes, but that only works if the workbook is active.

No, the range refers to its own workbook.

For ex., I define a range:

Dim r As Range
 
Set r = Workbooks("Test.xlsm").Worksheets("Sheet1").Range("a1")

Now, when another workbook is active, if you use:

r.Copy

its the range in the workbook Test.xlsm that is copied. There's no confusion becase the range r refers to the range in Test.xlsm, it doesn't matter which workbook is active.

 

[RSXchin]

can I use this on CLOSED books?

 

[pgc01]

No. This code assumes the workbook is open.

There are ways to get info from closed workbooks, but usually it's not worth it. It's easy to open the workbook, get the information and close it. You can use the Application.ScreenUpdating to False during the execution of the code to speed up the process.

If, anyway, you really want to get the info from a closed workbook, you can use the old ExecuteExcel4Macro() if it's just a couple of cells, or create a connection to the workbook and use ADO.

If you are not familiar with this, maybe this helps:

http://www.exceltip.com/st/Read_inf...orkbook_using_VBA_in_Microsoft_Excel/473.html

 

[RSXchin]

The reason I ask is, on our machines, when I close a file (yes, EVERY friggin file) it asks me if everything is correct.

Sendkeys can't be used to close it automatically.

 

[pgc01]

If that's the problem open the files with ReadOnly to make sure you change nothing and when you want to close it use:

Application.DisplayAlerts = False
' close the workbook
Application.DisplayAlerts = True

 

[CVDK]

Thanks PGC
I did not include the workbook in my definition.