Ünite 7: VBA ile API ve Veri Tabanı İşlemleri

Giriş

Excel VBA’da hazır komutların yetersiz olduğu durumlar için tüm yazılım dilleri ile çalışabilecek köprü vazifesi görecek yazılım dilleri kullanılır. Bu ihtiyacı karşılamak için Microsoft Windows yapısında sık kullanılan yapılardan bir tanesi dinamik link kütüphanesidir. VBA ile Uygulama Programlama Ara yüzü işlemlerinde genellikle Windows işletim sistemi yapısında varsayılan olarak bulunan kütüphaneler kullanılır. Bu işlemler sırasında Dinamik link kütüphaneleri ile bağlantı kurulur ve bu kütüphanelerde tanımlanmış fonksiyonlar kullanılır. Veri tabanları büyük miktardaki bilgileri depolamak için geliştirilen birbirleriyle ilişkili bilgilerin kayıt edilmesi ve çağrılabilmesi için hazırlanmış saklama alanlarıdır. Bilgisayar temelli kayıtların tutulması için hazırlanan veri tabanları, veri tabanı yönetim sistemleri aracılığı ile oluşturulur ve yönetilir.

Uygulama Programlama Arayüzü (API)

Günümüzde insanlar ihtiyaçları doğrultusunda geliştirdikleri farklı niteliklerdeki yazılımları bilgisayarlarında kullanmaktadır. Tüm yazılımlar; girdi, süreç ve çıktıdan oluşan fonksiyonlarla çalışmasına rağmen fonksiyonların tanımlandığı yazılım dilleri farklılık göstermektedir. Yazılım dillerinin tümünün bilgisayar yazılımı yapanlar tarafından öğrenilmesi ve bu yazılım dillerinde uzmanlaşmak para, zaman ve diğer maliyetler açısından mümkün değildir.

Yazılım fonksiyonlarını farklı dillerde tekrar yazma maliyeti, bankacılık gibi uluslararası ve geniş bir yelpazede çalışan işletmeler için oldukça yüksekti. Birbirlerinden farklı programlama dillerinde hazırlanmış yazılımların fonksiyonlarını birbirlerine adapte edecek (modüler) şekilde kullanma ihtiyacı bilgisayarların ve yazılımların artmasıyla daha da arttı. Bunun yanında birden fazla bilgisayarın eş güdümlü çalışabilmesi, yazılım tasarım süresinin kısaltılabilmesi, yazılım hatalarının daha kolay bulunabilmesi, veri akışlarının sadeleştirilerek yönetilebilmesi ve veri güvenliğinin sağlanabilmesi gibi ihtiyaçların da giderilmesi gündeme geldi. Bu nedenle yazılım dillerinin tümü ile çalışabilecek, köprü vazifesi gören üst diller geliştirilmeye başlandı.

Kütüphaneler bir yazılımda kullanılmak üzere bir araya getirilmiş nesne, fonksiyon ve metotların genel adıdır. Kütüphanelerin temel kullanım amacı bir yazılım dili içerisinde tekrar kullanılabilir fonksiyonları toplayarak yazılımcının hizmetine sunmaktır. Kütüphaneler başka programlar tarafından çağırılarak çalıştırılan yapılardır.

Excel VBA yazılımlarındaki komutlar varsayılan olarak yüklenen kütüphanede yer alır. İstenmesi durumunda Excel VBA’ya diğer kütüphaneler de bağlanabilir. Genellikle birden fazla kişinin beraberce üzerinde çalıştığı yazılım projelerinde geliştirilen fonksiyonlar ayrı bir kütüphanede tutulmaktadır. Böylece tekrarlanan fonksiyonların önüne geçilir. Gerektiğinde kütüphane farklı bir projeye bağlanarak daha önce hazırlanan fonksiyonlar üzerinde değişiklik yapılmadan kullanılır. Bir yazılıma birden fazla kütüphane eklenebilir.

