Excel içerisinde hazırlanmış olan makrolar bazı durumlarda bizim için ciddi riskler taşıyabilir. Öncelikle bu amaçla güvenlik seviyesi kontrolleri çok önemlidir. Eğer güvenlik seviyeniz makro içeren bir excel dosyasının içerisindeki kodların etkinleştirilmesinde sizin onayınızı almadan işlem yapabilen düşük durumunda ise böyle bir dosya ile sizin bilgisayarınızda bu makro kodları ile bir çok gizli bilgiye ulaşmak, bunları mail ile almak, silmek, değişiklik yapmak vb. mümkündür.

Bu sebeple güvenlik seviyenizi mutlaka kontrol etmenizi tavsiye ediyorum. Her ihtimale karşın dosyanın güvenli olduğu durumlarda bile bazen işlemi durdurmak, özellikle kodlamada yer alan sorunlardan dolayı oluşan sonsuz döngülerden kurtulmak için makroyu durdurmanız gerekecektir.

Excel içerisinde çalışan bir makroyu durdurmak için Ctrl + Alt + Pause(Break) tuşlarına birlikte basmanız gerekmektedir.

Süleyman TOSUN, PMP

Excel dosyalarında, verilerinizin değiştirilmemesi amacıyla protect özelliğini kullanabilirsiniz. Genel olarak kullanılan özellik bu olmasına rağmen daha kesin bir önlemi bu yazıda sizlere aktarıyoruz.

Excel dosyanızın başkaları tarafından açılmasını önlemek veya açılması halinde herhangi bir şekilde farklılık oluşturulmasını önlemek için neler yapılması gerektiğini adım adım inceleyelim.

1. Öncelikle dosyanızı Save As ile kaydetmek için ilerliyorsunuz.

2. Save As komutu ile açılan pencerenin en altında sol tarafta yer alan Tools kısmında General Options bölümünü kullanıyoruz.

3. General Options ile birlikte karşınıza dosyanın açılması ve dosyanın değiştirilebilmesi ile ilgili şifreleme alanları çıkacaktır, bu bölümde ayrıca dosyanızda bir değişiklik olduğunda bir önceki halinin de back-up olarak kaydedilmesi ile ilgili bir imkan sağlanmıştır.

Süleyman TOSUN, PMP

Excel içerisinde daha önceden kayıtlı olan herhangi bir makroyu çalıştırmak için temel olarak 3 farklı yöntem izlenebilir.

1.Makronun developer menüsü ile çalıştırılması
2.Makronun kısayol atamaları ile çalıştırılması
3.Makronun düğme (button) aracılığı ile çalıştırılması

Birinci yöntemle çalışmak için öncelikle Excel üzerinde Developer sekmesini açmak gerekecektir. Sonrasında bu bölümde yer alan Macros seçeneği altından kayıtlı makroların yer aldığı bölüm açılır. Böylece istediğiniz makroyu seçerek Run düğmesine tıklayarak çalıştırabilirsiniz.

İkinci yöntem ile makronun çalıştırılması için makronun kayıt esnasında bir kısayol tuşu ile tanımlanmış olması gerekmektedir. Daha önceden tanımlanmış olan bu kısayol tuşları ile işleminizi gerçekleştirebilirsiniz. Daha önceden bir tanımlama yapılmamışsa aşağıdaki şekilde görüleceği gibi makroların açıldığı ekrandan Options ile tanımlama yapılabilir.

Üçüncü yöntemi iki farklı şekilde ayırmak mümkündür. Burada istenilirse Developer menüsü altından Command Button dosyaya eklenerek makro bu button içerisine tanımlı hale getirilir. Bunun için commandbutton ekledikten sonra üzerine çift tıklayarak ilgili bölüme makroda yer alan kodları taşımanız yeterlidir.

Üçüncü yöntem için ikinci seçenek ise daha önce güvenlik ayarlarında kullandığımız excel options sekmesini kullanarak buradaki Customize bölümünden Macros seçeneğini seçerek bu makroyu kendimize bir kısayol tuşu gibi atama yöntemidir.

