Chrome
Sub TestEvaluateVBA1b_n1n2n3FoxTest()
Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")
End SubSub TestEvaluateVBA1b_n1n2n3FoxTest()
Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")
End Sub
Some Notes on On Error Resume Next usage
On Error Resume Next is bad
It is like a dopey bitch acting like a blind Bull in a china shop, with the exception that some coding at least notes what error was last hidden.
Usually the advice is to only use an On Error Resume Next when you are expecting an error but can’t think of any other way to check for it that does not raise an exception . Further you should then use the On Error GoTo 0 as soon as possible after to remove the error handler ( “turn it off”, “un plug it”, disable it )
Remember we can do this On Error GoTo 0 at any point even after the On Error Resume Next has been used, as the exception is cleared by On Error Resume Next (Actually, On Error GoTo 0 can also be used in the exception state and also disables the error handler but has no noticeable effect if the exception is still raised )
Both On Error GoTo -1 and On Error GoTo 0 clear the Err object
It was perhaps reasonable to expect that On Error GoTo -1 cleared the information from the Err object. It may not be so obvious that On Error GoTo 0 also does this. So if you wanted to use a check on the Err properties after an On Error Resume Next in order to ascertain if and what error had been “hidden” , then you must do that before any On Error GoTo 0 or On Error GoTo -1.
On Error GoTo -1 and Err.Clear clear the Err object of information
As we noted above, initially one might think that On Error GoTo -1 has no useful function when a On Error Resume Next is in place, as effectively any resume type statement effectively does On Error GoTo -1, but for the unique case of On Error Resume Next which maintains the Err properties of the last error, the use of On Error GoTo -1 gives the possibility to clear the properties of the Err object, without disabling the error handler. But note, that the method Clear, that is to say Err.Clear, can also be used to do that.
But you never know, some crazy combination of all the statements might best suit some messy system
On Error Resume Next is bad. In most cases there is a better alternative to using On Error Resume Next .
Often it is a quick workaround. That tends to be how I have used it.
I don’t think I should have used it in such cases.
_a) By definition a work a round is bad.
-b) Often it is jus ignorance as I don’t know ( yet ) the alternatives
Here some example of how I am using it. Maybe I will add to them, or give the better alternative not using error handling, if I ever figure it out.
Maybe from time to time I will add other examples of error handling generally to the end of this thread and welcome any comments or additions
Late Early Binding.
Only very rarely there are advantages in using Early Binding in preference to late Binding in a final shared File. For development the Early Binding is often preferable as this somehow seems to make an initial link or reference such that intellisense knows what is available. This requires however the checking of a library in the _ VB Editor – - - Tools – - - references _ list
The Late Binding alternative uses the CreateObject Method whose (“string”) argument , ignored by compile , is used at run time to “find” the library of the given name.
Well… I had some existing files which had a lot of Early Binding, and for the time being I did not want to change them.
The current problem example had an Early Binding reference to Word, done on a Office 2007 machine.
I got broken reference errors then on 2010 office versions. I also wanted the File to work in Excel 2003
I found by a bit of experimenting and Forum involvement _..
https://www.excelforum.com/developme...ml#post4820111
https://www.excelforum.com/excel-pro...ml#post4821675
_.. that a Globally Unique Identifier (GUID) appeared a fairly reliable to reference the appropriate libraries. A short code I found could be reliably used to check the reference programmatically via its GUID.
I don’t know yet if there is a good reference list for all GUIDs, but a simple code I could use to get a list of my checked references. For my example I checked the reference to Word on different Office versions and ran this code:
Code:
Sub RefItsGUIDsAndStuff()
Dim It As Variant
For Each It In ThisWorkbook.VBProject.References
Dim strIts As String
Let strIts = strIts & "Description:" & vbTab & It.Description & vbCr & "Name:" & vbTab & vbTab & It.Name & vbCr & "Buitin:" & vbTab & vbTab & It.BuiltIn & vbCr & "Minor:" & vbTab & vbTab & It.minor & vbCr & "Major:" & vbTab & vbTab & It.major & vbCr & "FullPath:" & vbTab & vbTab & It.fullpath & vbCr & "GUID:" & vbTab & vbTab & It.GUID & vbCr & "Type:" & vbTab & vbTab & It.Type & vbCr & "Isbroken:" & vbTab & vbTab & It.isbroken & vbCr & vbCr
Next It
Debug.Print strIts ' From VB Editor Ctrl+g to Immediate Window
End Sub
Some results for different Excel Versions
Code:
Excel 2007
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 12.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 6
Major: 1
FullPath: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 12.0 Object Library
Name: Office
Buitin: Falsch
Minor: 4
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Forms 2.0 Object Library
Name: MSForms
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\FM20.DLL
GUID: {0D452EE1-E08F-101A-852E-02608C4D0BB4}
Type: 0
Isbroken: Falsch
Description: Microsoft Scripting Runtime
Name: Scripting
Buitin: Falsch
Minor: 0
Major: 1
FullPath: C:\Windows\system32\scrrun.dll
GUID: {420B2830-E718-11CF-893D-00A0C9054228}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Excel 2003
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 11.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 5
Major: 1
FullPath: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 11.0 Object Library
Name: Office
Buitin: Falsch
Minor: 3
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Excel 2010
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 1
Major: 4
FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 14.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 7
Major: 1
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\SysWOW64\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 14.0 Object Library
Name: Office
Buitin: Falsch
Minor: 5
Major: 2
FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 14.0 Object Library
Name: Word
Buitin: Falsch
Minor: 5
Major: 8
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
I use the following codes to add programmatically the reference. ( The codes are in the ThisWorkbook code module). The reason for the error handler is that I cannot know if the check has already be made where the File might be in use. I think I can only check references that are made. The code would error at the attempt to check a reference already checked.
I could do the following which would be very simple: _ ….
Code:
With ThisWorkbook.VBProject.References
On Error Resume Next '
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2003
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2007
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=5 ' Office 2010
On Error GoTo 0
End With
_.. Typically, and a bad habit, is to use On Error Resume Next for convenience as above
With this following code, I have at least narrowed the chances of the code errorong
Code:
With ThisWorkbook.VBProject.References
On Error Resume Next '
Select Case CLng(Val(Application.Version))
Case 9: ' Excel 2000
Case 10: ' Excel 2002
Case 11: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2003
Case 12: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2007
Case 14: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=5 ' Office 2010
Case 15: temp = "Excel 2013"
Case 16: temp = "Excel 2016 (Windows)"
Case Else: temp = "Unknown"
End Select
On Error GoTo 0
End With
Reply With Quote
Bookmarks