Uygulama Programlama Ara yüzü (Application Programming Interface-API) bilgisayar programlamasında yazılım uygulamalarının birbirleri ile bütünleştirmek için oluşturulmuş rutinleri, protokolleri ve araçları içerir. API’ler yazılımları birbirlerine bağlayarak beraber çalışmalarına imkân sağlamaktadır. Örneğin, pos cihazı kullanan işletmede yazar kasada belirtilen tutar pos cihazı yazılımına gönderilebilmektedir. Müşteri banka kartını cihazdan geçirip şifresini girdiğinde pos cihazı bilgileri sim kart ile İnternet’e bağlanarak bankaya yollamaktadır. Bankadaki yazılım; tutar bilgisini, kart bilgisi ve şifreyi kontrol etmektedir. Bilgilere göre müşteri hesabından tutar miktarı düşülerek bilgi pos cihazına geri gönderilmektedir. Pos cihazına bankadan onay geldikten sonra bu bilgiyi yazar kasa cihazına ileterek fişin yazdırılmasını sağlamaktadır. Bu işlemler, yazılımlar arasında köprü vazifesi gören API’ler ve iletişimi sağlayan katmanlar sayesinde (communication foundation) bir dakikadan kısa bir süre içerisinde gerçekleştirilebilmektedir.

Yazılımları bütünleştirmenin yanında bilgisayar donanımları da API sayesinde kolay bir şekilde çalıştırılır. API fonksiyonları aynı yapı içerisindeki bütün programlama dillerinde ve geliştirme ortamlarında çağrılıp kullanılabilmektedir. API ile yazılımlar birbirlerine adapte edildiği için yazılımcılar fonksiyonları hazırlamak için kullanacağı dili seçmekte özgürdür. Böylece farklı yazılım dillerinde geliştirilen program blokları bir bütün gibi çalıştırılabilir.

VBA ile API İşlemleri

Kullanıcılar Excel VBA’daki hazır komutlardan veya oluşturdukları fonksiyon ve yordamlar ile ihtiyaçlarını giderecek yazılımları yaparlar. Excel VBA fonksiyonlarının yetersiz kaldığı durumlarda ise Windows işletim sistemi yapısındaki kütüphanelerde bulunan fonksiyonların kullanılması daha verimli olacaktır.

Windows işletim sisteminde VBA API ile kullanılan API kütüphanelerinden Advapi32, Windows GDI+, Comdl32, Kernel32, Shell32, User32, Netapi32 ve Winspool sıkça kullanılan kütüphanelerdir. Bu API kütüphanelerinin temel kullanım alanları ve içerdiği fonksiyonlardan bazıları S:136, Tablo 7.1’de verilmiştir.

VBA ile API işlemlerinde fonksiyon kütüphanelerinin kullanılabilmesi için kütüphanelerin deklare edilmesi gerekmektedir. Deklarasyon deyimi, ihtiyaç duyulan fonksiyonun bulunduğu kütüphanenin ve fonksiyonun yol ve içeriğinin tanımlandığı bir yordamdır.

Aşağıda örnek olarak bir deklarasyon deyimi verilmiştir:

Public Declare PtrSafe Function GetUserName Lib “advapi32.dll” Alias _ “GetUserNameA” (ByVal lpBuffer As String, nSize As Long) As Long

