Set workbook to variable either open or not

[YasserKhalil]

Hello everyone

I need to assign a variable wb to a workbook but I need to deal with this workbook either it is open or not
If it is closed I would use this line

Code: Select all

Sub Test()
    Dim wb As Workbook
    
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\Sampl.xlsm")
    
End Sub

But I need to deal with the workbook either it is open or closed and store it in the variable wb

[Doc.AElstein]

Hi Yasser,
I think I have only ever seen that done with error handling

Code: Select all

Sub Test()
Dim Wb As Workbook
 On Error Resume Next
 Set Wb = Workbooks("Sampl.xlsm") ' Workbooks is the collection object of all open workbooks: It will fail causing a run time error if the workbook, Wb,  is not open    
 On Error GoTo 0 ' Always best to turn off this error handling as soon as you are finished with it
  If Wb Is Nothing Then  Set Wb = Workbooks.Open(ThisWorkbook.Path & "\Sampl.xlsm")
End Sub

[YasserKhalil]

Thanks a lot Mr. Alan
I have tried this too but I welcome any fix to my try

Code: Select all

Sub Test()
    Dim wb      As Workbook
    
    On Error Resume Next
    Set wb = Workbooks("Sample.xlsm")
    If Not Err = 0 Then Set wb = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm")
    On Error GoTo 0
    
    MsgBox wb.Name
End Sub

[Doc.AElstein]

Err is the Error object
You probably wanted one of its Properties
Err.Number

If you were using
Option Explicit
you would have seen your error

ErrDot.JPG : https://imgur.com/ZXOlhZ1" onclick="window.open(this.href);return false;

ErrDot.JPG

[YasserKhalil]

Yes Mt. Alan
I am using Option Explicit and in immediate window when following the code for this first time before the workbook Sample is open I got Err.Number equals to 9 in the immediate window ..
Thanks a lot for contributions

[Doc.AElstein]

So all is OK now?
Problem is solved ??

[YasserKhalil]

Yes solved. But I welcome any other ideas if there. I would like to learn more and get more and more solutions for any problem
This helps me a lot to learn

[Doc.AElstein]

I would like to learn more and get more and more solutions for any problem
This helps me a lot to learn

This is very good - You have also helped me to discover something new.....
_.______________________

I am using Option Explicit and in immediate window when following the code for this first time before the workbook Sample is open I got Err.Number equals to 9 in the immediate window ..

You are correct. How very strange. I also get Err = 9 if workbook is not open
(Edit.... some of the next is not quite right ... see http://www.eileenslounge.com/viewtopic. ... 96#p247141" onclick="window.open(this.href);return false; )

Err is 9.JPG : https://imgur.com/rH7TkgK" onclick="window.open(this.href);return false;

Err is 9.JPG

But it changes to 0 after you try to use it in If Not Err = 0 Then ( even when the workbook is not open )
Err is 0.JPG https://imgur.com/HX0Dwoe" onclick="window.open(this.href);return false;

Err is 0.JPG

So I have also learnt something :) , thanks
( I was wrong: ...with Option Explicit ........ Err does not error. This I did not expect. It is very strange )

You have noticed an interesting phenomena. It is strange
I was wrong. I did not know that. I am very glad to know when I am wrong.
Important for me is to know the answer. So it is very good when I see that I am wrong.
Good work, Yasser

[YasserKhalil]

Thanks a lot Mr. Alan
We all learn but I learn more from you

[HansV]

Number is the default property of the Err object. So if you refer to Err without specifying a property, you get Err.Number.

[Doc.AElstein]

Thanks Hans, I did not know that. I thought it might be , but was not sure. I know that .Value is the default for many things, but I did not know what was the default for Err

In fact I was in error a second time, because my coding had the .._
On Error GoTo 0
_.. in the place to make it 0 too soon
It was not a strange phenomena making Err = 0 , it was my On Error GoTo 0 making Err = 0

So there is nothing strange about what is going on
In fact, Yasser’s original routine does work for me, _..._
_.. If , for example , my file “ClosedWorkbook.xlsm” exists at the place, ThisWorkbook.Path & "\ClosedWorkbook.xlsm" , then his original coding does work for me.

Code: Select all

 Sub TestC()
Dim wb      As Workbook
 On Error Resume Next
 Set wb = Workbooks("ClosedWorkbook.xlsm")
    If Not Err = 0 Then Set wb = Workbooks.Open(ThisWorkbook.Path & "\ClosedWorkbook.xlsm")
 On Error GoTo 0
    MsgBox wb.Name
End Sub

I am glad you answered so I re checked what I had written… there is not anything strange going on.
But it is probably good practice to use Err.Number , if you want the Number, just as it is good practice to use Range.Value when you want the value
I was wrong 2 out of 2 times today. ... I guess if your going to be wrong, then you might as well do it properly and be wrong lots .. Lol
( But I still learnt that Err wont get picked up as an error by Option Explicit - that is good to know )