Son olarak tüm bunların dışında makrolarınıza Developer menüsünden Visual Basic sekmesini kullanarak da ulaşabilirsiniz. Bu amaçla sizin Visual Basic bölümünde Project Explorer kısmını görünür hale getirmeniz faydalı olacaktır. Bu bölümde makrolarınız modüller altında veya atadığınız buttonlar içerisinde Sheets kısımlarında yer alacaktır.

Süleyman TOSUN, PMP

Excelde pivot table hazırlanması, design, format, makro kodları vb. bilgileri anlatacağımız bu seride ilk olarak pivot table makrolarla kullanımında önemli konular nelerdir başlığını ele alacağız.

1. Pivot table exceldeki verilerinizin raporlanmasını kolay ve görsel bir biçimde sağlayan bir fonksiyondur. Öncelikle verileri alacağınız sayfayı bir database olarak düşünebilirsiniz, bu database içerisinde yer alacak tüm verilerin sütun başlıklarının belirtilmesi gerekmektedir. Bu sütun başlıkları raporlamada ayrı kolonlar olarak kullanılacaktır.

2. Seçeceğiniz alan daha sonradan genişleyecek ve yeni veriler ekleyecekseniz alanı daraltmak veya daha geniş alan seçmek konusunda dikkat etmeniz gerekecektir. Özellikle makro kodlarında pivot oluşturduktan sonra kodlarla refresh yapılması durumunda pivotun son satırının hangi satır olduğunun tespit edilmesi önemlidir. Aksi takdirde yeni eklenecek bilgiler eski belirtilen sınırların dışında kalabilir. Bunu sağlamak için sütunlar içerisinde count özelliğini kullanabilir veya ayrıca kodlarla bu satırı tespit edebilirsiniz.

3. Blank olarak belirtilen hücrelerin de raporlarda yer alacağını düşünerek oluşturduğunuz pivot table üzerinden kodlama yapacaksanız bu alanda Grand Total, Sub Total ve Blank seçeneklerinin de olacağını düşünerek buna göre dizi veya değişkenlerinizi set etmeniz gerekecektir.

4. Verilerin makrolarda kullanılmasını sağlamak amacıyla (pivot table veri alanı olarak yer alacaksa), en iyi gösterim tabular form olacaktır.

5. Pivot Table verilerinin refresh edilmesi ile ilgili bilgileri otomatik veya sadece istenildiğinde şeklinde set etmek alınacak önemli bir karardır. Ayrıca her bir yenileme işlemi ile birlikte yeni kolonlar eklenecekse bu durumda pivotun yanında olan diğer bilgileri otomatik olarak kaybetme ihtimali bulunmaktadır. Bu sebeple yeni alan ekleme öncesinde yeterli boş sütun olup olmadığı düşünülmeli veya yeni sütun eklemek için kodlar kullanılmalıdır.

Aşağıda genel olarak kullanılabilecek kodların yapısının tanıtılması için örnekler eklenmiştir.

Pivot Table Eklemek İçin

Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        “Veriler!R2C1:R41C1″, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=”Sheet1!R3C1″, TableName:=”PivotTable1″, DefaultVersion _
        :=xlPivotTableVersion12

Field List Gösterim

ActiveWorkbook.ShowPivotTableFieldList = True

Veri Değişiklik

ActiveSheet.PivotTables(“PivotTable1″).ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        “Ver,!R4C1:R41C19″ _
        , Version:=xlPivotTableVersion12)

Tabular Form Gösterim

  ActiveSheet.PivotTables(“PivotTable1″).PivotFields( _
        “Alan”).LayoutForm = xlTabular

Otomatik Refresh

ActiveSheet.PivotTables(“PivotTable1″).PivotCache.RefreshOnFileOpen = True

Format Condition

Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
    End With

    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValuePercent
        .Value = 33
        .Operator = 7
    End With

    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValuePercent
        .Value = 67
        .Operator = 7
    End With

Tablo Görünüm

ActiveSheet.PivotTables(“PivotTable1″).TableStyle2 = “PivotStyleMedium11″

Alanları Eklemek

ActiveSheet.PivotTables(“PivotTable1″).AddDataField ActiveSheet.PivotTables( _
        “PivotTable1″).PivotFields(“Alan1″), “Sum of Alan1″, xlSum

Grafik Eklemek

ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=Range(“‘Sheet1′!$A$3:$B$33″)
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xlColumnClustered

