Hi fellow RExcel users,
In a similar vain to the thread for detecting if RExcel is installed I thought I would share some simple VBA code.
We have an RExcel application which will be in use by several different versions of Excel (2000,2003,2007) and thus RExcel.
Rather than write different versions of the Excel application I am trying to write one version which detects what version of RExcel is installed
and handles this in the appropriate manner. The RExcel VBA project reference differs in Excel 2007 to other versions.
Hence by running the VBA procedure "AddRExcelRefbyVersion" below the code looks to see what version of Rexcel is installed and adds the appropriate reference.
Warning: The code assumes that add-ins are installed in:
RExcel2007.xlam is located "C:\Program Files\RExcel\xls\RExcel2007.xlam"
RExcel.xla is located "C:\Program Files\R\(D)COM Server\xls\RExcel.xla"
Regards,
Wayne
'--------------------------------------------------------------------------------------------------'
Sub AddRExcelRefbyVersion()
Call RemoveBrokenRefs
On Error GoTo ErrHandler:
If AddinAvailable("RExcel2007.xlam") Then
ThisWorkbook.VBProject.References.AddFromFile "C:\Program
Files\RExcel\xls\RExcel2007.xlam"
End If
If AddinAvailable("RExcel.xla") Then
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\R\(D)COM
Server\xls\RExcel.xla"
End If
Exit Sub
ErrHandler:
Select Case Err.Number
Case Is = 32813
'MsgBox "Ref already exists" hence do nothing!
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
End Sub
'--------------------------------------------------------------------------------------------------'
Sub RemoveBrokenRefs()
Dim theRef As Variant
Dim i As Integer
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
End Sub
'--------------------------------------------------------------------------------------------------'
Function AddinAvailable(aiName As String) As Boolean
Dim result As Boolean
Dim ai As Object
result = False
For Each ai In Application.AddIns
If ai.Name = aiName Then
result = True
End If
If result Then Exit For
Next ai
AddinAvailable = result
End Function
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Erich
Neuwirth
Sent: 20 July 2008 10:21
To: R (D)COM and RExcel server related issues
Subject: Re: [Rcom-l] Detecting if RExcel is installed
On Jul 17, 2008, at 11:58 PM, Andrew McLachlan wrote:
Thank you Erich
I have just been reading about the AddIns collection and was wondering
whether to use that approach or not. Your reply has stopped me
wondering
:-) and I will be adding your code to my project.
During my testing of the functions below, I found that they worked as
expected. That is, when RExcel2007 in the Add-ins list was checked,
the
result was True, and when RExcel2007 in the Add-ins list was
unchecked,
the result was False.
However, I did a further test and got an unexpected result. I
unchecked
RExcel2007 to uninstall it and then installed and ran my own add-in
that
uses RExcel. I found that my own add-in worked and used R correctly.
This was unexpected. I had a assumed that if the RExcel add-in was
unchecked, then the RExcel functions would be unavailable. Is this
because I have added a reference to RExcelVBALib in the Tools >
Reference list?
That is probably the reason, but I have never tested this, therefore I
do not know in detail.
My guess it the followning.
The checkmark does not tell yo if the addin is installed, but if it is
active.
The fact that the addin is on the list means that it is installed.
My guess is that the installed property better should be called
activated.
I am wondering about my user installing my add-in without RExcel being
present and, thereby, generating uncontrolled errors. I am wanting to
detect when the RExcel macros are available and when they are not so
that I can give a warning to the user when they try to do things that
require RExcel. Should I be doing this, do you think? Or, should I
just
be making sure that I have RExcel properly installed before installing
my add-in (my defensive programming nerves start to twitch at the
thought!)?
You will have to check for yourself.
Any thoughts on this would be appreciated.
Note: As a small modification, I added the line "Dim ai As AddIn" to
the code below so that does not give an error when "Option Explicit"
is
used.
'Function code by Erich Neuwirth <[EMAIL PROTECTED]>
Function AddinInstalledAndEnabled(aiName As String) As Boolean
Dim result As Boolean
Dim ai As AddIn
result = False
For Each ai In Application.AddIns
If ai.Name = aiName Then
result = result Or ai.Installed
End If
If result Then Exit For
Next ai
AddinInstalledAndEnabled = result
End Function
'Function code by Erich Neuwirth <[EMAIL PROTECTED]>
Function RExcelAvailable() As Boolean
RExcelAvailable = AddinInstalledAndEnabled("RExcel.xla") or _
AddinInstalledAndEnabled("RExcel2007.xlam")
End Function
Cheers, Andrew Mc
--
Andrew McLachlan, Biometrician
Crop & Food Research, Palmerston North, New Zealand.
Tel +64-6-356-8300, +64-6-355-6110 (DDI)
On 17/07/2008 at 8:06 p.m., Erich Neuwirth
<[EMAIL PROTECTED]>
wrote:
Function AddinInstalledAndEnabled(aiName As String) As Boolean
Dim result As Boolean
result = False
For Each ai In Application.AddIns
If ai.Name = aiName Then
result = result Or ai.Installed
End If
If result Then Exit For
Next ai
AddinInstalledAndEnabled = result
End Function
Function RExcelAvailable() As Boolean
RExcelAvailable = AddinInstalledAndEnabled("RExcel.xla") or _
AddinInstalledAndEnabled("RExcel2007.xlam")
End Function
Andrew McLachlan wrote:
Hi All
Can anyone suggest to me ways of using VBA from within Excel to
detect
whether the RExcel add-in is installed or not?
My first thought is perhaps to check for the presence of some
RExcel
constants, for example, RExcelVersion.
Cheers, Andrew Mc
Visit our website at http://www.crop.cri.nz
______________________________________________________
CAUTION: The information contained in this email is privileged
and confidential. If you read this message and you are not the
intended recipient, you are hereby notified that any use,
dissemination, distribution or reproduction of all or part of the
contents is prohibited. If you receive this message in error,
please notify the sender immediately.
Any opinions or views expressed in this message are those of the
individual sender and may not represent those of their employer.
_______________________________________________
Rcom-l mailing list
Rcom-l@mailman.csd.univie.ac.at
http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l
More information (including a Wiki) at http://rcom.univie.ac.at
Visit our website at http://www.crop.cri.nz
______________________________________________________
CAUTION: The information contained in this email is privileged
and confidential. If you read this message and you are not the
intended recipient, you are hereby notified that any use,
dissemination, distribution or reproduction of all or part of the
contents is prohibited. If you receive this message in error,
please notify the sender immediately.
Any opinions or views expressed in this message are those of the
individual sender and may not represent those of their employer.
_______________________________________________
Rcom-l mailing list
Rcom-l@mailman.csd.univie.ac.at
http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l
More information (including a Wiki) at http://rcom.univie.ac.at
_______________________________________________
Rcom-l mailing list
Rcom-l@mailman.csd.univie.ac.at
http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l
More information (including a Wiki) at http://rcom.univie.ac.at
_______________________________________________
Rcom-l mailing list
Rcom-l@mailman.csd.univie.ac.at
http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l
More information (including a Wiki) at http://rcom.univie.ac.at
