Asked  7 Months ago    Answers:  5   Viewed   88 times

I've written a program that runs and messages Skype with information when if finishes. I need to add a reference for Skype4COM.dll in order to send a message through Skype. We have a dozen or so computers on a network and a shared file server (among other things). All of the other computers need to be able to run this program. I was hoping to avoid setting up the reference by hand. I had planned on putting the reference in a shared location, and adding it programmatically when the program ran.

I can't seem to figure out how to add a reference programmatically to Excel 2007 using VBA. I know how to do it manually: Open VBE --> Tools --> References --> browse --_> File Location and Name. But that's not very useful for my purposes. I know there are ways to do it in Access Vb.net and code similar to this kept popping up, but I'm not sure I understand it, or if it's relevant:

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

So far, in the solutions presented, in order to add the reference programmatically I will need to add a reference by hand and change the Trust Center - which is more than just adding the reference. Though I guess if I follow through with the solutions proposed I will be able to add future references programmatically. Which probably makes it worth the effort.

Any further thoughts would be great.

 Answers

93

Ommit

There are two ways to add references via VBA to your projects

1) Using GUID

2) Directly referencing the dll.

Let me cover both.

But first these are 3 things you need to take care of

a) Macros should be enabled

b) In Security settings, ensure that "Trust Access To Visual Basic Project" is checked

enter image description here

c) You have manually set a reference to `Microsoft Visual Basic for Applications Extensibility" object

enter image description here

Way 1 (Using GUID)

I usually avoid this way as I have to search for the GUID in the registry... which I hate LOL. More on GUID here.

Topic: Add a VBA Reference Library via code

Link: 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

Way 2 (Directly referencing the dll)

This code adds a reference to 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:WINDOWSsystem32vbscript.dll3"

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 :)

Tuesday, June 1, 2021
 
mgraph
answered 7 Months ago
47

Whilst slightly outside of the scope of my original requirements, I have found a solution to the problem that I can work with.

As it turns out, if you receive an email with a vcard in it as an attachment, then you are able to open this attachment and add it to your address book. So, if instead of offering a vcard download link to iPhone users you provide them with a way to be sent the vcard via email instead then they are able to add the card to their address book.

A slightly roundabout solution, but about the best I'm able to come up with whilst Apple don't allow you to do it natively. Certainly it's a better solution than doing nothing at all.

I ended up writing a blog post about this: Adding a vCard to your iPhone Address Book from a web page

Wednesday, July 28, 2021
 
Lloydworth
answered 4 Months ago
81

In answer to your question about late binding, this involves replacing the line of code

Dim Connection As ADODB.Connection

with

Dim Connection As object

and replacing

Set Connection = New ADODB.Connection

with

Set Connection = GetObject(, "ADODB.Connection")

And similarly for the other objects from that library.

Now, I am not sure if this will fix the actual issue that you are having. It sounds like there is a bug in the ActiveX library and you are hitting it, although nothing you are doing seems particularly esoteric.

Friday, August 27, 2021
 
bimal
answered 3 Months ago
74
Public Sub Node_Button_Duplication()
    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5

    '~~> This will give you the name
    Debug.Print Selection.Name
End Sub

FOLLOWUP

If you know the name of the commandbutton then you can change the properties like this.

Option Explicit

Sub Sample()
    Dim shp As Shape

    '~~> Since you already have the name replace "CommandButton1" by
    '~~> the name that you have
    Set shp = ActiveSheet.Shapes("CommandButton1")

    With shp.OLEFormat.Object
        .Object.Caption = "Test"
        .Left = 15
        .Top = 15
    End With
End Sub

You can also combine the above two like this

Public Sub Node_Button_Duplication()
    Dim shp As Shape

    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5

    '~~> This will give you the name
    Debug.Print Selection.Name

    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object
        .Object.Caption = "Test"
        .Left = 15
        .Top = 15
    End With

End Sub

And if you need to iterate through all the buttons then use this code.

Sub CommanButtons()
    Dim wks As Worksheet
    Dim OLEObj As OLEObject

    '~~> set it as per the relevant sheet
    Set wks = Worksheets("sheet1")

    For Each OLEObj In wks.OLEObjects
        If TypeOf OLEObj.Object Is MSForms.CommandButton Then
            Debug.Print OLEObj.Object.Caption
        End If
    Next OLEObj
End Sub
Tuesday, August 31, 2021
 
starian chen-MSFT
answered 3 Months ago
68

I had this exact same question. I couldn't find an answer so I just tried the guess and check method. The following code seems to work:

CFErrorRef error = NULL; 
ABAddressBookRef iPhoneAddressBook = ABAddressBookCreate();
ABRecordRef newPerson = ABPersonCreate();
ABRecordSetValue(newPerson, kABPersonFirstNameProperty, @"Jane", &error);
ABRecordSetValue(newPerson, kABPersonLastNameProperty, @"Smith", &error);

const CFStringRef customLabel = CFSTR( "mylabel" );

//phone
ABMutableMultiValueRef multiPhone = ABMultiValueCreateMutable(kABMultiStringPropertyType);
ABMultiValueAddValueAndLabel(multiPhone, @"1-444-444-444", kABPersonPhoneMainLabel, NULL);
ABMultiValueAddValueAndLabel(multiPhone, @"1-333-333-333", kABPersonPhoneMobileLabel, NULL);            
ABMultiValueAddValueAndLabel(multiPhone, @"1-666-666-666", kABOtherLabel, NULL);        
ABMultiValueAddValueAndLabel(multiPhone, @"1-555-555-555", customLabel, NULL); 
ABRecordSetValue(newPerson, kABPersonPhoneProperty, multiPhone,nil);
CFRelease(multiPhone);

ABAddressBookAddRecord(iPhoneAddressBook, newPerson, &error);
ABAddressBookSave(iPhoneAddressBook, &error);

if (error != NULL)
{   
    NSLog(@"Error!");   
}

If you check the address book, you will see a phone number with a custom label: mylabel

Thanks to: this post

And to: this blog

Wednesday, September 22, 2021
 
Ben
answered 2 Months ago
Ben
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share