Reference

DESCRIPTION:
As a programmer, it’s a good practice to add references you need on the body of the codes rather than manually added to the application. This gives an assurance that the application always work whenever new implementation is done. There are 2 ways to add references in VBA:
1. Via GUID
2. Via File reference

Add Reference Programaticaly

Sub X_Ref_Add_FSO_m()
	' You need to have Reference “Microsoft scripting Runtime” to access FileSystemObject
	' This procedure to add the reference programatically
	'
	Dim ID As Object

	Set ID = ThisWorkbook.VBProject.references
	ID.AddFromFile “C:\Windows\SysWOW64\scrrun.dll”

	If Err.Number <> 32813 And Err.Number <> 0 Then '32813 = exist and 0=do no exist
	MsgBox Err.Number, Err.Description, Err.HelpFile, Err.HelpContext, “Microsoft scripting Runtime”
	Else
	MsgBox Err.Number & ” has been added successful”, vbCritical
	End If
End Sub


Sub FileSystemObject_implementation()
	'This procedure will fail if no “Microsoft Scripting Runtime” added in Reference
	'
	Dim fso As FileSystemObject

	Set fso = New FileSystemObject
	Dim stream As TextStream
	Set stream = fso.CreateTextFile(“C:\work\Test.log”, True)
	stream.WriteLine “This line uses the WriteLine method.”
	stream.Write “This line uses the Write method.”
	stream.Close
End Sub

Sub X_Ref_Add_FSO_m()

' You need to have Reference “Microsoft scripting Runtime” to access FileSystemObject

' This procedure to add the reference programatically

Dim ID As Object

Set ID = ThisWorkbook.VBProject.references

ID.AddFromFile “C:\Windows\SysWOW64\scrrun.dll”

If Err.Number <> 32813 And Err.Number <> 0 Then '32813 = exist and 0=do no exist

MsgBox Err.Number, Err.Description, Err.HelpFile, Err.HelpContext, “Microsoft scripting Runtime”

Else

MsgBox Err.Number & ” has been added successful”, vbCritical

End If

End Sub

Sub FileSystemObject_implementation()

'This procedure will fail if no “Microsoft Scripting Runtime” added in Reference

Dim fso As FileSystemObject

Set fso = New FileSystemObject

Dim stream As TextStream

Set stream = fso.CreateTextFile(“C:\work\Test.log”, True)

stream.WriteLine “This line uses the WriteLine method.”

stream.Write “This line uses the Write method.”

stream.Close

End Sub

Via GUID

Private Function AddScriptingLibrary() As Boolean

	Const GUID As String = "{420B2830-E718-11CF-893D-00A0C9054228}"

	On Error GoTo errHandler
	ThisWorkbook.VBProject.References.AddFromGuid GUID, 1, 0
	AddScriptingLibrary = True
	MsgBox "Scripting ref has been added", vbOKOnly
	Exit Function

errHandler:
	MsgBox Err.Description
End Function

Private Function AddScriptingLibrary() As Boolean

Const GUID As String = "{420B2830-E718-11CF-893D-00A0C9054228}"

On Error GoTo errHandler

ThisWorkbook.VBProject.References.AddFromGuid GUID, 1, 0

AddScriptingLibrary = True

MsgBox "Scripting ref has been added", vbOKOnly

Exit Function

errHandler:

MsgBox Err.Description

End Function

List References with GUID and Path info to Excel

Sub ListReferencePaths()
    'To list all References along with path and GUID

    On Error Resume Next
    Dim i As Long
    With ThisWorkbook.Sheets(1)
        .Cells.Clear
        .Range("A1") = "Reference name"
        .Range("B1") = "Full path to reference"
        .Range("C1") = "Reference GUID"
    End With
    For i = 1 To ThisWorkbook.VBProject.References.Count
    With ThisWorkbook.VBProject.References(i)
        ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = .Name
        ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1) = .FullPath
        ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 2) = .GUID
    End With
    Next i
     On Error GoTo 0
End Sub

Sub ListReferencePaths()

'To list all References along with path and GUID

On Error Resume Next

Dim i As Long

With ThisWorkbook.Sheets(1)

.Cells.Clear

.Range("A1") = "Reference name"

.Range("B1") = "Full path to reference"

.Range("C1") = "Reference GUID"

End With

For i = 1 To ThisWorkbook.VBProject.References.Count

With ThisWorkbook.VBProject.References(i)

ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = .Name

ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1) = .FullPath

ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 2) = .GUID

End With

Next i

On Error GoTo 0

End Sub

Ratings: 1 Star 2 Stars 3 Stars 4 Stars 5 Stars (No Ratings Yet)
Loading...

Tags: Last column, Last row

YYkol.com

Reference

Reference

Add Reference Programaticaly

Via GUID

List References with GUID and Path info to Excel

Leave a Reply