Üzerinde çalışmış olduğunuz excel dosyasını mail gönderme işlemini sık sık yapıyorsanız ve bu rutin olarak birden fazla dosya için gerçekleşiyorsa en kolay yol bu excel dosyalarını açarak onları mail ile iletecek kodları kullanmaktır. Aşağıdaki kodlarla size aktif olan excel dosyasının mail ile nasıl iletileceğini belirtiyor.

Gönderen, gönderici, cc, konu başlığı ve içerik kısımlarını siz kendi içeriğinize uygun olarak düzeltebilirsiniz.

Bu kodları dosya açma kodları ile de birleştirerek tüm işlemi otomatik hale getirmek mümkündür.

Not: Kodlar içerisinde yer alan display mailin sadece hazırlanması ama gönderilmemesi için kullanılabilecek kod satırıdır. Maili hiç görüntülemeden direkt göndermek isterseniz Send kod satırını kullanmanız gerekmektedir.

 Dim myPassword As String
Dim myOutlok As Object
Dim myMailItm As Object

Set otlApp = CreateObject(“Outlook.Application”)
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & “” & ActiveWorkbook.Name

With otlNewMail
.SentOnBehalfOfName = “gönderen”
.To = “gönderilen”
.CC = “cc”
.subject = “konu başlığı”
.Body = “içerik”
.Attachments.Add fName
.Display
‘.Send
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing

Saygılarımla,

Süleyman TOSUN, PMP

Excel içerisinde bazen kullanıcıların şifre ile girişlerinin sağlanması ve güvenliği artırma ihtiyacı söz konusu olabilir. Tüm güvenlik önlemleri ayrıca ele alınmak koşulu ile bu kısımda arayüz olarak kullanılabilecek kısım ve kodlarını ele alacağız.

Öncelikle arayüz için görseli hemen paylaşalım.

Kullanıcı Kodu kısmı TextBox3 içerisinde yer alıyor ve şifre ise TextBox4 kısmında yazılıyor.  Şifre ifadelerinin * karakteri ile girilmesi için TextBox4 properties kısmından PasswordChar bölümüne * karakteri ekleniyor.  Ve tüm bu işlemde kullanıcı şifre kontrolünü ele alacak tuşumuz CommandButton2_Click olarak konumlandırıldı.

Şifre kontrolü için dosya içerisinde yer alan Kullanıcılar sayfası kullanılıyor ve şifre kontrolü sonrasında kullanıcı AnaSayfa olarak adlandırılmış sayfaya yönlendiriliyor.

İşte kodlarımız ve açıklamaları:

Private Sub CommandButton2_Click()

Dim userıd, passıd, user, pass, isim
Dim i As Integer

‘ Alanların Boş Geçilmemesi
If TextBox3.Text = “” Then
MsgBox (“Lütfen Kullanıcı Kodunuzu Giriniz”)
TextBox3.Activate
GoTo a
End If

If TextBox4.Text = “” Then
MsgBox (“Lütfen Şifrenizi Giriniz”)
TextBox4.Activate
GoTo a
End If

‘ Değişkenlere değerlerin alınması
userıd = TextBox3.Text
passıd = TextBox4.Text

‘ Şifre kontrolü

For i = 2 To 500

user = Sheets(“Kullanıcı”).Range(“A” & i)

If userıd = user Then
pass = Sheets(“Kullanıcı”).Range(“C” & i)
isim = Sheets(“Kullanıcı”).Range(“B” & i)

If Val(passıd) = Val(pass) Then
   TextBox3.Text = “”
   TextBox4.Text = “”
  
    ‘ şifre doğruysa ana sayfanın açılması
    Sheets(“Anasayfa”).Visible = True
    Sheets(“Anasayfa”).Select
    Sheets(“giris”).Visible = False
    ActiveWindow.Zoom = 100
 Sheets(“Aktif”).Range(“A2″) = userıd