• Public: Deklarasyon deyiminin geçerli olduğu yaşam alanını ifade etmektedir. Public ifadesi projenin tamamında erişime izin verildiğini gösterir.
• Declare: Deyimin Deklarasyon deyimi olduğunu göstermektedir
• PtrSafe: API köprüleme işleminin 64-bitlik işletim sistemi yapısında çalıştırılacağını göstermektedir. 32-bitlik işletim sisteminde bu deyim yer almaz. Hazırlanan yazılımın 64 veya 32 bitlik işletim sistemi yapısında çalıştırılıp çalıştırılmayacağı bilinmiyorsa basit bir koşul cümlesi kurulabilir. Böylece API hem 32 hem de 64 bitlik işletim sistemi yapılarında çalıştırılabilir. Bunun için #If VBA7 Then ile koşul cümlesi kurulur. Bu durumda VBA7 yani 64 bitlik bir yapı ile çalışıyorsa, deklarasyonun PtrSafe ifadeli olan deklarasyon deyimi çalıştırılabilir. Değilse 32 bitlik yapı ile kurulan deklarasyon deyimi çalıştırılır.
• Function: API ile bağlantı kurulan kütüphane içerisindeki hangi fonksiyonun kullanılacağını gösterir.
• GetUserName: API ile bağlantı kurulan kütüphane içerisindeki fonksiyonu gösterir. Örnekte bilgisayarın kullanıcı adını getirmeye yarayan fonksiyon yani GetUser- Name fonksiyonu için deklarasyon deyimi hazırlanmıştır.
• Lib: Kütüphane bağlantısı yapılacağını gösterir.
• “advapi32.dll”: API ile bağlantı kurulacak dinamik kütüphanenin ismi verilir. advapi32 kütüphanesi kayıt ve NT güvenlik işlemleri için kullanılan kütüphanelerden biridir.
• Alias: advapi32 kütüphanesi içerisinden çağrılan GetUserName fonksiyonunun kullanıcı tarafından geliştirilen VBA yazılımındaki takma adını ifade eder. Kullanıcı yazılımda Türkçeleştirilmiş şekilde fonksiyon isimleri kullanabilir. Kütüphaneye bağlantı yapıldıktan sonra GetUserName fonksiyonu VBA yazılımının içerisine taşınır ve Alias ile belirtilen takma isimle kullanılır. Karışıklık olmaması ve API fonksiyonu olduğunun gösterilmesi için genellikle kütüphanedeki fonksiyon isminin sonuna A harfi eklenerek kullanılır. Örnekte takma adı “GetUserNameA” şeklinde ifade edilmiştir.
• (ByVal lpBuffer As String, nSize As Long): API fonksiyonundaki girdileri göstermektedir. Fonksiyona String türünden lpBuffer ve Long veri türünden iki değişken gönderilebilir.
• As Long: API fonksiyonu Long türünden geri dönüş sağlar.

32 bitlik Windows işletim sistemi için hazırlanmış programların 64 bitlik Windows işletim sisteminde çalışmamasının temel sebebi API kütüphaneleri arasındaki iletişimi sağlayan deklarasyon deyiminin uyumsuzluğundan kaynaklanmaktadır. API deklarasyon deyimi sayesinde bağlantı kurulan kütüphanenin tümü değil sadece ihtiyaç duyulan fonksiyon yazılım içine çekilir. Böylece VBA yazılımının sade ve hızlı çalışması sağlanır.

Windows işletim sistemi yapısı içerisinde çekirdek kayıtların tutulduğu Advapi32 (Advanced Services) kütüphanesi ile bağlantı kurularak bilgisayar kullanıcı adı ve bilgisayar adını alacak bir örnek geliştirilecektir. VBA kullanıcı formunda bir tuşa tıklandığında API ile Advapi32 fonksiyonları çalıştırılacaktır. Kullanıcı adı ve bilgisayar adı Windows işletim sistemi yapısından alınarak kullanıcı formunda TextBox nesnesinin içerisine yazdırılacaktır.

Birinci aşamada Excel sayfasında Geliştirici sekmesindeki Visual Basic butonuna tıklanarak VBA yazılım platformuna geçilir. S:138, Şekil 7.1’de görüldüğü gibi Insert sekmesine tıklanarak açılan menü içerisinden UserForm seçeneğine tıklanır.

İkinci aşamada UserForm üzerine kullanıcı adı ve bilgisayar isminin yazdırılması için kullanılacak kontrollerden Label, TextBox, CommandButton nesneleri eklenir. S:138, Şekil 7.2’de UserForm üzerinde tıklanacak yerler alfabetik ve ToolBox menüsünde eklenecek kontrol nesneleri ise sayı ile kodlanmıştır.

Üçüncü aşamada Label türündeki kontrol nesnelerinin özellikleri düzenlenmesi için Proporties penceresinde işlem yapılır. S:138, Şekil 7.2’de UserForm üzerinde A harfi ile kodlanmış Label özelliklerinin S:139, Şekil 7.3’te a harfi ile görünen name öğesi lbl_kullanici_adi şeklinde, b harfi ile görünen Caption ögesi Kullanıcı Adı şeklinde düzenlenir.

