13.03.2018 tarihinde,VBA konularına Worksheet Olayları sayfası eklenmiştir.

16.02.2018 tarihinde,VBA konularına Workbook Olayları sayfası eklenmiştir.

28.12.2017 tarihinde,VBA konularına Dosya Okuma/Yazma sayfası eklenmiştir.

20.12.2017 tarihinde,sitem mobil uyumlu hale gelmiştir.

VBAMakroDört Temel Nesne4

Nesnelerin Efendisi - Application

Önceki bölümlerden hatırlayacağınız üzere, Excel, nesneler hiyerarşisi üzerine kurulmuş bir Nesne Modeline sahiptir ve işte şimdi göreceğimiz Application nesnesi de hiyerarşinin en tepesinde bulunur.

Application nesnesi Excelin ta kendisidir ve bu yüzden de default nesnedir. Bu şu demek, bazı durumlarda bu ifadeyi yazmanıza gerek olmadan buna ait özellik ve metodları kullanabiliriz. Yani Application.ActiveWorkbook yazmak ile ActiveWorkbook yazmak arasında hiçbir fark yoktur. Ancak bazı durumlarda da Application'ı açıkça yazmak gerekir. Aslında bu konu Global sınıfı ile ilgili bir konu olup önbilgi adına buraya bakabilirsiniz. Eğer Excelin kendisiyle ilgili bir işlem olacaksa o zaman Application'ı açıkça yazmamız gerekir. Mesela Excelden çıkış için Quit metodunu kullanmak, Excelin ekrandaki boyutlarını ayarlamak gibi. Bununla beraber benim tavsiyem, Application'ı her durumda yazmanızdır, ancak olur da internette araştırma yaparken Application'ın yazılmadığını görürseniz de şaşırmayın. Aşağıda özellik ve metodların tanıtımında Application'ın yazılması gereken durumlar için bunu açıkça yazdım, diğer durumlar için yazmadım, ancak kod örneklerinde size verdiğim tavsiyeyi tuttm ve Applicationu hep yazmaya çalıştım.

Bu arada herhangi bir nesnenin Application özelliğini kullanarak da bu Application nesnesini elde edebiliriz. Daha teknik bir ifadeyle, bazı nesnelerde bulunan Application özelliği(propertysi) Application tipinde değer döndürür, yani Application nesnesi elde edersiniz. Tabi bazen kullandığımız nesne Excel Nesne Modeline ait bir nesne değil de mesela bir Outlook nesnesi olabilir, böyle bir durumda Application propertysi kullandığınızda dönen değer tabiki Excel değil, Outlook olacaktır. Bunları Diğer Ofis uygulamlarıyla çalışmak bölümünde göreceğiz.

Genel Görünüm ve Uygulama Seviyesi İşlemleri

Excel'in genelini ilgilendiren birçok üye mevcuttur. Bunların birçoğu File>Options'tan ulaşabileceğiniz ayarların VBA karşılıklarıdır. Önemlilerine, daha doğrusu kısa ve orta vadede kullanma ihtimaliniz olan üyelere bir bakalım.

Application.DisplayFullScreen özelliği: Boolean tipinde değer döndürür.True atandığında Ribbon olsun durum çubuğu olsun hiçbirşey göstermez, sadece hücreler ve formül çubuğu görünür.

Application.DisplayFormulaBar özelliği: Bu da Boolean tiplidir. False atanırsa formül çubuğu gösterilmez. Bunu bazen formülleri göstermemek için(protection yaparak da sağlanır) bazen de Displayfullscreen özelliği ile birlikte, ekranda maksimum alanda yer açmak kullanılır.

Application.DisplayStatusBar özelliği:Bu da Boolean tiplidir, En alttaki durum çubuğunu gösterir veya gizler. (Application.StatusBar özelliği ile karıştırılmamalıdır, bu ikincisinde durum çubuğunda yazan metni alırız veya metin yazarız.)

Application.DisplayScrollbars özelliği:Bu da Boolean tiplidir, scrollbarları gösterir veya gizler.

Application.Interactive özelliği: Boolean döndürür. Diyelim ki çok fazla copy-paste yapan bir makronuz var, kodunuz da uzun sürüyor, beklerken o sırada Word veya Outlook'ta vakit geçireyim dediniz. Outlookta yazdığınız bir metni kesip başka bir yere kopyalamaya karar verdiniz, ancak tam az önce de Excel VBA kodunuz da bi kesme işlemi yapmıştı, siz şimdi clipboarda Outlook metnini almış oldunuz ve kodunuz hızlıca akıp geçti, ve paste işlemini yaparken Excelden aldığı parçayı değil, Outlooktaki metni yapıştırdı. İşte böyle bir durum olmasın diye bu tür işlemlerinizin olduğu kodlarınızın başına bu özelliği yazıp False değerini atayabilir, kodun sonunda bunu yine True'ya döndürebilirsiniz.

Application.Interactive = False
'kodlar
Application.Interactive = True

Application.Quit metodu:Excelden çıkış için kullanılır.

Bu arada Excelden çıkış yapılmasını yakalayacak bir event yok malesef. Bunu farklı yöntemlerle tespit eden bazı makaleler gördüm ama oldukça karışık olduğu için buraya almak istemedim. Ben şahsen, bunun yerine Personal.xlsb dosyasının kapanıp kapanmadığını bu dosya içindeki Workbook_Beforeclose olayı ile yakalıyorum, bunun kapanması demek zaten birçok durumda Excelin kapanması demek oluyor, ki bu da işimi görüyor. Bunların detayını Olaylar(Events) bölümünde göreceğiz.

Application.StatusBar: Görev çubuğuna mesaj yazmak için kullanılır. Özellikle kullanıcıları hem bilgilendirmek hem de bilinçli bir şekilde mesaj kutusu çıkarmak istemediğinizde faydalıdır. (Bazen schedule edilmiş işlerde arkadan gelen kodların takılmasını engellemek için, bazen de kullancılardan gelen "Bu kadar mesajbox çok can sıkıcı" itirazlarını ele almak için). Ancak unutulmamalıdır ki, MsgBox kadar da dikkat çekici değildir, hatta bazı durumlarda verdiğiniz mesaj gözden kaçabilir de. O yüzden kritik mesajları MsgBox ile vermenizi tavsiye ederim. Aşağıda bu özelliği ProgressBar olarak nasıl kullanıyoruz, onu da göreceğiz.

Son Söz

Object Browser veya MSDN üzerinden Uygulama seviyesinde yapılabilecek daha bir çok ayarlama olduğunu görebilirsiniz, buraya önemli olduğunu düşündüklerimi aldım, diğerlerini siz de araştırabilrsiniz. Bunların çoğu Excel Options üzerinden yapacağınız ayarlamalara denk gelir. Ör: Autorecover ayarı, autocorrect ayarı, dosyalar açıldığında linkleri update etsin mi ayarı gibi. 

Kod hızlandırıcılar

Aşağıdaki üç özellik kodlarınızın başında False sonunda True olarak ayarlandığında performans kazanımı sağlar.

Application.ScreenUpdating özelliği:Uzunca bir makro çalışırken ekranın bi gidip geldiğini, titrediğini görmüşsünüzdür(veya göreceksinizdir), hele hele farklı workbooklar arasında gidip gelme sözkonusu ise bu durum çok daha göze çarpar. Aslında tüm bu ekran hareketleri, genel süreci uzatan bir rol oynar, o yüzden bu ekran hareketini kapatarak kodunuzu hızlandırabilirsiniz. Bunu da bu özelliğe False değeri atayarak yapıyoruz. Kod bitmeden hemen önce açmayı unutmayın tabi.

Kod çok uzun sürüyorsa ScreenUpdating=False durumunda kullanıcılar Excelin kitlendiğini düşünebilir, o yüzden arada bir hareket göstermek iyi olabilir. Bunu da Doevents metodu ile yapabiliriz.

'Genellikle bir döngü içinde mantıklıdır
Application.ScreenUpdating=False
Do Until oldumu = True
'ara kodlar
DoEvents 'burda ekran tazelenir
Loop

Application.DisplayAlerts özelliği: Kodunuz çalışırken Excel bize bazı uyarılar çıkarabilir, bunlar da genelde can sıkıntısı yaratabilir. Özellikle schedule edilmiş makrolarınız varsa ve bunların birinde bir sayfa silme, veya varolan dosya üzerine yazma gibi size uyarı çıkaran kodlar varsa, bu özelliğe False atamazsanız ekran ilk uyarıda takılı kalır ve sizin bir cevap vermenizi bekler. Eğer bir seri schedule edilmiş kodunuz varsa, böyle bir durum kabul edilemez. O yüzden en faydalı bulduğum özelliklerden biri budur. Bu özelliğe false atandığında uyarılara varsayılan cevap verilir ve kod devam eder. Kod bittiğinde de bu özelliğe otomatik True değeri atanır.

