1

I have two variables Temp and aw that are both workbooks. I want to test if aw is empty, and if so, assign it to be the activeworkbook. I tried

if len(aw) = 0 then
 set aw = activeworkbook

but I kept getting an error.

Static aw As Workbook
Dim Temp As Workbook
Set Temp = ActiveWorkbook
If Temp = aw Then
GoTo Here
ElseIf Len(aw) = 0 Then
Set aw = ActiveWorkbook
Else
Application.ScreenUpdating = False
aw.Activate
ActiveSheet.Range("K5:K7").Clear
Set aw = Temp
aw.Activate
Application.ScreenUpdating = True
End If
Here:
aw.Activate
ActiveSheet.Range("K5").Select
Selection.Value = 15 * 60
Community
  • 1
  • 1
Austin
  • 25
  • 4
  • 1
    Why is `aw` a static variable? – cheezsteak Mar 03 '15 at 14:57
  • Better use nothing than 0. – Amen Jlili Mar 03 '15 at 14:57
  • aw is static because I'm wanting it to remember which workbook was active the last time the macro was run, and if that has changed (hence the `If Temp = aw` line), go back to that workbook and clear a cell. It's all part of a convoluted series of macros. @cheezsteak – Austin Mar 03 '15 at 15:45
  • [Avoiding using `ActiveWorkbook`, `Activesheet`, `Selection`, etc](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) It will simplify your project greatly. – cheezsteak Mar 03 '15 at 15:56
  • That's definitely going to be a helpful link when I get the time to make some of those recommended changes. Thanks – Austin Mar 03 '15 at 16:15

1 Answers1

3

The Len function is used to determine the length of a string of characters. It doesn't accept Workbook objects as input. (Not clear what the "length" of a workbook would be anyway!)

What you want to do instead is test whether aw is Nothing:

If aw Is Nothing Then Set aw = ActiveWorkbook
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188