Dördüncü aşamada TextBox ve CommandButton türündeki kontrol nesnelerinin özellikleri düzenlenmesi için Proporties penceresinde işlem yapılır. S:138, Şekil 7.2’de UserForm üzerinde C harfi ile kodlanmış TextBox kontrol nesnesinin Name öğesi tb_kullanici_adi şeklinde, S:138, Şekil 7.2’de D harfi ile kodlanmış TextBox kontrol nesnesinin Name öğesi ise tb_bilgisayar_adi şeklinde düzenlenir (S:139, Şekil 7.4 a ve c). Son olarak UserForm üzerinde E harfi ile kodlanan CommandButton türündeki kontrol nesnesinin özelliklerinden Name öğesi cmd_goster (S:139, Şekil 7.4 c), Caption öğesi ise Göster (S:139, Şekil 7.4 d) yapılır.

Beşinci aşamada API deklarasyon deyiminin tanımlanması için projeye bir modül eklenecektir.

Altıncı aşamada API deklarasyon deyiminin tanımlanması VBA proje penceresinin içerisinde Module1 nesnesinin üzerine çift tıklanır. S:140, Şekil 7.7’de görüldüğü gibi ve açılan pencere içerisine advapi32.dll kütüphanesinden GetUserName ve GetComputerName fonksiyonları ile hazırlanan VBA yazılımı arasında köprü oluşturulur.

Yedinci aşamada UserForm penceresine ulaşmak için VBA proje penceresinde S:140, Şekil 7.8’de görülen UserForm1 formunun üzerine çift tıklanır.

Sekizinci aşamada UserForm’da S:138, Şekil 7.2’de E harfi ile gösterilen CommandButton kontrol nesnesinin üzerine tıklanarak cmd_goster nesnesinin click olayı oluşturulmuştur. S:140, Şekil 7.9’da a harfi ile kodlanan bölümde fonksiyonlara gönderilecek String ve Long türünde değişken paketleri tanımlanmıştır. b ile kodlanan bölümde fonksiyon çalıştırılarak Long türündeki veri paketine kullanıcı adı atanmış ve tb_kullanici_adi isimli TextBox nesnesine Long türündeki değişken string türüne çevrilerek atanmıştır. S:140, Şekil 7.9 c harfi ile kodlanan bölümde ise aynı işlemler bilgisayar ismi ile ilgili yapılmıştır.

Veri Tabanı İşlemleri

Veri tabanları birbirleriyle ilişkili bilgilerin ihtiyaç duyulduğunda tekrar çağrılabilmesi için hazırlanmış bir tür saklama alanıdır. Veri tabanları; büyük miktardaki bilgileri depolamak için geliştirilmiştir. Cep telefonlarında kullanılan rehber, gündelik hayatta sıkça kullandığımız veri tabanı uygulamalarından sadece biridir. Günümüzde lojistik, telekomünikasyon, idari işlemler, kamu hizmetleri gibi birçok alanda veri tabanı altyapısını kullanan bilgisayar sistemleri ile hizmet verilmektedir. Bilgisayar temelli kayıtların tutulması için hazırlanan veri tabanları veri tabanı yönetim sistemleri (Database Management System-DBMS) aracılığı ile oluşturulur ve yönetilir. Microsoft Access, Microsoft SQL Server, MySQL, Oracle, IBM DB2, Informix, PostgreSQL, Interbase ve Sysbase gibi yazılımlar veri tabanı yönetim sistemlerine örnek olarak verilebilir.

Veri tabanlarında genellikle tablolar bulunur ve bu tablolar birbirleri ile belirli alanlarda ilişkilidir. Veri tabanı ilişkileri birdenbire, birden çoğa, çoktan bire ve çoktan çoğa olabilmektedir.

VBA ile Veri Tabanı İşlemleri

VBA yazılım platformunda veri tabanlarına erişim için 3 farklı ara yüz kullanılabilir.

• ADO (ActiveX Data Objects),
• RDO (Remote Data Objects),
• DAO (Data Access Objects).

Bu erişim ara yüzleri VBA yazılımı ile veri tabanları arasında bağlantı sağlamak için kullanılan bir tür kütüphanelerdir. Bu kütüphanelerde bağlantı, erişim, kontrol ve veri geri dönüşünü içeren fonksiyonlar vardır. Birbirlerinden farklı 3 ara yüz olmasının temel sebebi farklı gelişim evrelerinde kullanılmasıdır. Örneğin, son bağlantı yöntemi ADO diğer bağlantı ara yüzlerine göre daha basit ve esnek bir yapıda geliştirilmiştir. VBA veri tabanı örnekleri ADO ara yüzü ile verilecektir. Çünkü ADO kolay kullanımı olan ve Microsoft’un en yeni ve güçlü veri tabanı bağlantı ara yüzüdür. Bunun yanında veri tabanı yönetim sistemlerinin birçoğuna ADO ara yüzü ile erişilebilir. ADO ile veri tabanı bağlantısı yapılabilmesi için öncelikle referans olarak Microsoft ActiveX Data Objects X.X Library kütüphanelerinden biri eklenmelidir. Bunun için VBA yazılım platformunda Tools üst menüsü ve Referances seçeneği kullanılarak kütüphane ekleme işlemi yapılır. VBA ile veri tabanı farklı yazılımlar olduğundan ADO ara yüzü aracılığı ile veri tabanı bağlantısı yapılabilir.

VBA ile ADO aracılığı ile veri tabanı işlemlerini aşağıdaki gibi özetlemek mümkündür:

-Bağlantı:

• Yeni bağlantı tanımı,
• Veri tabanı bağlantı yolu atanması,
• Bağlantının açılması.
• -Tablo:
• Yeni tablo tanımı,
• Tabloya veri tabanı bağlantısının atanması,
• Tablo kaynağının tanımlanması,
• Tablonun açılması.
• -Tablo işlemleri:
• Veri tabanından okuma,
• Veri tabanına yazma,
• Veri tabanından silme.

-Kapatma:

• Tablonun kapatılması,
• Tablo yolunun temizlenmesi,
• Bağlantının kapatılması,
• Bağlantı yolunun temizlenmesi.

VBA ile veri tabanı uygulama örneği kapsamında Excel VBA ile hazırlanmış bir kullanıcı formu ile Microsoft Access veri tabanına bağlanacak bir yazılım hazırlanacaktır. Bağlantı

ADO ara yüzü ile gerçekleştirilecektir. VBA kullanıcı formu ile kullanıcıdan alınacak adı, soyadı ve yaş verileri veri tabanına eklenecektir. Ayrıca eklenen verileri düzeltecek veya silecek butonlar da tasarlanacaktır. Adı, soyadı ve yaş verileri TextBox kontrol nesnesinden alınacaktır. Veri tabanından okunan veriler ListBox içinde görüntülenecektir. Kullanıcı ListBox içerisinden seçtiği veriyi TextBox kontrol nesnesinin içerisine aktararak silme veya düzenleme yapabilecektir.

Birinci aşamada Microsoft Office 2013 programları arasında yer alan Microsoft Access programı çalıştırılır. S:143, Şekil 7.10’ da a harfi ile gösterilen Boş masaüstü veri tabanı seçeneğine tıklanır.

İkinci aşamada S:143, Şekil 7.11’de a harfi ile gösterilen pencere görüntülenir. Microsoft Access programı, varsayılan olarak bir tablo oluşturmuştur.

Üçüncü aşamada Microsoft Excel programı açılarak Geliştirici sekmesindeki Visual Basic butonuna tıklanır ve VBA yazılım platformuna geçilir. VBA yazılım platformunda S:144, Şekil 7.12’de a harfi ile gösterildiği gibi üst menüden Tools menüsü açılarak References menüsüne tıklanır.

Dördüncü aşamada VBA yazılım platformunda Insert açılan menüsünden UserForm seçeneği seçilerek projeye bir kullanıcı formu eklenir (S:144, Şekil 7.13).

Beşinci aşamada UserForm üzerine adı, soyadı ve yaş verilerinin kayıt, silme ve düzeltme işlemleri için kullanılacak Label, TextBox, CommandButton ve ListBox nesneleri eklenir. S:144, Şekil 7.14’te UserForm üzerinde tıklanacak yerler alfabetik ve ToolBox menüsünden eklenecek kontrol nesneleri ise sayı ile kodlanmıştır. Sırasıyla önce ToolBox üzerinde 2 numaralı Label kontrol nesnesine sonra UserForm üzerinde A, B ve C harfi ile kodlanan yerlere tıklanır.