Ancak bu özellik iki durumda işe yaramaz.

  • Mesaj kutularında. Özellikle schedule programınız varsa içinde mesaj kutusu kullanmamaya çalışın. Hata yönetimi işlemlerinde bile kullanmayın, onun yerine kendinize mail gönderebilir, StatusBarı kullanabilir veya Log kaydı tutabilirsiniz.
  • İçinde başka dosyalara link olan dosyalar açıldığında Linkleri update edeyim mi sorusu. Burda DisplayAlerts yerine Workbook.Open metodunun UpdateLinks parametresi kullanılır. Buradan bakabilirsinz. Gerçi bunu da aşmanın bir yolu var, ama dikkatli kullanılmasında fayda var.Application.AskToUpdateLinks özelliğine False atanırsa linkler otomatik güncellenir, ve bu soru karşımıza çıkmaz, böylece her Workbook.Open metodunda tek tek Updatelinks özelliğine değer girmek zorunda kalmayız. Ama bazı durumlarda otomatik update olmasın isterseniz bunu kullanmak yerine Workbook.open metodunun Updatelinks parametresini kullanın.

Application.EnableEvents özelliği: DisplayAlerts ile birlikte en çok değer verdiğim bir diğer özellik de budur. Hatta QuickAccess barda bu özelliği True ise False, False ise True yapan bir düğmem bile var. Bunu  biraz sonra açıklayacağım. Öncelikle ne işe yarar ona bakalım.

Bu özellik, herhangi bir event(olay) tetiklenmesin diye kullanılır. İki tür kullanım şekli olabilir.

  • Bir programın en başına false, en sonunua tekrar true olacak şekilde. Böylece tüm kod boyunca hiçbir olay tetkilenmez.
  • Bir döngü içinde satır silme, hücre değeri değiştirme gibi bir işlem vardır, ve sayfa modüllerin birinde Worksheet_change eventiniz de vardır, sadece bu event trigger olmasın diye ilgili döngünün başına ve sonuna konur. Böylece döngüden çıkıldığında diğer eventlerin trigger olabilmesine imkan verilmiş olur.

Ben çok faydalı bulduğum bu 3 özelliği bir prosedüre bağladım, ve birçok makroya girerken bunlara(bazen sadece ikisine) false değerini atıyorum, koddan çıkarken de tekrar true değerine döndürüyorum. Fonksiyon ve kullanım şekil aşağıdaki gibidir:

'Ana prosedürü
Public Sub AlertUpdatingEvent(a As Boolean, u As Boolean, Optional e As Boolean = True)
With Application
  .DisplayAlerts = a
  .ScreenUpdating = u
  .EnableEvents = e
End With
End Sub

'Prosedürü çağırma şeklim
AlertUpdatingEvent False, False 'Son parametreyi eklemediğimi için default değeri olan True atanır. Böylece sadece uyarıların çıkmasını ve ekran titreşimini engellemiş ama eventler çalışsın istemiş oldum

Hesaplama, Zamanlama(Scheduling) ve Bekle(t)me

Bu başlıktaki konular her zaman olmamakla birlikte genelde birarada kullanılmaktadırlar, en azından bir kullanım yakınlığı vardır diyebiliriz.

Hesaplama işleri

Application.Calculation özelliği: Excelin formüller için hesaplama yöntemini seçmenizi sağlar. Excelin bu özelliğini bildiğinizi varsayıyorum, bilmiyorsanız öncesinde mutlaka buraya, buraya ve buraya bakın.

Bu özelliğin alabileceği 3 enumaration değeri var.

xlCalculationAutomatic :Varsayılan değer budur. Herhangi bir hücrede değişiklik olduğunda tüm workbooklarda formüller yeniden hesaplanır.
xlCalculationSemiautomatic :Table'lar dışında herşey otomatik hesaplanır.
xlCalculationManual :Hesaplama işlemi kapalıdır. Kullanıcı hesaplama yapana kadar da öyle kalır.

Özellikle büyük formüllü dosyalarda bir makro çalıştıracaksanız ve herhangi olumsuz bir etkisi olmayacaksa öncesinde hesaplama kapatılıp makro bitmeden hemen önce de tekrar açılabilir.

Application.Calculation = xlCalculationManual
'burada diğer işler yapılır
Application.Calculation = xlCalculationAutomatic

Calculate metodu: Tüm workbooklardaki yeni, değişmiş ve volatil formüllerin hesaplanmasını sağlar(o anda Manuel hesaplama seçimi yapıldıysa anlamlıdır, aksi halde zaten formüller hesaplanmıştır ve gerek yoktur). Calculation property'sinin aksine bunda Application denmesine gerek yoktur. Örnek biraz aşağıda bulunmaktadır.

Application.CalculationState özelliği: Hesaplamanın ne durumda olduğunu gösterir. Alabileceği değerleri bir kodla görelim.

Sub CalcState()
If Application.CalculationState = xlDone Then 'enumeration değeri 0
	MsgBox "Hesaplama Bitti"
ElseIf Application.CalculationState = xlPending Then 'Görev çubuğunda "Calculate" yazar 'enumeration değeri 2
	MsgBox "Tetiklendi ama henüz hesaplama başlamadı"
Else 'xlCalculating 'enumeration değeri 1
	MsgBox "Hesaplama devam ediyor 'Görev çubuğunda %sel bir oran görünür
End If
End Sub	 

Done ve Calculating gayet aşikar fakat Pendingi tam olarak anlamamış olabilirsinz. Hani bazen Excel manuel hesaplama modundayken, formüllerden birine baz teşkil eden bir hücreyi değiştirdiğinizde en alttaki durum çubuğunda Calculate yazar, bazen de dosyanızda çok sayıda formül varsa Excel bu kadar formülle başa çıkamaz ve en altta yine Calculate yazar. İşte bu durum xlPending durumudur. Böyle durumlarda Excelde hesaplama yapmak için Formulas menüsünden Calculate demek veya F9'a basmak gerekir.
NOT:Bir de kısırdöngülü formüllerde Calculate yazdığını görürsünüz, bu da bir xlPending durumudur ancak onun çözümü aşağıdakiler değil, kısırdöngüye neden olan formülü düzeltmektir.

İnternette birçok forumda yaygın bir kullanım örneği olarak aşağıdaki kod parçası verilir. Deniyor ki, "kodunuz çalışmaya başlamıştır, büyük bir hesaplama yapıyordur, ancak daha hesaplama bitmeden bir sonraki satıra geçer, bu da hatalı sonuçlar neden olabilir, o yüzden aşağıdaki kod ile kodununzun aşağı satıra geçmesini engellersiniz".

Application.Calculate 'hesaplamaya başladınız
Do While Application.CalculationState <> xlDone
     DoEvents
Loop
'kodun kalan kısmı

Halbuki Calculate metodu asynchoronus değildir, yani hesaplama bitmeden zaten bir sonraki satıra geçmez. O yüzden yukarıdaki tavsiye bence anlamsızdır. Ancak bir şekilde(forumlarda yardım isteyen diğer kişilerin başına gelen çok özel durumlarda, artık neyse o özel durumlar bilemiyorum) böyle birşey olduğunu farkederseniz bu kodu kullanabilirsiniz.

Bu arada şu farkı iyi anlamanız gerekiyor; Calculate metodunu uyguladığınızda sanki Excelde F9'a basmış veya Calculation menüsünden Calculate butonuna basmış gibi olursunuz ve formüller yeniden hesaplanır ancak sayfanız o an hala Manuel modda kalmaya devam eder ve sonraki aşağı/sağa formül kaydırma işlemleri sonucunda formüller hesaplanmaz. Halbuki Calculation özelliğine xlAutomatic atayarak hem hesaplamayı açmış olurusunuz hem de statüyü kalıcı olarak Otomatiğe çevirmiş olursunuz ve sonraki formül kaydırmalarda formüller hemen hesaplanır. Hangisi ihtiyacınıza uygunsa onu kullanmalısınız. Eğer ki geçici bir hesaplama yapmak istiyorsanız Calculate metodunu, kalıcı hesaplama için ise Calculation özelliğini kullanabilirsiniz.

Önemli bir husus da şudur; Application, Worksheet ve Range nesnleri için varolan Calculate metodu Workbook için bulunmamaktadır. Ancak aşağıda gibi bir kod ile sadece Activeworkbook'un Calculation işlemini yapabilrsinz.

