Keskustelu: Ohjelmointikysymykset: VBA (Excel) ja referencet automaattisesti?

Minulla on Excel -pohjainen ohjelma, joka vaatii tietyt VBA:ssa määritetyt referencit. Kooditekstit on suojattu salasanalla, eikä ole tarkoitus, että käyttäjät, joilta ei vaadittuja referenssejä löydy, manusti alkaisisivat niitä lisäämään.

Onko mahdollisuuksia tarkistaa esim. Workbook_open -vaiheessa, ovatko vaaditut referenssit valittu vai ei, ja tarvittaessa ohjelmoida valitsemaan tarvittavat?

Tuon referenssien tarkistuksen voit automatisoida lisäämällä projektiisi Microsoft Visual Basic for Applications Extensibility 5.3 library:n ja seuraavan koodin

Sub CheckReference()
    Dim vbProj As VBProject
    Dim Refe As Reference
    Dim Broken As String

    Set vbProj = ActiveWorkbook.VBProject

    For Each Refe In vbProj.References
        If Refe.IsBroken Then _
          Broken = IIf(Broken = "", Refe.Name, Broken & vbCrLf & Refe.Name)
        'viittauksen referenssiin voi poistaa vbProj.References.Remove Refe -komennolla
    Next
    If Broken <> "" Then _
      MsgBox "These are missing or broken:" & vbCrLf & Broken
End Sub

Referenssin lisäyksen hoidat seuraavalla

Sub AddReference()
    Dim vbProj as VBProject

    Set vbProj = ActiveWorkbook.VBProject
    vbProj.References.AddFromFile "C:\TestFiles\Refme.xls"
End Sub

Kiitos!

Täytyypä kokeilla.

Yksi tarkennus. Onko tuo polussa mainittu tiedosto se lähde, josta viittaukset poimitaan nykyiseen työkirjaan? Voiko viittaus perustua mallitiedostoon eli .xlt tyyppiin?

Kokeilin tuota, mutta suoraan .xls tai .xlt tiedostosta en onnistunut referenssejä kopoimaan. Sen sijaan suoraan .dll tiedostosta (kunhan sai kaivettua nimen ja polun esille) tuo asennus onnistui.

Tämä hieman hankaloittaa tilannetta, koska käyttäjillä saattaa olla eri versioita officesta/excelistä ja toisaalta koska .dll:t saattavat sijaita eri paikoissa. Katsoin noita ADD* vaihtoehtoja, mutta niistä ei oikein apua ollut. Lienee niin, ettei referenssejä voi lisätä pelkän nimen perusteella?

Jos et saanut luotua referenssiä johonkin toiseen xls-tiedostoon, niin sen makroja pystyy käyttämään Application.Run -metodilla.

Application.Run "Taulukko2.xls!Macro1"
'Jos tiedostonnimessä on jotain ihme merkkejä (välilyönti tai viiva ei ole vielä ihme merkki),
'niin laittaa tiedoston nimi hipsuihin
Application.Run "'Tiedot vuosilta 00-02&04.xls'!Laske"

Referenssit pystyy linkkaamaan myös GUID:n kautta, jolloin tiedosto itsessään saa olla missä tahansa.

'Microsoft ActiveX Data Objects 2.7 Library
vbProj.References.AddFromGuid("{EF53050B-882E-4776-B643-EDA472E8E3F2}", 2, 7)
'Lopussa olevat numerot kertovat mikä versio kyseisestä tiedostosta, jos samalla GUID:lla on useita versioita samasta referenssistä

VB:n helppi kirjoitti:

The AddFromGuid method searches the registry to find the reference you want to add. The GUID can be a type library, control, class identifier, and so on.

Jossain oli uutinen, että myös Word- ja Excel- tiedostoilla pitäisi olla omat GUID:nsa. Uutinen oli joku "isoveli valvoo" -uutinen, sillä GUID:lla pystyi selvittämään millä tietokoneella ja Windows-versiolla kyseinen tiedosto on kirjoitettu.

VBA osaa kertoa myös mistä löytyy tiedostot, joita Officen ohjelmat käyttävät.

Sub Esimerkki()
    Dim r As Reference

    Debug.Print Application.Path
    'C:\Program Files\Microsoft Office\OFFICE11
    Debug.Print Application.LibraryPath
    'C:\Program Files\Microsoft Office\OFFICE11\LIBRARY
    Debug.Print Application.DefaultFilePath
    'C:\Documents and Settings\[User]\My Documents
    Debug.Print Application.StartupPath
    'C:\Documents and Settings\[User]\Application Data\Microsoft\Excel\XLSTART
    Debug.Print Application.TemplatesPath
    'C:\Documents and Settings\[User]\Application Data\Microsoft\Templates\
    Debug.Print Application.UserLibraryPath
    'C:\Documents and Settings\[User]\Application Data\Microsoft\AddIns\
    Debug.Print Application.Version 'mihin versioon Officesta ohjelma kuuluu
    '11.0
        '8.0=Office 97
        '9.0=Office 2000
        '10.0=Office XP
        '11.0=Office 2003
    Debug.Print Application.OperatingSystem
    'Windows (32-bit) NT 5.01
    Debug.Print
    'tulostetaan ohjelmassa aktiivisina olevien referenssien nimi, GUID ja tiedoston polku
    For Each r In ActiveWorkbook.VBProject.References
        Debug.Print r.Name & ": " & r.GUID
        Debug.Print "Path: " & r.FullPath
    Next r
End Sub

