프로그래밍 방식으로 참조를 추가하는 방법

프로그래밍 방식으로 참조를 추가하는 방법

---

완료되면 Skype를 실행하고 정보로 메시지를 보내는 프로그램을 작성했습니다. Skype4COM.dllSkype를 통해 메시지를 보내 려면에 대한 참조를 추가해야합니다 . 우리는 네트워크와 공유 파일 서버 (무엇보다도)에 수십 대 정도의 컴퓨터를 가지고 있습니다. 다른 모든 컴퓨터는이 프로그램을 실행할 수 있어야합니다. 손으로 참조를 설정하는 것을 피하고 싶었습니다. 참조를 공유 위치에두고 프로그램이 실행될 때 프로그래밍 방식으로 추가 할 계획이었습니다.

VBA를 사용하여 Excel 2007에 프로그래밍 방식으로 참조를 추가하는 방법을 알아낼 수없는 것 같습니다. 수동으로 수행하는 방법을 알고 있습니다. Open VBE --> Tools --> References --> browse --_> File Location and Name. 그러나 그것은 내 목적에별로 유용하지 않습니다. Access Vb.net 에서이를 수행하는 방법이 있고 이와 유사한 코드가 계속 표시된다는 것을 알고 있지만 이해하거나 관련이 있는지 확실하지 않습니다.

ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:="{0002E157-0000-0000-C000-000000000046}", _
    Major:=5, Minor:=3

지금까지 제시된 솔루션에서 프로그래밍 방식으로 참조를 추가하려면 참조를 추가하고 보안 센터를 변경해야합니다. 이는 참조를 추가하는 것 이상입니다. 제안 된 솔루션을 따라 가면 프로그래밍 방식으로 향후 참조를 추가 할 수있을 것 같습니다. 아마도 노력할만한 가치가 있습니다.

더 이상의 생각이 있으면 좋을 것입니다.

---

Ommit

VBA를 통해 프로젝트에 참조를 추가하는 방법에는 두 가지가 있습니다.

1) GUID 사용

2) dll을 직접 참조합니다.

둘 다 다루겠습니다.

하지만 먼저 이것들은 당신이 돌봐야 할 세 가지입니다

a) 매크로가 활성화되어야합니다.

b) 보안 설정에서 "Visual Basic 프로젝트에 대한 액세스 신뢰"가 선택되어 있는지 확인하십시오.