‘Ana sayfa tuşların gizlenmesi
Sheets(“Anasayfa”).CommandButton9.Visible = False
Sheets(“Anasayfa”).CommandButton1.Visible = False
Sheets(“Anasayfa”).CommandButton10.Visible = False
Sheets(“Anasayfa”).CommandButton11.Visible = False
Sheets(“Anasayfa”).CommandButton12.Visible = False
Sheets(“Anasayfa”).CommandButton13.Visible = False
Sheets(“Anasayfa”).CommandButton14.Visible = False
Sheets(“Anasayfa”).CommandButton15.Visible = False
Sheets(“Anasayfa”).CommandButton16.Visible = False
Sheets(“Anasayfa”).CommandButton7.Visible = False
Sheets(“Anasayfa”).CommandButton17.Visible = False
Sheets(“Anasayfa”).CommandButton5.Visible = False
Sheets(“Anasayfa”).CommandButton6.Visible = False
 
 GoTo b

       
        Else
    ‘ Şifrenin yanlış olması durumu
    MsgBox (“Girmiş olduğunuz şifreniz yanlış, lütfen tekrar deneyiniz”)
   TextBox4.Text = “”
  TextBox4.Activate
 
    GoTo a
    End If

End If

If i = 500 Then

MsgBox (“Girmiş olduğunuz kullanıcı kodu yanlış, lütfen tekrar deneyiniz”)
TextBox3.Text = “”
 TextBox4.Text = “”
TextBox3.Activate
GoTo a

End If
Next i

a:
b:

End Sub

Saygılarımla.

Süleyman TOSUN, PMP

Excel içerisinde yer alan data list (data validation) özelliğini bir çoğumuz kullanmıştır. Genel olarak bu özelliğin haricinde bir ek gereklilik olduğunu fark ettim.

Örneğin birden fazla seçim kriteriniz ve sınıflandırma verileriniz mevcut, ilk kritere bağlı olarak ikinci alt kriter setinin değişmesini arzu ediyorsunuz. Bu durumda data validation sizler için yeterli olmayabilir.

Ekteki dosya içerisinde veri girişinin yapıldığı sayfayı ve verilerin kriter setlerinin yazılı olduğu iki sayfa yer almaktadır. İkinci sayfa içerisinde yer alan verileri değiştirerek sizler de bu dosyayı kendi çalışmalarınızda kullanabilirsiniz.

ExcelFormul

Saygılarımla.

Süleyman TOSUN, PMP

Excel içerisinde oluşturulan pivot table için daha önceki zamanlarda hücrelere verilen koşulların yeterli olduğu görülüyordu. 2007 ile birlikte refresh edilen pivot table içerisinde sorunlar yaşanabiliyor. Fakat bu noktada bir önemli özellik ortaya çıkıyor.

Şimdi sırası ie pivot table içerisinde tek bir sütuna ait nasıl koşullu formatlama yapılabileceğini görelim ve sonrasında bunların makro kodlarını inceleyelim.

 Öncelikle basit bir veri oluşturalım.

 Burada yer alan çalışma verilerini kişi ve konu bazında pivot tablo içerisinde gösterelim.

Tüm bu veriler içerisinde saat olarak 9 değerinin üzerinde değer gösteren kısımların kırmızı ile renklendirilmesi için koşullu format ifadesi girelim. Bu ifade değerlerden ilkinin olduğu B5 hücresi üzerinde iken gerçekleştirilecektir.

Bu işlem sonunda B5 hücresinin yanında aşağıdaki şekilde bir ifade yer alacaktır.

Bu işaretin yan tarafında bulunan ok tuşuna basıldığında 3 farklı seçenek sunulmaktadır. Yapacağınız işleme göre bu seçeneklerden biri ile işleme devam edebilirsiniz.

Şimdi gelelim tüm bu işlemler için oluşturulmuş olan makroları incelemeye. Bunun için de bu işlemleri yaparken makro kaydet seçeneğini kullandığımızı hatırlatalım.

Pivot Tablo Oluşturma Kodları

   Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        “Sheet1!R1C1:R16C4″, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=”Sheet5!R3C1″, TableName:=”PivotTable2″, DefaultVersion _
        :=xlPivotTableVersion12
    Sheets(“Sheet5″).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables(“PivotTable2″).PivotFields(“Ad”)
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(“PivotTable2″).PivotFields(“Konu”)
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables(“PivotTable2″).AddDataField ActiveSheet.PivotTables( _
        “PivotTable2″).PivotFields(“Çalışma Saati”), “Sum of Çalışma Saati”, xlSum

Koşullu Formatın İlk Hücre İçin Oluşturulması

   Range(“B5″).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:=”=9″
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Pivot Seçeğinin Genişletilmesi

