Excel VBA Tools / References - Do they work across multiple users / computers / excel version

[DylaBrion]

Hi

In an earlier thread it was suggested that I ensure that Reference Object Libraries for Outlook and Word are selected within Excel VBA

This helped resolve some issues with the code

I have a general question as follows

Given that my excel workbook will go to multiple users with different PCs and Excel versions, will the references and code still work?

Many thanks for any advice you can provide

 

[combo]

Generally, your code requires MS Office with Excel, Outlook and Word installed. The user needs Word as WordEditor set in Outlook, otherwise the code will fail. This can be checked by IsWordMail property.
Users that have lower than yours Office version can have problem running the code (reference downgrade problem).
In fact, you use referenced libraries (Word and Outlook) very selectively and they easily can be removed. Your code uses late binding, alien objects are declared as Object an assigned with CreateObject/GetObject, only named constants values come from the libraries. Go through the code and find used variables/constants that start with "wd" or "ol". Open Object Browser, find the constant in either Word or Outlook library and check its value in the description in the bottom section of the browser. Declare those values as constants (you have already done this for some constants, as [tt]Const olAppointmentItem = 1[/tt], this overwrites values from external referenced library with values in code).

combo

 

[macropod]

The simplest solution, assuming you're using early binding, is to set the references on a system using the earliest version of Office you plan to support. Testing on the range of Office versions you plan to support is recommended anyway, in case you've tried to use functions that aren't supported in earlier versions or you've tried to use functions that have been deprecated in later versions.

Cheers
Paul Edstein
[MS MVP - Word]

 

[DylaBrion]

Many thanks macropod

I will not always know what office versions are being used across different users so assume that the approach suggested by Combo is the one to take?

Thanks

 

[macropod]

It's not a matter of know which versions - but with knowing the earliest & latest versions. You can't use late binding get around the consequences of trying to use functions that aren't supported in earlier versions or trying to use functions that have been deprecated. So, if you have access to the earliest & latest platforms you'll be supporting, test on both and compile on the earliest.

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

>compile on the earliest.

This is the VBA forum, we don't get to compile to an exe ...

 

[DylaBrion]

Thanks to all

Unfortunately I do not have access to know which versions of office will be used

Thanks

 

[SkipVought]

https://support.office.com/en-us/ar...of-Excel-2fd9ffcb-6fce-485b-85af-fecfd651a5ac

I doubt that anyone is using a version earlier than Excel 97. Know its limitations.

The next watershed version is Excel 2007. Know its limitations.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[DylaBrion]

Okay thanks

So if that is the case but I don't have access to excel 97 I guess it's not possible for me to compile?

Thanks again

 

[DylaBrion]

Hi

I thought I would try the following. I have unticked the references to MS Object 15.0 Libraries for Office, Word and Outlook and have then run my code. I only seem to have an issue with one module which is below and highlighted where I hit the issue. I know you have told me what to do but I am still confused (as still learning) so if could advise further I'd appreciate it

Thanks

Sub CreateCalendarSchedule_Click()
'Declare Variables
Dim ol As Object
Dim oItem As Object
Dim StartDate As Date
Dim StartTime As String
Dim EndDate As Date
Dim EndTime As String
Dim TimingofWork As String
Dim BuildingofWork As String
Dim Title As String
Dim DetailedDescriptionofWorks As String
Dim ImplementationPlan As String
Dim Whatmonitoring As String
Dim Backoutplan As String
Dim TestPlan As String
Dim PostImplementationVerification As String
Dim ImpacttoSytemOutputsandUsers As String
Dim Otherifapplicable As String
Dim CRNumber As String
Dim makeReminder As String

'Check Outlook is Open and if not then open
On Error Resume Next
Set ol = GetObject(, "Outlook.Application")
On Error GoTo 0
If ol Is Nothing Then
    Set ol = CreateObject("Outlook.Application")
End If

'Capture Data From Excel
StartDate = [C11]
StartTime = [E11]
EndDate = [G11]
EndTime = [I11]
TimingofWork = [K11]
BuildingofWork = [C13]
Title = [I13]
DetailedDescriptionofWorks = [C15]
ImplementationPlan = [F17]
Whatmonitoring = [F19]
Backoutplan = [F21]
TestPlan = [F23]
PostImplementationVerification = [F25]
ImpacttoSytemOutputsandUsers = [C27]
Otherifapplicable = [C29]
CRNumber = [J31]

'Create Appointment Item
[highlight ]Set oItem = ol.CreateItem(olAppointmentItem)[/highlight]
'Set Start Date
oItem.Start = StartDate + TimeValue("00:00")
'Set End Date
oItem.End = EndDate + TimeValue("00:30")
'appointment subject
oItem.Subject = Title & " - " & BuildingofWork
'location description
oItem.Location = BuildingofWork

'Display
oItem.Display

'Create Appointment Details
oItem.Recipients.Add ("CR Notification Group")
'set the busy status
oItem.BusyStatus = olFree
'reminder before start
oItem.ReminderMinutesBeforeStart = 15
'reminder activated
oItem.ReminderSet = True

'Paste Details to Appointment body message
Worksheets("Drop Down and Pastes").Range("C2:D22").Copy
Dim oInspector As Object
Dim oWordEditor As Object
Dim oWindow As Object
Set oInspector = oItem.GetInspector
'oInspector.Activate
Set oWordEditor = oInspector.WordEditor
oWordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF

'Reset Variables
Set ol = Nothing
Set oItem = Nothing

End Sub

 

[DylaBrion]

I cannot seem to highlight the code where it stops - sorry

 

[DylaBrion]

To All

I've managed to work this out (I think) and declared constants in my code and it now works

Thanks so much for your guidance and patience.

I have pasted my code for reference and to ask if there is any other fine tuning / improvements I should make

Thanks again as you have all been a great help

Sub CreateCalendarSchedule_Click()
'Declare Variables
Dim ol As Object
Dim oItem As Object
Dim StartDate As Date
Dim StartTime As String
Dim EndDate As Date
Dim EndTime As String
Dim TimingofWork As String
Dim BuildingofWork As String
Dim Title As String
Dim DetailedDescriptionofWorks As String
Dim ImplementationPlan As String
Dim Whatmonitoring As String
Dim Backoutplan As String
Dim TestPlan As String
Dim PostImplementationVerification As String
Dim ImpacttoSytemOutputsandUsers As String
Dim Otherifapplicable As String
Dim CRNumber As String
Dim makeReminder As String
Const olAppointmentItem = 1
Const wdPASTERTF = 1
Const olfree = 0

'Check Outlook is Open and if not then open
On Error Resume Next
Set ol = GetObject(, "Outlook.Application")
On Error GoTo 0
If ol Is Nothing Then
    Set ol = CreateObject("Outlook.Application")
End If

'Capture Data From Excel
StartDate = [C11]
StartTime = [E11]
EndDate = [G11]
EndTime = [I11]
TimingofWork = [K11]
BuildingofWork = [C13]
Title = [I13]
DetailedDescriptionofWorks = [C15]
ImplementationPlan = [F17]
Whatmonitoring = [F19]
Backoutplan = [F21]
TestPlan = [F23]
PostImplementationVerification = [F25]
ImpacttoSytemOutputsandUsers = [C27]
Otherifapplicable = [C29]
CRNumber = [J31]

'Create Appointment Item
Set oItem = ol.CreateItem(olAppointmentItem)
'Set Start Date
oItem.Start = StartDate + TimeValue("00:00")
'Set End Date
oItem.End = EndDate + TimeValue("00:30")
'appointment subject
oItem.Subject = Title & " - " & BuildingofWork
'location description
oItem.Location = BuildingofWork

'Display
oItem.Display

'Create Appointment Details
oItem.Recipients.Add ("CR Notification Group")
'set the busy status
oItem.BusyStatus = olfree
'reminder before start
oItem.ReminderMinutesBeforeStart = 15
'reminder activated
oItem.ReminderSet = True

'Paste Details to Appointment body message
Worksheets("Drop Down and Pastes").Range("C2:D22").Copy
Dim oInspector As Object
Dim oWordEditor As Object
Dim oWindow As Object
Set oInspector = oItem.GetInspector
'oInspector.Activate
Set oWordEditor = oInspector.WordEditor
oWordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF

'Reset Variables
Set ol = Nothing
Set oItem = Nothing

End Sub

 

[macropod]

>compile on the earliest.

This is the VBA forum, we don't get to compile to an exe ...

Who said anything about compiling to exe? All VBA code is compiled to run.

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

I am well aware of VBA compilation. My point was really that with VBA you can't compile to a known fixed state (nence my comment about an exe) Load a VBA project into a different version of Office and it will recompile itself, so with VBA 'compil(ing) on the earliest version' achieves little, particularly if we are using late binding

 

[macropod]

By setting the early binding references to whatever other Office components one is employing on the earliest supported Office version, those references will auto-update when run on a system using a later Office version; it doesn't go the other way, though.

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

Sure, but that's got little to do with compilation.

(but just to be clear here, I agree that development should be done on earliest version that one intends to support)

 

[macropod]

Look at it this way: If the references haven't been set correctly, it isn't going to compile. Hence compiling the code on the earliest supported Office version (after taking account of functions that aren't supported in earlier versions or trying to use functions that have been deprecated) is the surest way of being able to run it on all versions.

Cheers
Paul Edstein
[MS MVP - Word]

 

[DylaBrion]

Hi All

I’m watching your conversation, eager to learn and code correctly. If I do not have access to earlier versions then I assume I cannot compile on an earlier version?

Thanks

 

[SkipVought]

Coding for earlier versions than you have means that, for instance:

1) if the earlier version for which you are coding only has 32,767 rows, then YOU must limit the workbook row limit to 32,767 even if your version has a greater row limit or

2) if the earlier version for which you are coding does not include the ListObject object, then YOU must not include features that use the ListObject object in your application

...etc.

Know the limitations and features of the version of "the lowest common denominator", so to speak.

Design and code accordingly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

If you want your code work in earlier versions than you have, then you need to:
1) avoid referencing to other office applications other than host application your code is,
2) make sure that the lower version provides functionality of the document/library (Office, Excel, VBA in your case) and late binding objects (Outlook) you use.
This can be done by exploring the link provided by Skip, you need also to check Outlook. You can define the lowest version the code can work and compare with user's excel version returned by [tt]Application.Version[/tt] (note that this property returns string).
To be fully prudent, you should also test if you get Outlook and Word applications and what are their versions - user can have standalone excel or excel version is higher than other components. For some lower Outlook versions Word is not the default WordEditor, this should also be cheched.

combo