[Doc.AElstein]

I hope I get this right……
Usually you should try to avoid error handling: use it only if there is no other way.
These ways do not use error handling

Code: Select all

Sub TestD()
Dim WbStear As Workbook, Wb As Workbook
    For Each WbStear In Workbooks
     If WbStear.Name = "Sample.xlsm" Then Set Wb = Workbooks("Sample.xlsm"): Exit For
    Next WbStear
    If Wb Is Nothing Then Set Wb = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm")
End Sub
Sub TestE()
Dim Wb As Workbook, WbNme As String
    For Each Wb In Workbooks
     Let WbNme = Wb.Name
       If WbNme = "Sample.xlsm" Then Set Wb = Workbooks("Sample.xlsm"): Exit For
    Next Wb
    If WbNme <> "Sample.xlsm" Then Set Wb = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm") ' : Let WbNme = Wb.Name
End Sub
Sub TestF()
Dim Wb As Workbook
    For Each Wb In Workbooks
       If Wb.Name = "Sample.xlsm" Then Set Wb = Workbooks("Sample.xlsm"): GoTo IsOpen
    Next Wb
 Set Wb = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm")
IsOpen:
End Sub
Sub TestG()
Dim Wb As Workbook
    For Each Wb In Workbooks
       If Wb.Name = "Sample.xlsm" Then Set Wb = Workbooks("Sample.xlsm"): GoTo IsOpen
    Next Wb
 If Not Dir(ThisWorkbook.Path & "\Sample.xlsm") = "" Then Set Wb = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm")
IsOpen:
End Sub

[HansV]

Press F2 in the Visual Basic editor to display the Object Explorer.
Search for Err. You'll see that Err is a function that returns an ErrObject:

S2545.png

The Number property of ErrObject has a special icon, indicating that it is the default:

S2546.png

[Doc.AElstein]

Thanks Hans
I seem to have missed out totally learning about the Object Explorer. I’m not too sure why. I don’t think I have ever hardly used it.
I do remember that early in my learning VBA, a few people said to me that you need to know what you want in order to look for what you want.
I can see their point: To get the information you showed… I had to have that information up in from of me already ( your screenshots) , and then I searched around until I found the same .
I can’t see any logic to how I would find that information from the Object Explorer, unless I had your screenshots in the first place.
Using the Object Explorer currently to me seems total un intuitive, it is totally lost on me
Just my usual ignorance or slowness to get the point, I guess.
_._____________________________________________-

... Err is a function that returns an ErrObject...

…… Err is a function that returns an ErrObject……
I never heard that before. I think I need to remember that. I did see it at some point just now,
Function Err() As ErrObject
ErrFunctionAsObject.JPG : http://i.imgur.com/YBH9Lrq.jpg https://imgur.com/gpBpDDs" onclick="window.open(this.href);return false;


Finally after selecting ErrObject , I get a similar screenshot to your final one
ErrObject.JPG : : http://i.imgur.com/bKyNNPP.jpg https://imgur.com/qCdbGHL" onclick="window.open(this.href);return false;

If I look carefully at that last screen shot I see a small green dot,
( you may need to click on the imgur link to get a better resolution - Look to the left of ‘_-Number in the Element von ‘ErrObject’ window)
- so I can see, as you mentioned, a way to find the default property. That is a similar thing to your ….. special icon …. That is also useful to know, thanks.

But I still see no way to navigate to this final point, without already having your screenshots to start with. So the Object Explorer is, unfortunately, lost on me.
I don’t think I have found a good Blog on the object explorer. But I should say I have never really looked for one yet...

But I think I am going to add that phrase … Err is a function that returns an ErrObject… to a lot of my notes now. Thanks for that.
Or rather, I think I will substitute that for all the times I have written …… Err is an object…. …
Instead of writing …… Err is an object…. … , I will instead write …Err is a function that returns an ErrObject…

That statement explains exactly why .._
Err
_.. does not fail . ……
It does not fail, because, somewhere, hidden internally in Excel , God only knows where, is something pseudo like

Function Err( ) As Object
If no Dot thing given…. Then Err is = the Err.Number
End Function

I expect it is a bit more complicated than that, but that explains why.._
Err
_.. does not fail: It is pseudo like a function call … and somehow that is wired to return the Property of .Number by default

Code: Select all

 Sub TestWaysToUseFunctionErr()
 Call Err ' A way to use a Function
 Err ' Another  way to use a Function
Dim vTemp
 Let vTemp = Err() ' Another way to use a Function
End Sub

Alan

[HansV]

The green dot in the icon indicates the default property or method of an object. In the latest version of the Visual Basic Editor, this has been replaced with an entirely different icon (I have Office 2019).

[Doc.AElstein]

Thanks, I expected that was the case..