How to implement version independent VBA code?

Started by Thomas Winkel, February 01, 2015, 12:11:53 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Thomas Winkel

Hi,

at the moment our company is switching from Office 2010 to 2013.
But for Visio the users can decide either to keep 2010 or to switch to 2013.
All our code is written on a computer with Visio 2010 and Office 2010.
We have references to Excel and Outlook and more (see attachment).

Now we have the following situations:

Office 2010 / Visio 2010:
No problems.

Office 2013 / Visio 2013:
No known problems on user side.
But many users refuse Visio 2013 because they don't like the GUI.
A developer with this combination cannot make commits because it crashes on all other combinations.

Office 2013 / Visio 2010:
The code crashes during start up (building the ribbon) at the chr() function.
This can be solved by correcting the references manually on the users computer.
But then the user cannot upgrade to new versions which comes nearly every day via SVN.

I would like to have a version independent implementation that works with every combination of Visio / Office >=2010.
Is this possible?
I am a bit confused that the migration already collapse on a standard VBA function like chr()...

This post gives an overview of our development model:
http://visguy.com/vgforum/index.php?topic=6220.msg25371#msg25371

Best regards,
Thomas

daihashi

#1
I've encountered the same problem as you... to work around it I stopped using references to other office applications and instead just define them as regular objects instead. For a while though I did programmatically set references based on the version of office the end-user had installed. I would probably still be using this method if I did not lock my VBA projects down with a password; I stopped using it because I did not like the idea of using sendkeys to unlock the VBA project so that it could add references.

It worked like this; upon document open it would use a generic Object variable to open excel in the background (invisible). Then I could detect the version that was open by using the object_variable.Version statement.  Then I would call another routine that used the version number as ByVal. From there it would make decisions based on the version number. This is not my original code; I just wrote it right now after I read your post, but it's pretty darn close to the original, and should work for your purposes with some minor tweaking.


Private Sub Document_DocumentOpened(ByVal doc As IVDocument)

call excelVERSION

End Sub


Sub excelVERSION()
Dim xlApp As Object
Dim excelVERSION As Integer

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    excelVERSION = xlApp.Version

set xlApp = Nothing
call chgREF(excelVERSION)

End Sub


Sub chgREF(byval excelVERSION as integer)
    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim refCHK As VBIDE.Reference

    Set VBAEditor = Application.VBE
    Set vbProj = ActiveDocument.VBProject

    For Each refCHK In vbProj.References
        If refCHK.Name = "Excel" Then
           If excelVERSION = 15 then
            Select Case refCHK.Description
                       Case "Microsoft Excel 15.0 Object Library"
                                 GoTo completed
                       Case "Microsoft Excel 14.0 Object Library"
                                 vbProj.References.AddFromFile "C:\Program Files\Microsoft Office\Office15\EXCEL.EXE"
                                 GoTo completed
            End Select
            End If
        End If
    Next

completed:
    Set vbProj = Nothing
    Set VBAEditor = Nothing
End Sub

daihashi

Quote from: Thomas Winkel on February 01, 2015, 12:11:53 AM
Office 2013 / Visio 2013:
No known problems on user side.
But many users refuse Visio 2013 because they don't like the GUI.

Best regards,
Thomas

Btw; I also experience users who don't wish to upgrade. This makes creating documents that all behave in the same way an absolute nightmare; I would basically have to write 2-3 different versions of code. Finally I just told all the end-users tough luck, and that if they want to continue to be productive and use the tools and documents that I create then they need to upgrade.

Don't get me wrong... I go to great lengths to account for user experience, but the burden it placed on me was excessive. I finally tried just forcing users to "deal with it"; and while everyone complained about it... eventually people upgraded all of their Office suite to 2013.

  • I guess what I'm saying is that it sucks that the users complain, but they will upgrade once they realize they have no other choice. The alternative would be for them to use documents without automation; which most users won't go back to once they've experienced automation. Maybe you should also try putting your foot down before you write an entire second set of code for end-users who are "whining".

btw, I'm a Network Architect and not a programmer. So my take on the end-user may be more callous than how a "true" programmer might feel about what approach should be taken in this situation.

AndyW

I think it's not whether you are a programmer or network architect, more a case of if your end users are internal or external!

Would you say tough luck to a client?
Live life with an open mind

daihashi

True.. if someone were paying me, and it was something within my capabilities, then I would not say no. You hit the nail on the head, the people I create things for are indeed internal for the company I work for. :)

Thomas Winkel

Thanks for your feedback, Daihashi.

Your solution requires the users to check "Trust access to the VBA project object model" in the Trust Center Settings, right?
Meanwhile we decided to only support the combination: Visio 2010 / Office 2013.

Best regards,
Thomas

daihashi

#6
It does, but I also have the following code in my document_open routine; warning, this may make some people uncomfortable. For my environment it is acceptable.. you will need to determine if it is acceptable for your own environment as well.

This code still requires the end user to enable macros one time; this could also be written as a Wscript that runs seperately/before the user starts their office applications.


Sub regkeyexists()
Dim myWS As Object
Dim regkeyexists As Boolean
Dim getversion, value, rtype As String
Dim i_RegKey, i_RegKey2, iregkey3 As String
getversion = Application.Version
If getversion = "14.0" Then
i_RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Visio\Security\VBAWarnings"
i_RegKey2 = "HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security\UFIControls"
iregkey3 = "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Visio\Security\AccessVBOM"
ElseIf getversion = "15.0" Then
i_RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Visio\Security\VBAWarnings"
i_RegKey2 = "HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security\UFIControls"
iregkey3 = "HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Visio\Security\AccessVBOM"
End If
  On Error Resume Next
  Set myWS = CreateObject("WScript.Shell")
  myWS.RegRead i_RegKey
  Call enablemacros(i_RegKey, i_RegKey2, iregkey3)
End Sub

Function enablemacros(ByVal i_RegKey, i_RegKey2, iregkey3 As String)
Dim value, rtype As String
Dim myWS2 As Object
value = "1"
rtype = "REG_DWORD"
  Set myWS2 = CreateObject("WScript.Shell")
  myWS2.RegWrite iregkey3, value, rtype
  myWS2.RegWrite i_RegKey2, value, rtype
  myWS2.RegWrite i_RegKey, value, rtype
  Set myWS2 = Nothing
End Function