×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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 Const olAppointmentItem = 1, this overwrites values from external referenced library with values in code).

combo

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

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]

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

(OP)
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

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

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]

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

>compile on the earliest.

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

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

(OP)
Thanks to all

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

Thanks

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

https://support.office.com/en-us/article/Use-Excel...

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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

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

(OP)
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

CODE -->

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 

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

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

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

(OP)
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

CODE -->

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 

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

Quote:

>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]

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

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

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

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]

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

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)

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

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]

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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 Application.Version (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

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

(OP)
Thanks Combo and skip. I will check this

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close