BadSource kirjoitti:

'Microsoft ActiveX Data Objects 2.7 Library
vbProj.References.AddFromGuid("{EF53050B-882E-4776-B643-EDA472E8E3F2}", 2, 7)
'Lopussa olevat numerot kertovat mikä versio kyseisestä tiedostosta, jos samalla GUID:lla on useita versioita samasta referenssistä

Kiitti paljon!
Sellainen kysymys vielä, että onko tuo GUID -arvo aina sama (joskin version numero voisi vaihdella) työasemasta riippumatta? Ja otetaanko aina uusin versio käyttöön, jos tuota versiota ei erikseen määritetä?

GUID (globally unique class identifier) on ohjelmille, joilla sellainen on, aina sama. Nuo GUID:t löytyy Windowsin rekistereistä HKEY_CLASSES_ROOT/TypeLib:n alta. Tosin ei kovinkaan käyttäjäystävällisessä muodossa. Siksi suosittelenkin, että valitset haluamasi/tarvitsemasi referenssit aktiivisiksi työkirjaasi ja ajat sen edellisen viestin lopussa olevan koodin, joka listaa aktiivisten referenssien nimen, GUID:n ja polun.

GUID:lla haettaessa nuo Major/Minor arvot on pakko antaa, eli ei voi hakea pelkän GUID:n avulla, mutta ne voi olla vaikkapa (0, 0), jolloin valitaan ohjelman ensimmäinen löytyvä versio, jos vain GUID on annettu oikein.

Versionumeroksi kannattaa asettaa ensimmäisenen versio, jolla ohjelmasi toimii. Jos asettamaasi versionumeroa (->ohjelmaa) ei löydy, niin Windows yrittää tarjota uudempaa versiota (isompaa numeroa minor-major -järjestyksessä). Jos uudempi versio löytyy, niin sitten kokeillaan sillä. Oletuksena kuitenkin käytetään sitä asettamaasi versiota, ei uusinta mahdollista.

Täällä MrExcel (toisena oleva Ivan F Moala:n vastaus) vastaa samaan kysymykseen hieman pidemmin ja laajemmin.

Yritin tuota lisäystä, mutta addfromguid lause vaati jostain syystä loppuun yhtäsuuruusmerkit, jonka jälkeen luonnollisesti lisäsin loppuun vielä True. Kun tämän ajoin, Excel ilmoitti, ettei ko versiota löydy. Yritin lähteä alimmalta tasolta eli major oli 1, minor 0.

Kiitos koodivinkkiesi osaan kyllä nyt löytää nimellä ja kuvauksella ja guidilla nuo referenssit. Tämä puoli on ok. Sen sijaan ongelma onkin siinä, kuten 1. postissa yritin selittää, että kun taulukko-ohjelmaa ajetaan jossain muussa työasemassa, jossa on vanhemmat versiot excelistä ja referenssiversiot ovat eri, voitaisiin tehdä tarkistus ja muuttaa ohjelmaan aikanaan valitut uudemmat referenssiversiotiedot vanhempiin ja ko. työasemasta löytyviin versioihin. Ongelma on nimittäin ilmennyt siten, että kun joku vanhalla työasemalla ajaa ohjelmaa, sen suoritus keskeytyy virheeseen, koska alkuperäisessä tiedossa valittuna olevaa uudempaa referenssitietoa ei löydy koneelta. Ohjelma pyörisi, jos vanhempi referenssitieto haettaisiin tilalle (ja kun VBA koodi on salasanalla suljettu, ko. käyttäjä ei pääse muuttamaan referenssitietoja).

1.) Ota pois ne sulut. Itsekkin copy/pastasin tuon omasta koodistani, niin nohevana jätin turhat muuttujat pois. Saamasi virheilmoitus tarkoitti sitä, että komenennolle pitäisi antaa muuttuja, jonne paluuarvo palautetaan. Eli se kaipasi jotain tälläistä...

ResponseValue = vbProj.References.AddFromGuid("{EF53050B-882E-4776-B643-EDA472E8E3F2}", 2, 7)

'Muuta tuo edellinen muotoon...
vbProj.References.AddFromGuid "{EF53050B-882E-4776-B643-EDA472E8E3F2}", 2, 7
'...niin ei tarvita muuttujaa, jonne paluuarvo palautetaan

Tuo sulku-juttu on jokin VB/VBA:n "ominaisuus", että sulkujen kanssa tarvitaan muuttujia ja Call-lauseita, kun ne menee ilman sulkuja sellaisinaan.

2.) Tee koko referenssien vienti täysin automaattiseksi koodisi kautta. Eli heti workbookin avauksen yhteydessä ensin kaikki pois, jotka lähtee, ja sitten tarvitut tilalle. Viet ne tuon AddFromGuid:n kautta mahdollisimman pienillä versionumeroilla, jolloin saat määriteltyä tarvitsemasi referenssit sellaisessa muodossa että ohjelmasi toimii ja ne mahdollisesti löytyy käyttäjien koneilta. Ei heti oletuksena sitä uusinta ja hienointa. Jos ei löydy niitä minimin täyttäviä, niin mahdollisesti uudemmat. Vanhempaan päin on aina hankalampi mennä.

Jos tuon tekee workbookin avauksen yhteydessä heti ensimmäisenä, niin jos tarvittavia referenssejä ei löydy, niin siitä voidaan antaa ilmoitus käyttäjälle ja workbook sulkea hallitusti. Samalla voi antaa tarkan ilmoituksen mikä puuttuu.