Sub CalcBook()
    Dim ws As Worksheet
    Application.Calculation = xlManual
    For Each ws In ActiveWorkbook.Worksheets
        ws.Calculate
    Next
    Set ws = Nothing
End Sub

Application.CalculateFull: Otomatik veya Manuel modda olun farketmez, tüm formüllü hücreleri yeniden hesaplar. Calculate metodundan farklı olarak, sadece yeni, değişmiş ve volatil formülleri değil, tüm formül içeren hücreleri tekrar hesaplar. Bu yüzden genelde(her zaman değil) normal Calculate metoduna göre daha yavaştır. Durum çubuğunda ısrarla Calculate yazıyorsa yani xlPending durumundan bir türlü çıkamıyorsanız bunu kullanabilrsiniz. Klaveye kısayolu Ctrl+Alt+F9'dur.

Application.CalculateFullRebuild:Bu metod CalculateFull ile aynı işi yapıyor gibi görünüyor, Excel 2007 ve sonrası kulanıcıların çok kullanacağı bir metod değildir. Özetle şunu diyebilirim ki, 2007 öncesi versiyonlarda aşırı formülden dolayı hesaplama zinciri bozulduysa ve F9 yaptığınız halde Excel hesaplama yapmıyorsa bu metod işe yarayacaktır. Ancak sanki tüm hücrelere formülleri tekrar girmek gibi iş yaptığı için CalculateFull'e göre biraz daha yavaştır.

Application.CalculationInterruptKey:Hesaplamanın hangi tuşla iptal edileceğini söyler. Bunun pratik kullanımı, Personal.xlsb'nin Workbook_Open makrosu içine yazma şeklindedir. Ben şahsen sadece ESC tuşuna(xlEscKey) basıldığında hesaplamanın iptal edilmesini istiyorum, size de bunu öneririm. Zira eliniz yanlışlıkla bi ok tuşuna değse bile o anda %90larda olan calculation tekrar %0dan başlayacaktır.

Genel öneriler

  • Önce normal Calculation yapın. Sonra state kontrol edin, hala xlPendingse CalculateFull uygulayın.
  • Kod hızlandırıcılar bölümündeki 3 özelliğe bazen bu Calculation'ı da ekleyerek daha hızlı kod çalıştırabilirsiniz. Ancak kullanımı konusunda dikkatli olmak gerekir, zira arada bir yerlerde formül çekme/uzatma ve sonra Copy-Paste işlemi varsa Calculation sonucunda hatalı durumlar oluşabilir.
Sub Calculationlar()

AlertUpdatingEvent False, False, False
Application.Calculation = xlCalculationManual
'kodlar buraya gelir

'arada bir açmak gerekebilir
ActiveSheet.Calculate 'duruma göre Application.Calculate veya Range("...").Calculate
If Application.CalculationState=xlPending then Application.CalculateFull
'tekrar kapatalım
Application.Calculation = xlCalculationManual
'diğer kodlar

'çıkışta tekrar eski haline getiriyoruz
AlertUpdatingEvent True, True, True
Application.Calculation = xlCalculationAutomatic

End Sub

Son olarak Calculate işlemlerinin VBA ve Excel ilişkilerini tekrar şöyle bir özetleyelim: 

İşlem Excel VBA
Tüm workbookları hesaplatmak Calculation>Calculate(veya F9) Application.Calculate
Aktif sayfayı hesaplatmak Calculation>Calculate Sheet(Shift+F9) ActiveSheet.Calculate
Aktiveworkbook hesaplatmak - Döngü içinde Sheet.Calculate
Belli bir range'i hesaplatmak - Range.Calculate
Full hesaplama yapmak Ctrl+Alt+F9 Application.CalculateFull

Calculation için örnek bir senaryo

Şimdi diyelim ki departmanınızdaki kişilerin kullanması için çok sayfalı ve çok formüllü bir excel dosya hazırladınız. İlk sayfada tek sayfalık bir karne/skorkart tarzı birşey var, diğer sayfalarda ise toplu listeler. Hepsi de datayı gizli bir sayfadan alıyor.

Liste sayfalarında çok fazla satır ve sütun ve hep SUMIFS tarzı formüller olduğu için bunlarda sort veya filter işlemleri çok ağır olmaktadır, zira bu iki işlem de calculation tetikleycisidir. Çözüm şöyle olabilir:

Dosyanın Workbook_Activate(Neden Workbook_Open olmadığını az sonra belirteceğim) eventine dosya açılır açılmaz Calculation'ı xlManual yapan kodu ekledim, ve bi mesajbox ile bunu kullanıcıya bildiriyorum.(Mesajbox sinir bozucu gelirse statusbara da yazdırabilirsiniz). Karne sayfasına gelince ise Worksheet_Change eventine yazdığm kod ile sadece belli hücreler değiştiğinde hesaplama yapmasını sağlıyorum. Kullanıcı olur da o sırada başka dosyalarda işlem yapmak isterse Workbook_Deactivate eventine calculationı tekrar otomatik yapan bir kod yazdım ki, kullanıcı o sırada Calculationın kapatıldığını unutup diğer dosyalarda formül uzatma gibi işler yaparsa hep aynı sonucun yazdığını görüp şaşırmasın. Hatta ortalama bir kullanıcı Excelin Calculation özelliğinden bihaberdardır bile diyebiliriz. Kodlar şöyle:

Private Sub Workbook_Activate()
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Dosya aktive olduğu için Calculation yine geçici olarak Manuel yapıldı"
End Sub
 
Private Sub Workbook_Deactivate()
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = "Başka dosyayı açtığınız için Calculation tekrar otomatik yapıldı"
End Sub
		

Application.Volatile metodu:Bu metoda UDF bölümünde değindiğimiz için burada ayrıca bahsetmiyorum.

Erteleme ve bekleme

Application.OnTime metodu: Site boyunca zaman zaman programlanmış işlerden veya İngilizce tabiri ile işleri schedule etmekten bahsediyorum, mutlaka dikkatinizi çekmiştir. İşte bu işi bu harika metod ile başarıyorum. Genel kullanım şeklini aşağıda veriyorum ama bu konuyla ilgili uzunca bir örneğe sayfanın en altında ayrı bir bölüm ayırdım.

Şimdi metodun genel syntaxına bakmadan önce görevini açıkça belirtelim: Bir prosedürün belirli bir anda çalışmasını sağlar, ki bu tanım bize onu asıl amacı dışında(ama faydamıza olacka şekilde) kullanacağımızı da söylemektedir, yani Wait ve Sleep metodları yerine. Bunlara da hemen bu metoddan sonra değineceğiz.