Altıncı aşamada Label türündeki kontrol nesnelerinin özellikleri düzenlenmesi için Proporties penceresinde işlem yapılır. S:144, Şekil 7.14’te UserForm üzerinde A harfi ile kodlanmış Label özelliklerinde S:154, Şekil 7.15’te a harfi ile görünen Name öğesi lbl_adi, b harfi ile görünen Caption öğesi ise Adı şeklinde düzenlenir (A-a lbl_adi, A-b Adı).

Yedinci aşamada S:144, Şekil 7.14’te UserForm üzerinde D, E ve F harfleri ile kodlanmış Text- Box türündeki kontrol nesnelerinin Name öğeleri S:145, Şekil 7.16’da görüldüğü gibi tb_adi (Da), tb_soyadi (E-b), tb_yasi (F-c) şeklinde düzenlenir.

Sekizinci aşamada kayıt, silme ve düzeltme işlemleri için kullanılacak komut düğmelerinin (S:144, Şekil 7.14 G, H ve I) Name ve Caption öğeleri ile ilgili işlem yapılır. S:145, Şekil 7.17’de görüldüğü gibi G-a cmd_kaydet, G-b Kaydet, H-c cmd_duzelt, H-d Düzelt, I-e cmd_sil ve I-f Sil şeklinde düzenlenir.

Dokuzuncu aşamada S:144, Şekil 7.14’te J harfi ile gösterilen ListBox kontrol nesnesinin özellik öğesi S:146, Şekil 7.18’de görüldüğü gibi düzenlenir.

Kullanıcı formu tasarımı bittiğinde S:146, Şekil 7.19’da görüldüğü gibi olacaktır.

Onuncu aşamada kullanıcı formu tasarımı tamamlanmasının ardından kod yazımına geçilir. Bu aşamada bağlantı yolu atamasının kolay yapılabilmesi ve esnek olması için baglanti yolu şeklinde tanımlanmış ADO ara yüzü için kullanılacak bir fonksiyon tanımlanır (S:146, Şekil 7.20).

On birinci aşamada veri tabanından veri okuyarak ListBox içerisine okunan verileri atayacak alt yordam tasarlanacaktır.

On ikinci aşamada on birinci aşamada tanımlanan veri tabanından veri okuma alt yordamını tetikleyecek kullanıcı formu nesnesi bağlantısı S:147, Şekil 7.22’de görüldüğü gibi yapılır.

On üçüncü aşamada kayıt yapma alt yordamı tasarlanacaktır. Kullanıcı formunda tb_adi, tb_soyadi ve tb_yasi şeklinde isimlendirilen metin kutularına yazılanların veri tabanına kayıt edilmesi için bu alt yordam kullanılacaktır.

On dördüncü aşamada kayıt yapma alt yordamının cmd_kaydet isimli Command-Button kontrol nesnesi içerisinden tetiklenmesi için VBA kodu hazırlanacaktır. Buton içerisinde ilk olarak metin kutularının tümünün dolu olup olmadıkları S:148, Şekil 7.24’ te a harfi ile gösterilen alanda kontrol edilir.

On beşinci aşamada kullanıcı formunda ListBox kontrol nesnesi içerisinde verilere tıklanması durumunda çalışacak alt yordam hazırlanacaktır. ListBox kontrol nesnesi içerisinde veri tabanından okunmuş olan kayıtlar vardır. Veri tabanından okunan kayıt alanları arasında tire (-) işareti vardır. ListBox kontrol nesnesi içerisinde yer alan verinin üzerine tıklandığında tire (-) işaretinden veriler bölünerek adı, soyadı ve yaşı bilgileri metin kutularının içerisine yazdırılır. Bu işlem için S:149, Şekil 7.25 a harfi ile gösterildiği gibi Variant veri tipinde secilen_kayit isminde değişken paketi tanımlanır.

On altıncı aşamada ListBox kontrol nesnesi içerisinde veri tabanından okunup Text-Box kontrol nesnelerinin içerisine on beşinci aşamada atılmış olan verilerin düzeltilme işlemi için işlem yapılacaktır.

On yedinci aşama veri düzeltme işlemlerinin yapıldığı on altıncı aşamadaki adımların benzerlerini içerir. En önemli farkı verilerin güncellenmemesidir, bunun yerine Tablo.Delete komutu kullanılarak tablodan seçilerek açılan veri silinir (S:151, Şekil 7.27).