1. Seçenek

 Selection.FormatConditions(1).ScopeType = xlSelectionScope

2. Senenek

 Selection.FormatConditions(1).ScopeType = xlDataFieldScope

3. Seçenek

   Selection.FormatConditions(1).ScopeType = xlFieldsScope

 Bir sonraki yazımızda bu kodların nasıl değiştirilebileceğini ve kullanılabileceğini ele alacağız.

Süleyman TOSUN, PMP

Excel içerisinde makro kaydetmek için gerekli olan Developer menüsünü daha önceki yazımızda tanıtmıştık. Burada Developer menüsünün altında yer alan Record Macro seçeneğini inceleyeceğiz.

Record Macro seçeneği ile excel içerisinde yapacağınız tekrarlayan işleri birer makro olarak kaydetmek, onlara kısa yol tuşları tanımlamak ve daha sonra tüm bu işleri tekrar yapmak yerine bu kısa yol tuşunu kullanmak mümkün olacaktır.

Bu bölümde MacroName, Shortcut Key, Store Macro in, Description tanımlamaları yer alır.

Bu tanımlamalar kolaylıkla anlaşılabilecek şekilde konumlandırılmıştır. Anlatılması gereken tek önemli kısım Store Macro in seçeneğidir.

Bu bölümde yer alan seçeneklerin farklılıklarını inceleyelim:

Personal Macro Workbook: Excel içerisinde tüm dosyalarda geçerli bir makro oluşturmak için personal.xlsb dosyasını açar ve dosyayı buraya kaydeder.

New Workbook:Yeni bir excel dosyası açılır ve mkro bu dosya içerisine kaydedilir.

This Workbook: Makroyu sadece aktif olan dosyada kaydeder.

Record Macro ile ilgili bilgilerin girilmesi sonrasında OK tuşuna basılırsa kayıt işlemi başlayacaktır. Kayıt esnasında makronuzda yer alan işlemleri siz excel içerisinde gerçekleştirdikçe bunlar kodlara dönüşecektir.

Makro kaydının durdurulması için yapmanız gereken Stop Recording seçeneğini kullanmaktır.

Stop Recording ile birlikte makro kayıt işlemi sona erer. Bu durumda artık makronuzu isteğiniz zaman kullanabilirsiniz. Farklı fonksiyonlar şeklinde makronuzu konumlandırarak yeni kodlarda kullanabilirsiniz.

Ya da en önemlisi ve benim en çok faydalandığım nokta: Kodunun nasıl yazıldığını bilmediğiniz bir işlemi makro kaydet seçeneği ile kaydedip sonra hazır kodları kendinize göre uyarlayabilirsiniz.

Kaydedilen makro bilgisini görmek için Macros seçeneğini kullanabilirsiniz.

Macros seçeneği ile var olan tüm makrolarınızın isimleri size sunulur. Bu bölümden ilgili makroyu çalıştırabilir. Makronun kodlarına bakabilir, bu kodları değiştirebilir veya makroyu silebilirsiniz. Makro kaydı yaparken yaptığınız tanımlamaları da options seçeneği ile değiştirmeniz mümkündür.

Önemli iki not:

1. Makro kaydederken kullanacağınız kısayol tuşu mevcutta kullandığınız kısayol tuşlarından farklı olsun.

2. Makroyu kaydettikten sonra excel dosyanızı makro ile kaydetmek için Excel Macro-Enabled Workbook seçeneğini kullanın.

Makro seçeneği ile kaydetmiş olduğunuz dosyaların uzantısı xlsm olacaktır.

Bir sonraki yazımızda Visual Basic Editor ekranlarının tanıtımını yapıyor olacağız.

Süleyman TOSUN, PMP

Excelde makrolarla çalışmak için öncelikle “Developer” menüsünün açık olması gerekmektedir. Bu kısım ile birlikte hem makro kaydetmek hem de vb kodları ile makrolarınızı oluşturmak mümkün olacaktır. Ayrıca visual basic editor erişimi de bu bölümden yapılabilmektedir. Developer menüsü için öncelikle Excel Options seçeneğini seçerek “Show deveopler tab in the Ribbon” seçeneğini işaretlemeniz gerekiyor.