Syntax: ApplicationObject.OnTime(EarliestTime, ProcedureName, LatestTime, Schedule

Tam açıklaması şöyle oluyor. ProcedureName ismindeki makro EarlistTimeda başlasın, o sırada başka bir makro çalışıyorsa veya Exceli meşgul eden başka birşey varsa da LatestTime'a kadar çalıştırmayı denesin. Eğer LatestTime belirtilmezse, excelin meşguliyeti bitene kadar bekler ve sonra çalıştırır. Yani eğer, "Kod,programladığım saatten en geç 1 saat içinde çalışsın, yoksa çalışmasının bi anlamı yok, çünkü o rapor artık işe yaramaz olur" dediğiniz bir durum varsa bu parametreyi "EarliestTime + 1 saat" olarak belirtebilirsiniz, aksi durumda boş bırakın. Schedule parametresi default değeri True'dur ve genelde yazılmaz, schedule ettiğiniz bir prosedürü iptal etmek için bu değere False atarsınız.

Önemli Not:Excelden her çıkış yaptığınızda, tüm schedule programı sonlanır. Eğer, recursive(tekrarlı) yani bittikten sonra yeniden schedule edilen programınız varsa Exceli hep açık bırakmanız gerekir, ki benim bilgisayarımda olan budur. Ve bence bu metoddan verim almanın en güzel yolu onu recursive bir şekilde kullanmaktır. Şimdi küçük bir örnek bakalım, siz sonra buradan daha detaylı örneği incelersiniz.

Sub ontimeornek()
    Application.OnTime Now + TimeSerial(0, 0, 3), "mesajver" 'Asynhronous metoddur
    MsgBox "beklemeden çalıştım"
End Sub

Sub mesajver()
    MsgBox "selam"
End Sub

Örnekte gördüğünüz üzere mesajver makrosunun çalıştırılacağı zamanı Run tuşuna bastıktan 3 sn sonra çalıştıracak şekilde parametrik verdim. Yani burada spesifik bir saat belirtmek yerine, şimdiye(Now) referansla bir saat de verebiliyoruz. Örnek gösterimler şöyle olabilir.

Application.OnTime "22:30:00"
Application.OnTime Now + TimeValue("00:10:00") 'Şimdiden 10 dk sonra
Application.OnTime Now + TimeSerial(0,10,0) 'Bu da aynı. TimeSerial'de saat, dakika ve saniye virgülle ayrılır

Application.Wait metodu: Programın belirli bir süre durmasını(beklemesini) sağlar. Peki neden? Neden programınızın bir süre durmasını bekleyesiniz ki? İşte örnek senaryolar olmayınca malesef makro öğrenimi çok zor olmaktadır. Önce gelin nasıl kullanılacağına , sonra nedenine bakalım.

Sub bekle()
   Application.Wait (Now + TimeValue("0:00:10")) 'Synhronous metoddur
   MsgBox "bekleyip çalıştım"
   Call mesajver
End Sub

Sub mesajver()
    MsgBox "selam"
End Sub

Bu metod Boolean döndürdüğü için belirli bir zaman geçip geçmediğini kontrol etmek için de kullanılır.

If Application.Wait(Now + TimeValue("0:00:10")) Then '10 sn geçtiyse. =True demeye gerek görmeyebiliyoruz, önceki konuları hatırlayacak olursanız
 Application.Speech.Speak "Zaman doldu" 'Evet, Excel 2013ten itibaren artık konuşuyor 
End If

Dikkat: Bu metod kullanılırken çok dikkat etmek gerekir, zira ilgili süre geçene kadar Excel kitlenir.

Şimdi de örnek bir senaryo düşünelim. Diyelim ki bir makronuzu sabah/gece 5'e schedule ettiniz: Kodunuz bir veritabanını güncel veri gelmiş mi diye 10 dk'da bir tarıyor, ve sonunda 5:40ta yani 4. seferinde güncel datayı gördü ve hemen çekti, bikaç işlem yaptı, 5.43te işi bitti ama kod devam ediyor, ettiği yerde başka bir veritabanı bağlantısı yapacak, ama siz biliyorsunuz ki o veritabanı 6:30da doluyor, 5.43te buraya bağlanmaya çalışırsa güncel olmayan veriyi alabilir, işte böyle bir durumda kodu 6:30a kadar bekletmek gerekebilir.

Application.Wait "06:30:00"

Hatta bunu bir de saat 6:30dan önce  mi diye kontrol etmek lazım, eğer ilk veritabanını sorgulanması 5:40 değil de 6:30dan sonraya kaldıysa ikinci veritabanı için beklemelik bir durum olmayacaktır, o yüzden Wait kullanmamak gerekir, aksi halde ertesi sabah 6:30'a kadar Exceliniz bloke olur.

Bir diğer örnek durum da şu olabilir. Veritabanı işlemlerinde göreceğiz gerçi ama RefreshAll gibi asyncrohnous(sonraki satıra geçmek için beklemeyen) bir metod çalıştığında kod okuma devam eder. Eğer tüm refresh işleminin bitmesini beklesin istiyor ve tahminen refreshin ne kadar süreceğini biliyorsanız ilgili süre kadar bekletebilirsiniz.

'Önceki kodlar
Me.RefreshAll
Application.Wait Now + "00:30:00" '30 dakika yeterlidir
'diğer kodlar

Yine bu linkte sorulan soruya da güzel bir yanıt verilmiş. Soru şu: "Bir veritabanından 3000 farklı kayıt okumaya çalşıyorum, ancak bazen kod o kadar hızlı akıyor ki, bazı kayıtları okuyup hedef dosyaya yazdıramıyorum. Excelin her kayıt için yeterince beklemesini nasıl sağlarım." Buna verilen cevap oldukça güzel, gerçi bunda Wait metodu yerine hemen bir alttaki Sleep fonksiyonu kullanılmış. Ayrıca DoEvents bilgisi de gerekiyor, ki o da Sleepten hemen sonraki konu.

Sleep Metodu: Bu metod VBA metodu olmayıp Windows fonksiyonudur, bu yüzden bunu kodumuzun başına import etmek gerekir. Wait ile aynı işi yapar, tek farkı, milisaniye cinsinden paremetre almasıdır. İmport işlemi dahil tam bir kod örneği aşağıda bulunmaktadır. (import işlemleri hakkında detaylı bilgi burada mevcuttur)

'bu kısım import kısmıdır, kod sayfasının en tepesine yazılır
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) '64 Bit Sistemler için
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) '32 Bit Sistemler için 
#End If

'bu kısım esas kod kısmı
Sub sleepmetodu()
    MsgBox "Başlıyorum"
Sleep 10000 '10bin mili saniye yani 10 sn bekliyoruz
    MsgBox "Süre bitti"
End Sub

Yukarda belirttiğimiz gibi, OnTime metodunu da amacı dışında bir nevi bekleme amacı olarak kullanabiliriz, ancak Wait ve Sleepte kod akışı o satırda duruken OnTime'da durmaz devam eder, o yüzden OnTime eğer bu amaçla kullanılacaksa ilgili prosedürün son satırı olmasında fayda var.

Sub tetikleyici()
'çeşitli kodlar
Application.OnTime Now + TimeSerial(1,0,0), "makrom"
End Sub

'şimdi bu durumda 1 saat sonra makrom makrosu başlayacak. Ekran 1 saat boyunca serbest.
Sub makrom()
'çeşitli kodlar
End Sub

Bunların dışında bir de farklı bir yaklaşım var ki, bunda da OnTime'da olduğu gibi bekleme süresince Excele erişimimiz açık durmaktadır, zira bunda bir döngü içinde DoEvents metodu kullanılmaktadır.

Dim newTime As Date
newTime = Now + TimeValue("00:00:10")
Do While Not Now >= newTime
    DoEvents
Loop
MsgBox "selam

DoEvents: MSDN açıklaması çok yüzeysel malesef, ben yine de bu açıklamayı vereceğim, sonra nerelerde kullanıldığını söyler ve birkaç örnek gösterirsem daha anlaşılır olur.

MSDN açıklaması: Program akışını işletim sistemine verir.

Şimdi MSDN'nin açıklamasını biraz daha açalım. Bir Windows uygulamasında aynı anda onlarca program çalışabilmektedir. Sizin Excel VBA kodunuz işlemciyi çok fazla bloke ederse Windows buna sinirlenebilir, hatta bakar ki Excelden ses seda yok, bunun kitlendiğini düşünüp kapatmaya çalışabilir, çünkü belleksiz kalan diğer gariban programlar çalışsın diye düşünür. İşte böyle durumlarda araya bi DoEvents sokmak gerekebilir, ki Windows Excel'in yaşadığını düşünsün. Peki genel olarak nerelerde kullanırız ona bakalım:

  • Büyük döngülerde araya girebilmek için(özellikle kitlenme durumları yaşıyorsanız)
  • ScreenUpdating=False olduğunda kullanıcı Excelin kilitlendiğini sanmasın diye ekran tazelemede
  • Bir şartın olmasını beklerkenen

      Do Until beklenen=true
         DoEvents
      Loop

Bu yukardaki ilk iki maddeyi içeren bir örnek verelim. Uzun bir döngüsel işleminiz var diyelim, hızlı çalışsın diye ScreenUpdating=False yaptınız. Kullanıcı program kitlendi sanmasın diye, DoEvents metodu Statusbarı bir nevi Progressbar gibi kullanmamızı sağlayacak. Tabi bu aşağıdaki örnekte %1den %100e giden bir progressbar yapmış olduk ancak siz isterseniz başka ölçüler kullanabilrisiniz. Mesela 20 bölgelli bir bankada her bölgenin işi 1 dk sürüyorsa, her döngü sonuna "20 bölgede " & i & " adedinde işlem tamam" gibi bir metin yazdırabilirsiniz. 

Sub doeventprogressbar()
	Dim i As Long
	Dim bas As Double
	bas = Timer
	Application.ScreenUpdating = False
	For i = 1 To 100000 '100.000in 100lük dilimlere böldüğümüzde her bir bölümün büyüklüğü
		Cells(1, 1) = i
		If i Mod 1000 = 0 Then
			DoEvents
			Application.StatusBar = "%" & i * 100 / 100000
		End If
	Next i
	Application.ScreenUpdating = True
	MsgBox Round(Timer - bas) & " sn sürdü"
End Sub

Bu arada bu koddan screenupdatingi çıkarın, programın ne kadar yavaşladığını göreceksiniz.

Dosya ve Klasör işlemleri

Bazen kullanıcıdan, üzerinde işlem yapılacak bir dosya veya klasör seçmesini isteriz. Bazı durumlarda seçilen dosya ile sadece işlem yapılırken bazen dosyanın açılması sağlanır.