c) `Microsoft Visual Basic for Applications Extensibility '개체에 대한 참조를 수동으로 설정했습니다.

방법 1 (GUID 사용)

레지스트리에서 GUID를 검색해야하므로 일반적으로이 방법을 사용하지 않습니다. LOL이 싫습니다. 여기 에서 GUID에 대해 자세히 알아 보십시오 .

주제 : 코드를 통해 VBA 참조 라이브러리 추가

링크 : http://www.vbaexpress.com/kb/getarticle.php?kb_id=267

'Credits: Ken Puls
Sub AddReference()
     'Macro purpose:  To add a reference to the project using the GUID for the
     'reference library

    Dim strGUID As String, theRef As Variant, i As Long

     'Update the GUID you need below.
    strGUID = "{00020905-0000-0000-C000-000000000046}"

     'Set to continue in case of error
    On Error Resume Next

     'Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
        Set theRef = ThisWorkbook.VBProject.References.Item(i)
        If theRef.isbroken = True Then
            ThisWorkbook.VBProject.References.Remove theRef
        End If
    Next i

     'Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear

     'Add the reference
    ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:=strGUID, Major:=1, Minor:=0

     'If an error was encountered, inform the user
    Select Case Err.Number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0
End Sub

---

방법 2 (dll을 직접 참조)

이 코드는 다음에 대한 참조를 추가합니다. Microsoft VBScript Regular Expressions 5.5

Option Explicit

Sub AddReference()
    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As VBIDE.Reference
    Dim BoolExists As Boolean

    Set VBAEditor = Application.VBE
    Set vbProj = ActiveWorkbook.VBProject

    '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
    For Each chkRef In vbProj.References
        If chkRef.Name = "VBScript_RegExp_55" Then
            BoolExists = True
            GoTo CleanUp
        End If
    Next

    vbProj.References.AddFromFile "C:\WINDOWS\system32\vbscript.dll\3"

CleanUp:
    If BoolExists = True Then
        MsgBox "Reference already exists"
    Else
        MsgBox "Reference Added Successfully"
    End If

    Set vbProj = Nothing
    Set VBAEditor = Nothing
End Sub

Note: I have not added Error Handling. It is recommended that in your actual code, do use it :)

EDIT Beaten by mischab1 :)

---

There are two ways to add references using VBA. .AddFromGuid(Guid, Major, Minor) and .AddFromFile(Filename). Which one is best depends on what you are trying to add a reference to. I almost always use .AddFromFile because the things I am referencing are other Excel VBA Projects and they aren't in the Windows Registry.

The example code you are showing will add a reference to the workbook the code is in. I generally don't see any point in doing that because 90% of the time, before you can add the reference, the code has already failed to compile because the reference is missing. (And if it didn't fail-to-compile, you are probably using late binding and you don't need to add a reference.)

If you are having problems getting the code to run, there are two possible issues.

1. In order to easily use the VBE's object model, you need to add a reference to Microsoft Visual Basic for Application Extensibility. (VBIDE)
2. In order to run Excel VBA code that changes anything in a VBProject, you need to Trust access to the VBA Project Object Model. (In Excel 2010, it is located in the Trust Center - Macro Settings.)

Aside from that, if you can be a little more clear on what your question is or what you are trying to do that isn't working, I could give a more specific answer.

---

Browsing the registry for guids or using paths, which method is best. If browsing the registry is no longer necessary, won't it be the better way to use guids? Office is not always installed in the same directory. The installation path can be manually altered. Also the version number is a part of the path. I could have never predicted that Microsoft would ever add '(x86)' to 'Program Files' before the introduction of 64 bits processors. If possible I would try to avoid using a path.

The code below is derived from Siddharth Rout's answer, with an additional function to list all the references that are used in the active workbook. What if I open my workbook in a later version of Excel? Will the workbook still work without adapting the VBA code? I have already checked that the guids for office 2003 and 2010 are identical. Let's hope that Microsoft doesn't change guids in future versions.

The arguments 0,0 (from .AddFromGuid) should use the latest version of a reference (which I have not been able to test).

What are your thoughts? Of course we cannot predict the future but what can we do to make our code version proof?

Sub AddReferences(wbk As Workbook)
    ' Run DebugPrintExistingRefs in the immediate pane, to show guids of existing references
    AddRef wbk, "{00025E01-0000-0000-C000-000000000046}", "DAO"
    AddRef wbk, "{00020905-0000-0000-C000-000000000046}", "Word"
    AddRef wbk, "{91493440-5A91-11CF-8700-00AA0060263B}", "PowerPoint"
End Sub

Sub AddRef(wbk As Workbook, sGuid As String, sRefName As String)
    Dim i As Integer
    On Error GoTo EH
    With wbk.VBProject.References
        For i = 1 To .Count
            If .Item(i).Name = sRefName Then
               Exit For
            End If
        Next i
        If i > .Count Then
           .AddFromGuid sGuid, 0, 0 ' 0,0 should pick the latest version installed on the computer
        End If
    End With
EX: Exit Sub
EH: MsgBox "Error in 'AddRef'" & vbCrLf & vbCrLf & err.Description
    Resume EX
    Resume ' debug code
End Sub

Public Sub DebugPrintExistingRefs()
    Dim i As Integer
    With Application.ThisWorkbook.VBProject.References
        For i = 1 To .Count
            Debug.Print "    AddRef wbk, """ & .Item(i).GUID & """, """ & .Item(i).Name & """"
        Next i
    End With
End Sub

The code above does not need the reference to the "Microsoft Visual Basic for Applications Extensibility" object anymore.

---

프로그래밍 방식으로 Guid를 얻는 방법은 다음과 같습니다! 그런 다음 위의 답변과 함께 이러한 guid / filepaths를 사용하여 참조를 추가 할 수 있습니다!

참조 : http://www.vbaexpress.com/kb/getarticle.php?kb_id=278

Sub ListReferencePaths()
'Lists path and GUID (Globally Unique Identifier) for each referenced library.
'Select a reference in Tools > References, then run this code to get GUID etc.
    Dim rw As Long, ref
    With ThisWorkbook.Sheets(1)
        .Cells.Clear
        rw = 1
        .Range("A" & rw & ":D" & rw) = Array("Reference","Version","GUID","Path")
        For Each ref In ThisWorkbook.VBProject.References
            rw = rw + 1
            .Range("A" & rw & ":D" & rw) = Array(ref.Description, _
                   "v." & ref.Major & "." & ref.Minor, ref.GUID, ref.FullPath)
        Next ref
        .Range("A:D").Columns.AutoFit
    End With
End Sub

다음은 동일한 코드이지만 워크 시트를 출력 전용으로 사용하지 않으려면 터미널에 인쇄합니다.

Sub ListReferencePaths() 
 'Macro purpose:  To determine full path and Globally Unique Identifier (GUID)
 'to each referenced library.  Select the reference in the Tools\References
 'window, then run this code to get the information on the reference's library

On Error Resume Next 
Dim i As Long 

Debug.Print "Reference name" & " | " & "Full path to reference" & " | " & "Reference GUID" 

For i = 1 To ThisWorkbook.VBProject.References.Count 
  With ThisWorkbook.VBProject.References(i) 
    Debug.Print .Name & " | " & .FullPath  & " | " & .GUID 
  End With 
Next i 
On Error GoTo 0 
End Sub 

참고 URL : https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically