VBA Code is not portable from one Laptop to the other

[DeanP123]

Hi all,

I have VBA code on one laptop. Functions just fine. On another laptop it doesn't work. It does however compile without any issues or bugs/syntax.

Can anyone advise on what I can do to start diagnosing this?

 

[RoryA]

Welcome to the forum.

What exactly does "doesn't work" mean? Does it raise an error, or do the wrong thing(s), or do nothing at all?

My first question when diagnosing would be, do both computers use the same version of Excel?

 

[DeanP123]

Thanks for quick reply!

The laptop that has VBA code that "works" uses outlook 14.0.7153.5000
The laptope where it doesnt work uses Outlook 14.0.6023.1000

Now into the what makes it "work".

Its essentially VBA code in "ThisOutlookSession" that monitors all mail items coming in. If a mail item has the subject title: "Marketplace - Notification", then it takes the content of the email, parses it, and takes the parsed content and pastes it in a popup which hovers over the screen.

Emails with the subject title on the working laptop trigger a popup that I can see. The non working version doesnt trigger a popup when the same email with subject title comes through.

 

[RoryA]

I've moved your thread as it isn't related to Excel.

Do you get any error messages, or does it simply not produce the popup? Have you tried adding breakpoints or debug statements to the code to see if it's being triggered at all?

 

[DeanP123]

I've moved your thread as it isn't related to Excel.

Do you get any error messages, or does it simply not produce the popup? Have you tried adding breakpoints or debug statements to the code to see if it's being triggered at all?

I added a Print.Debug statement. It produces output in the immediate window on the working laptop. But nothing in the other laptop. Heres the code, its fairly short. You can see I added a print.debug in line 10:

Private WithEvents Items As outlook.ItemsPrivate Sub Application_Startup()
  Dim olApp As outlook.Application
  Dim objNS As outlook.NameSpace
  Set olApp = outlook.Application
  Set objNS = olApp.GetNamespace("MAPI")
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)
Debug.Print "Im alive!!"
  On Error GoTo ErrorHandler
  Dim MyAppID, ReturnValue
  Dim Msg As outlook.MailItem
  Dim vPID As Variant
  Dim arr
  Dim InstrumentBatch
  Dim Instrument
  Dim WithinInstrument
  Dim html As HTMLDocument: Set html = CreateObject("htmlfile")
  If TypeName(item) = "MailItem" Then
    Set Msg = item
    If Msg.Subject = "Marketplace - Notification" Then
        InstrumentBatch = Split(Msg.HTMLBody, "</th></head>")
        Instrument = Split(InstrumentBatch(1), "</td></tr>")
        UserForm2.Instrument = ""
        UserForm2.Direction = ""
        UserForm2.Nominal = ""
        UserForm2.Rate = ""
        UserForm2.Tenor = ""
        UserForm2.PV01 = ""
        For i = 0 To UBound(Instrument) - 1 Step 1
            Instrument(i) = Replace(Instrument(i), "<tr><td>", "|")
            Instrument(i) = Replace(Instrument(i), "</td><td>", "|")
            WithinInstrument = Split(Instrument(i), "|")
            UserForm2.Instrument = UserForm2.Instrument & WithinInstrument(1) & vbNewLine
            If InStr(WithinInstrument(2), "receives") Then
               WithinInstrument(2) = Replace(WithinInstrument(2), "Nr", "N r")
            Else
                WithinInstrument(2) = Replace(WithinInstrument(2), "Np", "N p")
            End If
            UserForm2.Direction = UserForm2.Direction & WithinInstrument(2) & vbNewLine
            UserForm2.Nominal = UserForm2.Nominal & WithinInstrument(3) & vbNewLine
            UserForm2.Rate = UserForm2.Rate & WithinInstrument(4) & vbNewLine
            UserForm2.Tenor = UserForm2.Tenor & WithinInstrument(5) & vbNewLine
            UserForm2.PV01 = UserForm2.PV01 & WithinInstrument(6) & vbNewLine
        Next
        UserForm2.Direction.TextAlign = 3
        UserForm2.Nominal.TextAlign = 3
        UserForm2.Rate.TextAlign = 3
        UserForm2.Tenor.TextAlign = 3
        UserForm2.PV01.TextAlign = 3
        MsgBox "Notification: Trade done on Marketplace", vbSystemModal + vbExclamation, "Marketplace Trade"
        UserForm2.Show vbModeless
    End If
  End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

 

[DeanP123]

Debug.Print, I mean.

 

[DeanP123]

Also notice I use HTMLDocument object. This is not in the standard excel toolset. So what I did for both laptops is tools->references-> tick the Microsoft HTML Object Library box. This ensures that when it encounters HTML Document it is recognised. If this box is unticked, it'll complain about not recognising HTML Document. That is purely FYI and I dont think it has anything to do with the problem at hand.

 

[DeanP123]

I sorry, I am using Outlook not excel. It still uses VBA and hoping you could assist?

 

[RoryA]

I'd suggest you add a debug statement to the Application_Startup code as well to check if that is setting things up properly. I'm assuming you have verified that the Trust Center macro settings on each machine are the same?

 

[DeanP123]

I'd suggest you add a debug statement to the Application_Startup code as well to check if that is setting things up properly. I'm assuming you have verified that the Trust Center macro settings on each machine are the same?

Brilliant, thank you so much. I went to File -> options -> Trust Centre -> Trust centre settings -> enable all macros.

Its solved. Thanks again