Bu bölümde anlatılan konular genel olarak, seçilen dosyayı açma veya bir şekilde dosya/klasör ismi elde etme amacıyla kullanılan işlemlerle ilgilidir. Daha genel olarak tüm dosya işlemlerini şurada ele alıyor olacağız.

Kullanıcıdan dosya/klasör bilgisi istemenin en ilkel yolu bunu bir inputboxla sormak olacaktır, ancak şükür ki VBA'de bunu yapmamızı sağlayan daha iyi yöntemler var. Şimdi bunlara bakalım:

Application.FileDialog özelliği: 2002 yılında gelen bu özellik bundan daha önce varolan GetOpenFilename ve GetSaveAsFileName özelliklerinin gelişmiş halidir. O yüzden bu ikisinin artık çok kullanmaya gerek yok, ama başka kodlarda görmeniz durumunda ne olduğunu bilmeniz için onlara da kısaca değineceğiz. Bu özelliğin FileDialogType şeklinde tek bir parametresi vardır ve o da MsoFileDialogType sabitlerinden biri olabilir. Bunlar:

  • msoFileDialogFilePicker: Dosya seçtirir, path dahil tam ismini döndürür(Ör:"C:\Hedefler\Satış\2016.xlsx"
  • msoFileDialogFolderPicker: Klasör seçtirir, path dahil tam ismini döndürür(Ör:"C:\Hedefler\Satış"
  • msoFileDialogOpen: Açılacak dosyayı seçtirir(onu açmaz, sadece seçtirir)
  • msoFileDialogSaveAs:Farklı Kaydet dialog kutusunu açar, dosyayı kayderken ismin ne olacağını girmenizi sağlar(dosyayı kaydetmez,sadece isim ve adres belirlersiniz)

Bu property ile FileDialog nesnesi elde edilir. Bu nesnenin de kendi metod ve özellilikler vardır. Genel olarak önce bu tipte bir değişken yaratmak ve ona atama yapmak intellisense açısından uygun olacaktır.

Bu nesnenin iki metodu var, pratikte en çok kullanacağınız metodu Show metodudur. Bir seçim yapıldıysa -1(True) döndürür, seçim yapılmadan işlem iptal edilirse 0(False) döndürür. Bu sayfada gördüğümüz gibi, bu metod arka planda bir function prosedür olarak hazırlanmıştır, çünkü bize bir değer döndürüyor.

Seçim sonucu True ise Execute metodunu yazarak duruma göre uygun işlemi de yaptırabilrisiniz. Ancak bunun yerine Workbook.Open veya Workbook.SaveAs gibi metodlar da kullanılabilir. Execute yazmak basit görünse de arka planda seçim tipi ve sonucunu karşılaştıran bir koşullu yapı barındırdığı için perfomans sorununa neden olabilir, özellikle büyük kodlarda. O yüzden doğrudan Workbook metodlarını kullanmanızı tavsiye ederim.

Önemli propertyler ise şöyledir.

  • AllowMultiSelect: Çoklu seçim yaptırma imkanı verir, sadece dosyalar için geçerlidir, klasörlerde çoklu seçim yapılamaz.
  • Title:Dialog kutusunun başlığını değiştirebilirsiniz.
  • InitialFileName:Seçim yaptırırken sık kullanılan bir dosya/klasör varsa default olarak bunu seçtirebilirsiniz, SaveAs yaparken de yine aynı mantıkla default bir adres ve isim belirleyebilirsiniz.
  • Filters:Hangi tür dosyaların gösterileceğini belirlersiniz.
  • SelectedItems:Seçilen dosyaların/klasörlerin tam adresini verir. Tek seçim yapıldıysa SelectedItems(1) şeklinde kullanılır.

Şimdi ilk olarak dosya seçme örnekleriyle başlayalım, sonra da diğerlerine geçelim.

Aşağıdaki örnekte dosya seçtiriyorum ve seçilen dosyaları siliyorum. Bu arada With .. End With yapısını nasıl kullandığımıa dikkat edin. Bilgi almak için buraya tıklayın.

Sub Dosyaislemlerim()

Dim i As Byte
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
  .AllowMultiSelect = True
  .Title = "Silinecek dosyaları seçin"
  .Show
  
   For i = 1 To .SelectedItems.Count
      Kill .SelectedItems(i) 'buradaki kill metodunu sonra göreceğiz, şimdilik sadece sdosya silmeye yaradığını bilin
   Next
End With

End Sub

Dialog kutusunu seçim yapmadan kapadığımızda bunu anlayan bir kontrol noktası koyalım. Bu kontrol noktasını doğrudan metodun sonuç değeri ile yapıyoruz.

Sub Dosyaislemlerim()

Dim i As Byte
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .AllowMultiSelect = True
    .Title = "Silinecek dosyaları seçin"
    If .Show = 0 Then
        MsgBox "Seçim yapmadan çıkış yaptınız"
        Exit Sub
    End If
               
    'iptal edilirse buraya gelmeden program sonlanır, çünkü Exit Sub denildi
    For i = 1 To .SelectedItems.Count
        Kill .SelectedItems(i)
    Next
End With
 
End Sub		

Şimdi de diğer 3 tip için de örnekler yapalım. Önce dosya aç:

Sub Dosyaislemlerim()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
    '.AllowMultiSelect = True
    .Title = "Açılacak dosyayı seçin"
    .InitialFileName = "C:\deneme.xlsx"
     If .Show = True Then
        .Execute 'veya Workbooks.Open (.SelectedItems(1))
     End If
End With

End Sub

Şimdi default dosya adı belirtmeyelim, kullanıcı seçsin ama çok kalabalık dosya türünü barındıran bir görüntü de olmasın, sadece excel dosyaları olsun.

Sub Dosyaislemlerim()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
   '.AllowMultiSelect = True
   .Title = "Açılacack dosyayı seçin"
   '.InitialFileName = "C:\inetpub\wwwroot\ee\yuklemeler\pivot - data.xlsx"
   .Filters.Clear 'varsayılan olakra 20 tane dosya tipi var, bunları temizlzeyelim
   .Filters.Add "Excel dosyaları", "*.xls*"
   .Filters.Add "Tüm dosyalar", "*.*" 
   If .Show = True Then
     .Execute 'veya Workbooks.Open (.SelectedItems(1))
    End If
End With

End Sub

Eğer farkettyiseniz msoFileDialogFilePicker ve msoFileDialogOpen ifadelerinin her ikisini de dosya açmada kullanabiliriz. Hatta dosya silme işlemi için bile msoFileDialogOpen kulanılabilir. Yukarıdaki msoFileDialogFilePicker ile yapılmış dosya silme örneğinde kendiniz deneyip görebilirsiniz. O halde neden iki ayrı ifadeye gerek var diye düşünüyor olabilirsiniz. Buna malesef benim de cevabım yok. O kadar araştırdım ancak bir açıklama bulamadım. Bulduğum zaman bu paragrafı güncellerim.

Şimdi de folder işlemlerine bakalım

Sub folderislemleri()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
   .AllowMultiSelect = True 'buna True dense bile çoklu seçim yaptırmaz
   .Title = "klasör sçein"
    If .Show = True Then
       MsgBox .SelectedItems(1) & " klasörünü seçtniz"
    End If
End With
End Sub

Son olarak da SaveAs işlemi yapalım.

Sub Dosyaislemlerim()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)

With fd
   .InitialFileName = "C:\deneme.xlsx"
   If .Show = True Then
     .Execute 'veya Workbooks.Saveas ile
   End If
End With

End Sub

Application.GetOpenFilename ve Application.GetSaveAsFilename: Yukarıda bahsettiğim gibi yeni kodlarınızda bu iki metod yerine FileDialog yöntemini kullanmanızı tavsyie ederim. Bunları sadece geriye dönük uyumluluk adına ve karşınıza bunları içeren bir kod geldiğinide anlamanız için bilmeniz gerektiğini düşünüyorum.

Syntax: GetOpenFilename( GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

Bunda FileDialogda olduğu gibi bir nesne yaratmaya gerek yok, doğrudan kullanılabilir. Bundan dönen değer üç şey olabilr. Seçim yapılmadıysa False(Boolean) veya tek bir dosya seçim yapıldıysa dosyanın tam adını(path dahil) veren bir string ya da çoklu seçim yapıldıysa bir dizi. O yüzden dönüş tipi varianttır ve tanımlanırken de böyle tanımlanmalıdır.

Yine FileDialogda olduğu gibi burda da doğrudan dosya açma veya kaydetme yok sadece dosya ismi elde edilir, sonrasında ayrı bir satırda dosya açma işlemi yapılır.

Sub getopenfilenameornek()
 
Dim filtreler As String
Dim başlık As String
Dim dosya As Variant
 
    filtreler = "Excel dosysaları(*.xls*),*.xls*, Tüm dosylar (*.*),*.*"
    başlık = "Açılacak dosyayı seçin"
    dosya = Application.GetOpenFilename(filtreler, 5, başlık, , True)
 
    If IsArray(dosya) Then 'multi parametresini true belirlediğimiz içn öncelikle dizi olup olmadıını kontrol etmemiz lazım
        For Each a In dosya
            Workbooks.Open (a)
        Next a
    Else
        If dosya = False Then
            MsgBox "seçim yapılmadı"
        Else
            Workbooks.Open dosya
        End If
    End If

End Sub

SaveAsFileName de bunun aynısı olup sadece save etmeye yarıyor.

FindFile: Bu da,  GetopenFileName ve FileDialog gibi yine Open File dialog kutusunu getirir ancak geriye bir dosya adı döndürmez. Eğer seçim yapıldıysa dosyayı açar ve True değerini döndürür, seçim olmadıysa False döndürür. Bunu diğerleriyle bir anlam bütünlüğü var diye buraya aldım ancak açıkçası pratikte nasıl bir kullanımı olur bilmiyorum, şahsen ben şimdiye kadar hiç kullanmadım.

Diğer üyeler

Application.ActivateMicrosoftApp metodu: Bu metod, başka bir MS Office uygulamasını açar. Eğer halihazırda ilgili uygulama açıksa onu aktive eder yoksa yenisini yaratır ve açar. Ancak bunu bu şekilde doğrudan kullanmak yerine ilgili Office uygulamasını obje olarak yaratıp onun Nesne modeline ulaşmak istemeniz durumunda ise(ki daha çok bu yöntemi kullanacaksınız) farklı bir yöntem kullanılır. Bunu da bu buradan görebilirsiniz. Bu metoda dönecek olursak aşağıdaki örnek kodda Word uygulaması açılmakta.

 Application.ActivateMicrosoftApp xlMicrosoftWord
Application.Inputbox metodu: Bunu burada inceledik.

Application.OnKey metodu: Excel, bir makroyu kaydederken bize bunu bir kısayol tuşuna atayıp atamayacağımız konusunda bir imkan sunar, ancak bunun bir sınırı vardır ki o da sadece Ctrl tuşunu kullanmak zorunda olmamızdır ve bu da bir noktadan sonra yetersiz kalmaya başlıyor. İşte bu noktada OnKey metodu yardıma koşuyor, bununla istediğiniz tuş kombinasyonlarına atama yapabiliyorsunuz ve bu tuşlara bastığınızda bir olay tirgger olmuş gibi istediğiniz makro çalışmaya başlıyor.

Bu tuş kombinasyonu, sadece mevcut Excel oturumunda geçerli olmaktadır. Excel, kapatılıp tekrar açıldıktan sonra kullanılamazlar. Süreklilik kazandırmak için bunları Personal.xlsb dosyanızın Workbook_Open makrosuna yazabilirsiniz.

Ör:

Private Sub Workbook_Open()
  Application.CalculationInterruptKey = xlEscKey
  Application.OnKey "+^{F}", "Calculationlar"
End Sub	

OnKey metodunu, Çeşitli Windows veya Office kısayol tuşlarını kontrol etmek için de kullanabilirsiniz. Mesela Cut/Copy işlemlerini engellemek için kullanabilirsiniz. Bunu da yine Workbook eventleri ile birlikte kullanmak gerekiyor. Bununla ilgili örnek biraz daha uzun olduğu için onu Workbook Eventleri sayfasına aldım.

Application.Version:Bu özellikle Excelin versiyonunu öğreniyoruz. Böylece kullanıcının Excel versiyonuna göre davranışımızı değiştirebiliyoruz. Mesela 2010 versiyonu ile birlikte gelen Slicer'larla ilgili bir işlemi 2007 ve öncesi kişilerde yapmaya çalışırsak hata alırız. Keza, Slicerlar 2010'da geldi ama sadece Özet tablolarda kullanılmak üzere gelmişti. Table'lar üzerinde kullanımı 2013 versiyonuyla geldi. Bu yüzden bir Table üzerinde Slicer kullanımı olacaksa yine hata alınır.

Şimdi Excelin konuşması özelliğini kullanan başka bir örnek düşünelim. Bu özellik 2013 versiyonuyla geldiği için versiyon numarası 15.0'dır. Versiyon numaralarına buradan ulaşabilirsiniz. Diyelim ki, ortak kullanım için bir Add-in yaptınız ve bu addinde Kokpit adında tüm raporlara ulaşmayı sağlayan bir UserForm var. Kullanıcıların raporlara Kokpit üzerinden ulaşmasını istiyorsunuz, çünkü buradan ulaştıklarında raporlar Readonly açılıyor. Böylece siz raporlarda bir düzenleme yapmak istediğinizde kimsede açık bulunmamış oluyor ve düzenlemelerinizi rahatlıkla yapabiliyorsunuz. Ancak bazı yaramaz arkadaşlar dosyalara ortak alandan ulaşmaya çalışabilir. İşte onlar için ThisWorkbook modülünün Workbook_Open makrosuna aşağıdaki kodu yazabilirsiniz.

Private Sub Workbook_Open()
    'diğer kodlar(Kullanıcının siz olması durumunda aşağıdaki kodun çalışmamasını sağlayacak kodlar dahil, şimdilik kafa karıştırmasın diye bunları atladım)
    If Not Me.ReadOnly Then
        If Val(Application.Version) >= 15 Then
            Application.Speech.Speak ("Hey. Bana ortaka alandan değil, Kokpit formu üzerinden gir") 'Konuşarak iletişim
        Else
            MsgBox "Hey. Bana ortaka alandan değil, Kokpit formu üzerinden gir" 'MsgBox ile iletişim
        End If
        Logger "Bilgi", 0, "Ortak alandan girmeye calisma"
        Me.Close savechanges:=False
    End If

End Sub

Application.OnTime ile Scheduling işlemleri

Aşağıdaki kodlar benim günde yaklaşık 40 raporun kendiliğinden çalışmasını, refresh olmasını ve yeni günün tarihiyle kaydedilmesini, gerekiyorsa ilgili alıcılara maille iletimini sağlar.

Ön bilgiler:

  • İki bilgisayarla çalışıyorum. Biri, sadece birkaç makronun çalıştığı PC'm, biri de schedule edilmiş işlerin ağırlıklı olarak çalıştığı laptop.
  • Kodların bazısı her iki makinada da çalışıyor, bazısı sadece PC'de, bazısı da sadece laptopta
  • PC'de çalışanlar genelde mesai saati dışında çalışıp bitiyor. Gün içinde çalışanlar da hep laptopta çalışıyor.
  • Diyelim ki sicilim 12345. PC ismim B12345, laptop ismim L12345
  • Raporların bir kısmı Veri Ambarı(kısaca DWH) tablolarına SQL atıyor, bazısı DWH'tan türeyen datamartlara, bazıları da online sistemlere. Online olanlar için herhangi bir "tablo dolmuş mu" kontrolü yapmıyorken, DWH ve ondan türeyen datamartlar için dünün datası dolmuş mu diye kontrol yapıyorum
  • İlk kod bloğunda kapat_on_laptop diye bir modül var. Bunun hikayesi şu. Her akşam laptopta Exceli otomatik kapattırıyorum, ki güncel Personal.xlsb ve Schedule.xlsb dosyaları laptopa yedeklenebilsin. Böyelce PC'mde shcedule ayarlarında bir değişiklik yaptığımıda veya Personal.xlsb dosyama yeni bir proesedür eklediğimde laptopa da aynen geçmesini sağlıyorum ki işlerde aksama olmasın. Bunun için PC'de bu iki dosyayı ne zaman kaydetsem, bunların "Workbook_AfterSave" eventi devreye giriyor ve dosyaların güncel halini ortak alana da kaydediyor. Laptopta da Windows Task Scheduler ile her geceyarısı önce yedeklemeyi yaptırıyorum, sonra Excel'i tekrar açtırıyorum
  • Hem PC'de hem laptop'ta Personal.xlsb ve Schedule.xlsb dosyam XLSTART klasöründe yer alıyor, böylece Excel açılır açılmaz onlar da gizli olarak açılıyorlar

Aşağıdaki vereceğim tüm prosedürler Schedule.xlsb dosyamda bulunmaktadır. Şimdi, ilk olarak ilk olarak Workbook_Open prosedrüne bakalım.

Private Sub Workbook_Open()

   Call StartTimer 'açılır açılmaz StartTimer makrosunu çalıştırsın istiyorum, hem PCde hem laptopta

'Laptopta kitlenme v.s nedeniyle Exceli sonradan tekrar açma durumu olursa diye hepsini ilk açılışta schedula alıyorum. PC'de buna gerek yok, zira burdaki raporlar sabah gitmedikten sonra bi anlamı yok, o yüzden 8:30dan sonra açtıgımda gereksiz PCmi meşgul etmesin istiyorum

If Environ$("computername") = "B12345" Then
    If TimeValue(Now) < TimeValue("08:30:00") Then
        Application.OnTime Now + TimeValue("00:01:00"), Procedure:="dwhkontrol"
    End If
Else 'yani Laptopsa
    Application.OnTime Now + TimeValue("00:01:00"), Procedure:="dwhkontrol" 'saat kontrolüne gerek yok, her halükarda 6:30dan sonra açmış olcam, işe sabahın köründe gelmiyorum sonuçta :)
    
	If TimeValue(Now) > TimeValue("08:01:00") Then
	    Application.OnTime Now + TimeValue("00:02:00"), Procedure:="pyskontrol"
	End If
	
	If TimeValue(Now) > TimeValue("08:11:00") Then
	    Application.OnTime Now + TimeValue("00:02:30"), Procedure:="nrkontrol"
	End If
	
	If TimeValue(Now) > TimeValue("08:21:00") Then
	    Application.OnTime Now + TimeValue("00:03:00"), Procedure:="pdmkontrol"
	End If
	
	If TimeValue(Now) > TimeValue("08:31:00") Then
	    Application.OnTime Now + TimeValue("00:04:00"), Procedure:="pargkontrol"
	End If
    
End If

Şimdi de StartTimer prosedürünü inceleyelim. Bu, schedule işlemlerini ele aldığım ana prosedürdür.


Sub StartTimer()

'hem pcde hem laptopta 24 saat sonrasına tekrar schedule ediyorum
Application.OnTime Now + TimeSerial(24, 0, 0), Procedure:="StartTimer"

'Sadece PC
If Environ$("computername") = "B12345" Then
    Application.OnTime TimeValue("00:10:00"), Procedure:="KronosVBA" 'bunda kontrollük bir durum yok, data çekilmiyor, detayı önemsiz
    Application.OnTime TimeValue("00:20:00"), Procedure:="accessleri_compact" 'kontrollük bir durum yok, data çekilmiyor, detayı önemsiz
    Application.OnTime TimeValue("00:40:00"), Procedure:="gunicisatisyedekleveeskilerisil" 'kontrollük bir durum yok, data çekilmiyor
    Application.OnTime TimeValue("01:00:00"), Procedure:="datayukleme_bosalt" 'kontrollük bir durum yok, data çekilmiyor, outlooktaki datayukleme klasörünü boşaltıyoruz
    Application.OnTime TimeValue("04:00:00"), Procedure:="ihtiyacfaiz" 'Online sistemden geldiği için kontrole tabi tutmuyoruz
   '********kontol sorguları başlıyor
    Application.OnTime TimeValue("06:00:00"), Procedure:="dwhkontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    Application.OnTime TimeValue("08:20:00"), Procedure:="pdmkontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    Application.OnTime TimeValue("23:50:00"), Procedure:="portfoyliste" 'Online sistemden geldiği için kontrole tabi tutmuyoruz
    Application.OnTime TimeValue("23:55:00"), Procedure:="vadesizgünsonu" 'Online sistemden geldiği için kontrole tabi tutmuyoruz
Else 'Laptop
    Application.OnTime TimeValue("23:55:00"), Procedure:="kapat_on_laptop" 
    Application.OnTime TimeValue("05:50:00"), Procedure:="güniçi_kredinin_yardımcıdatası" 'Online sistemden geldiği için kontrole tabi tutmuyoruz
    '********kontol sorguları başlıyor *************
    Application.OnTime TimeValue("06:01:00"), Procedure:="dwhkontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    Application.OnTime TimeValue("08:01:00"), Procedure:="pyskontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    Application.OnTime TimeValue("08:11:00"), Procedure:="nrkontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    Application.OnTime TimeValue("08:21:00"), Procedure:="pdmkontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    Application.OnTime TimeValue("09:31:00"), Procedure:="pargkontrol" 'ilk burada başlıyorum, sonra kendi modülü içinde 10dk artırıyorum
    
    Application.OnTime TimeValue("17:10:00"), Procedure:="pkuyar", LatestTime:=TimeValue("18:00:00") 'Online sistemden geldiği için kontrole tabi tutmuyoruz. 
    'Application.OnTime TimeValue("23:30:00"), Procedure:="güniçivadeli" 'Online sistemden geldiği için kontrole tabi tutmuyoruz. 
   
    'PAZAR
    If Weekday(Date, vbSunday) = 1 Then
        'Application.OnTime TimeValue("10:45:00"), Procedure:="segmentdegisim"
        Application.OnTime TimeValue("17:45:00"), Procedure:="tmv_200K_degisim"
    End If
    
End If
        
End Sub

'----------------------------------------------------------------------------------------------------------------

Sub kapat_on_laptop()
On Error Resume Next
    Workbooks("Book1").Close savechanges:=False
    Application.Quit
End Sub

Şimdi de Tablo yükleme modülündeki prosedürlere bakalım. Burada da kalabalık olmaması adına tüm kontrol prosedürlerini koymadım, onun yerine iki farklı yöntemi göstermesi adına iki prosedürü verdim. İlkinde , BT ekiplerinden gelen "Tablo doldu" tarzındaki mailin varlığını kontrol ediyorum. İkincisinde ise farklı dinamiğe sahip olduğundan "Tablo doldu" maili gelmeyen bir datamart için bir tabloya SQL atıp kayıt dönüp dönmediğine bakıyorum.

'1.yöntem
Sub dwhkontrol()
Const raporad As String = "dwhkontrol"
Dim outapp As Object
Dim objNS As Object
Dim objFolder As Object
Const adres As String = "\0yuklemekontrol\dwh\dwhyukleme - "

On Error Resume Next
'kontrol dosyası olşutuysa dwhcılar prosedürünü çağırıp oradaki raporları çalıştırttırıyorum
'gunlukyol, Public bir constant olup ana modülümün tepesinde tanımlanmıştır
If Application.Run("PERSONAL.xlsb!FileFolderExists", gunlukyol & adres & Date - 1 & " Sonuçları.txt") Then 
'Bu prosedürle dosyanın varlığını kontrol ediyorum. Bunun amacı, bazen Excel kitleniyor veya makinaya birşeyler oluyor, kapatıp açmam gerekiyor. ama sabahtan kontroller zaten yapılmış olabiliyor. işte aynı kontrolleri bidaha yapmasın diye dosyanın oluşup oluşmadığna bakıyorum
    Call dwhcılar
    Exit Sub
End If

'Burada önce Outlook açıkmı diye bakıyoruz, açık değilse outlook nesnesi yaratıyoruz. Outlook programlama kısmını Diğer Uygulamalarla erişşim bölümünde ayrıca inceleyeceğiz.
Set outapp = GetObject(, "Outlook.Application")

If Err.Number <> 0 Then 
    Set outapp = CreateObject("Outlook.Application")
End If

On Error GoTo hata

Set objNS = outapp.GetNamespace("MAPI") 
Set objFolder = objNS.GetFolderFromID("00000000C7...85E26C790000") 'bu uzun id, datayukleme klasörüne ait, BT'den gelen mailler bu klasöre düşüyor. aslında daha uzundu ama ben kısalttım biraz
       
'tüm mailleri tarayıp mailin varlığını kontrol ediyoruz
   For Each msg In objFolder.Items
         If msg.Subject = "DWH (Production) - Bilgi Mesaji - VERİ AMBARI Yüklemesi Tamamlanmıştır." And DateValue(msg.ReceivedTime) = Date Then
            Set dosya = CreateObject("Scripting.FileSystemObject")
            dosya.CreateTextFile (gunlukyol & adres & Date - 1 & " Sonuçları.txt") 
'tablo dolduysa bi dosya oluşturuyorum ve rapor prosedüürleri bu dosyanını varlığını kontrol ediyor
            rapor = "dwh kontrol"
            alici = "12345"
            Call Mailat2(rapor, alici) 'tablo dolduysa kendime mail de attırıyorum
            Call dwhcılar 
            Logger WorksheetFunction.Rept(" ", 50 - Len(raporad)) & raporad, "OK", 0, "Rapor başarıyla çalıştı" 
'bu kısmı logcu uygulamasında ayrıca göreceğiz. özetle kendime mail atmakla kalmayıp logunu da tutuyorum
            Exit Sub 'diğer mailitemlara bakmaya gerek yok
         End If
   Next
   
'maili bualmadıysa 10 dk sonraya programla tekrar
Application.OnTime Now + TimeSerial(0, 10, 0), Procedure:="dwhkontrol"
Set objFolder = Nothing
Set objNS = Nothing
Exit Sub

hata:
Set objFolder = Nothing
Set objNS = Nothing
Logger WorksheetFunction.Rept(" ", 50 - Len(raporad)) & raporad, "Hata", Err.Number, Err.Description
Application.Run "PERSONAL.xlsb!AlertUpdatingEvent", True, True

End Sub

'----------------------------------------------------------------
'2.yöntem
Sub pdmkontrol()
Const raporad As String = "pdmkontrol"
Const adres As String = "\0yuklemekontrol\pdm\pdmyukleme - "

On Error GoTo hata
If Application.Run("PERSONAL.xlsb!FileFolderExists", gunlukyol & adres & Date - 1 & " Sonuçları.xlsm") Then
    Call pdmciler
    Exit Sub
End If

Application.Run "PERSONAL.xlsb!AlertUpdatingEvent", False, False

'eğer dosyayı önceki runlarda oluştuysa mail atmadan çık, yoksa çalıştır
If Application.Run("PERSONAL.xlsb!FileFolderExists", gunlukyol & adres & Date - 1 & " Sonuçları.xlsm") Then Exit Sub 

Workbooks.Open Filename:= _
    gunlukyol & adres & "Format.xlsm"
    
'dosya save olduysa mailat yoksa atma
If Not Application.Run("PERSONAL.xlsb!FileFolderExists", gunlukyol & adres & Date - 1 & " Sonuçları.xlsm") Then
    ActiveWorkbook.Close savechanges:=False
    Application.Run "PERSONAL.xlsb!AlertUpdatingEvent", True, True
'********tekrar schedule**********
    Application.OnTime Now + TimeSerial(0, 10, 0), Procedure:="pdmkontrol"
    Exit Sub
Else
    ActiveWorkbook.Close
    Kill "C:\geçici\geçici2.xlsm"

    rapor = "pdm kontrol"
    alici = "12345"
    Call Mailat2(rapor, alici)
    Application.DisplayAlerts = True
    Call pdmciler
    Logger WorksheetFunction.Rept(" ", 50 - Len(raporad)) & raporad, "OK", 0, "Rapor başarıyla çalıştı"
    Exit Sub
End If

hata:
Logger WorksheetFunction.Rept(" ", 50 - Len(raporad)) & raporad, "Hata", Err.Number, Err.Description
Application.Run "PERSONAL.xlsb!AlertUpdatingEvent", True, True

End Sub

Şimdi de dwhcılar prosedürne bakalım. Burada DWH'tan çalışan raporlar çalışma sırasına sokuluyor.

Sub dwhcılar()
If Environ$("computername") = "B12345" Then
    Application.OnTime Now + TimeSerial(0, 20, 0), Procedure:="yeni10250K"
    Application.OnTime Now + TimeSerial(0, 4, 0), Procedure:="dusenartansube_vdltl"
    Application.OnTime Now + TimeSerial(0, 5, 0), Procedure:="dusenartanmbb_vdltl"
    Application.OnTime Now + TimeSerial(0, 6, 0), Procedure:="dusenartansube_vdsztl"
    Application.OnTime Now + TimeSerial(0, 7, 0), Procedure:="dusenartanmbb_vdsztl"
Else 'laptopsa
    Application.OnTime Now + TimeSerial(0, 1, 0), Procedure:="güniçi_kredinin_yardımcıdatası" 'oraclelar
    Application.OnTime Now + TimeSerial(0, 10, 0), Procedure:="güniçi_kredideki_yardımcı_pivotları_güncelle"
    Application.OnTime Now + TimeSerial(0, 12, 0), Procedure:="mevduatdüsüs"
    Application.OnTime Now + TimeSerial(0, 15, 0), Procedure:="musteri_degisim" '8,5tan önce çalışıyorsa açılır    
End If
End Sub

'Diğer datamartlara ait prosedürleri kalabalık yaratmaması adına koymadım

Son olarak örnek bir rapor prosedürüne bakalım.


Sub dusenartansube_vdltl()
   Const raporad As String = "DusenArtanVadeliSb"
   
   If Not Application.Run("PERSONAL.xlsb!FileFolderExists", gunlukyol & "\0yuklemekontrol\dwh\dwhyukleme - " & Date - 1 & " Sonuçları.txt") Then Exit Sub
   

   'eğer dosyayı önceki runlarda oluştuysa mail atmadan çık, yoksa çalıştır
    If Application.Run("PERSONAL.xlsb!FileFolderExists", gunlukyol + "\Düşen&artanlar\Vadeli TL Günlük en çok düşenartan şubeler - " & Date - 1 & " Sonuçları.xlsm") Then Exit Sub

    On Error GoTo çıkış
    Application.Run "PERSONAL.xlsb!AlertUpdatingEvent", False, False

    Workbooks.Open Filename:= _
        gunlukyol + "\Düşen&artanlar\Vadeli TL Günlük en çok düşenartan şubeler - Format.xlsm" 'tüm scheduled raporlarım - 'Format.xlsm' uzantılıdır
    'diğer kodlar

End Sub

Scheduled program akışını kesintiye uğratan unusurlar

Bazen schedule edilmiş dosyalarınızı güniçinde de açmanız ve kullanmanız gerekebilir. O yüzden belli saatlerde açıldığında faklı bir davranış göstermesini isteyebilirsiniz. Keza dosyayı açan sizseniz başka, diğer kişilerse başka şekilde davranmasını isteyebilirsiniz.

Bu davranış şekillerinden biri de kullanıcıya bir mesaj göstermek olabilir. Eğer programın bir yerinde mesaj verirseniz, bu mesaj kutusundaki bir düğmeye basılana kadar ekranda kalır, bu da takip eden schedule kodlarınızın çalışmasını engellemiş olur. O yüzden MsgBox'lı uyarılarınızı Schedule zamanı içinde size kesinlikle çıkarmamalı, sadece diğer kişilere çıkarmalıdır. Gerekiyorsa kendinize bir maille bilgilendirme yaptırabilir veya Statusbara mesaj yazdırabilir veya logcu prosedürüyle kayıt altına alabilirsiniz.

Benim kullandığım yöntem şudur. Kullanıcılara UserForm kullanarak Kokpit diye bir arayüz oluşturdum ve bunu onlara verdiğim Add-in'deki menüye yerleştirdim. Kullanıcılar ilgili dosyayı kokpitten açtıklarında, dosyanın readonly açılmasını sağlıyorum. Normalde herkes hep dünün raporunu açmak ister , o yüzden kokopitten de sadece dünün raporlarını açabiliyorlar. Daha eski raporları açmak için ortak alana gidip klasörden manuel açmaları gerekiyor. Şimdi öyle bir kod yazmam gerekiyor ki, dünkü raporu ortak alandan açamasınlar, zira bir rapor gün içinde iki defa farklı zamanlarda refresh ediliyor; aynı zamanda ana format dosyamın da açılmaması lazım, zira onu açarlarsa schedule makrom takılmaktadır. O yüzden dünün dosyasını veya Format dosyasını klasörden manuel açmaya çalışırlarsa uyarı verdiriyorum. Şimdi aşağıdaki kodu inceleyelim.

Private Sub Workbook_Open()
On Error GoTo hata

If Environ("UserName") <> 12345 Then 'Dosya benim dışında biri tarafından açıldıysa
    If Right(Me.Name, 11) = "Format.xlsm" Or (Right(Me.Name, 25) = Date - 1 & " Sonuçları.xlsm" And Not Me.ReadOnly) Then
        MsgBox "Dosyayı ortak alandan değil, Kokpitten açın."
        Me.Close savechanges:=False
    End If
End If

If Environ("computername") <> "L12345" Then Exit Sub 'dosyayı bazen PC'mde açıp SQL'inde veya genel sayfa düzeninde güncelleme yapmam gerekiyor. Açılır açılmaz da refresh olmasın diye laptop dışında açılırsa yani PC'mde açılırsa kalan kodlar çalışmasın istiyorum
'Diğer kodlar

End Sub

Bir diğer önemli husus da, programınızda son derece ince ayarlanmış bir hata yönetimi mekanizması olması gerekir. Detaylar için hata yakalama bölmünü iyice özümsemiş olmanızı tavise ederim. Schedule edilmiş programlarda hataları hata mesajı olark vermek yerine bir logcu prosedürüyle bir dosyaya yazdırmayı tercih edebilirsiniz.

YORUMLAR