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
