VBA'ya Yeni Başlayanlar için 24 Yararlı Excel Makro Örnekleri (Kullanıma Hazır)

Excel Makrolarını kullanmak işi hızlandırabilir ve size çok zaman kazandırabilir.

VBA kodunu almanın bir yolu, makroyu kaydetmek ve oluşturduğu kodu almaktır. Ancak, makro kaydedici tarafından bu kod genellikle gerçekten gerekli olmayan kodlarla doludur. Ayrıca makro kaydedicinin bazı sınırlamaları vardır.

Bu nedenle, arka cebinizde bulundurabileceğiniz ve gerektiğinde kullanabileceğiniz kullanışlı VBA makro kodları koleksiyonuna sahip olmakta fayda var.

Bir Excel VBA makro kodu yazmak başlangıçta biraz zaman alabilir, ancak bittiğinde, onu referans olarak hazır tutabilir ve daha sonra ihtiyacınız olduğunda kullanabilirsiniz.

Bu büyük makalede, sık sık ihtiyaç duyduğum ve özel kasamda sakladığım bazı yararlı Excel makro örneklerini listeleyeceğim.

Bu öğreticiyi daha fazla makro örneği ile güncellemeye devam edeceğim. Listede olması gerektiğini düşünüyorsanız, yorum bırakmanız yeterli.

Gelecekte başvurmak üzere bu sayfayı yer imlerine ekleyebilirsiniz.

Şimdi Makro Örneğine girmeden ve size VBA kodunu vermeden önce, bu örnek kodları nasıl kullanacağınızı göstereyim.

Excel Makro Örneklerinden Kodu Kullanma

Örneklerden herhangi birindeki kodu kullanmak için izlemeniz gereken adımlar şunlardır:

• Makroyu kullanmak istediğiniz Çalışma Kitabını açın.
• ALT tuşunu basılı tutun ve F11 tuşuna basın. Bu, VB Düzenleyicisini açar.
• Proje gezgininde herhangi bir nesneye sağ tıklayın.
• Ekle -> Modül'e gidin.
• Modül Kodu Penceresindeki kodu kopyalayıp yapıştırın.

Örnek, kodu çalışma sayfası kod penceresine yapıştırmanız gerektiğini söylüyorsa, çalışma sayfası nesnesine çift tıklayın ve kodu kopyalayarak kod penceresine yapıştırın.

Kodu bir çalışma kitabına ekledikten sonra, onu bir .XLSM veya .XLS uzantısıyla kaydetmeniz gerekir.

Makro Nasıl Çalıştırılır

Kodu VB Editöründe kopyaladıktan sonra, makroyu çalıştırma adımları şunlardır:

• Geliştirici sekmesine gidin.
• Makrolar'a tıklayın.

• Makro iletişim kutusunda, çalıştırmak istediğiniz makroyu seçin.
• Çalıştır düğmesine tıklayın.

Şeritte geliştirici sekmesini bulamıyorsanız, nasıl edineceğinizi öğrenmek için bu öğreticiyi okuyun.

İlgili Eğitim: Excel'de makro çalıştırmanın farklı yolları.

Kodun çalışma sayfası kod penceresine yapıştırılması durumunda, kodu çalıştırma konusunda endişelenmenize gerek yoktur. Belirtilen eylem gerçekleştiğinde otomatik olarak çalışacaktır.

Şimdi, işi otomatikleştirmenize ve zamandan tasarruf etmenize yardımcı olabilecek faydalı makro örneklerine geçelim.

Not: Bir veya iki satırdan sonra kesme işaretinin (') birçok örneğini bulacaksınız. Bunlar, kodu çalıştırırken yok sayılan ve kendi kendine/okuyucu için notlar olarak yerleştirilen yorumlardır.

Makalede veya kodda herhangi bir hata bulursanız, lütfen harika olun ve bana bildirin.

Excel Makro Örnekleri

Aşağıdaki makro örnekleri bu makalede ele alınmıştır:

Tüm Çalışma Sayfalarını Tek Seferde Göster

Birden çok gizli sayfası olan bir çalışma kitabında çalışıyorsanız, bu sayfaları birer birer göstermeniz gerekir. Çok sayıda gizli sayfa olması durumunda bu biraz zaman alabilir.

İşte çalışma kitabındaki tüm çalışma sayfalarını gösterecek olan kod.

'Bu kod çalışma kitabındaki tüm sayfaları gösterecek Sub UnhideAllWoksheets() Dim ws As Worksheet As Worksheet In ActiveWorkbook'ta.

Yukarıdaki kod, çalışma kitabındaki her bir çalışma sayfasından geçmek için bir VBA döngüsü (Her Biri İçin) kullanır. Daha sonra çalışma sayfasının görünür özelliğini görünür olarak değiştirir.

Excel'de sayfaları göstermek için çeşitli yöntemlerin nasıl kullanılacağına dair ayrıntılı bir eğitim.

Etkin Sayfa Dışındaki Tüm Çalışma Sayfalarını Gizle

Bir rapor veya gösterge tablosu üzerinde çalışıyorsanız ve raporun/gösterge tablosunun bulunduğu çalışma sayfasının dışındaki tüm çalışma sayfasını gizlemek istiyorsanız, bu makro kodunu kullanabilirsiniz.

'Bu makro, aktif sayfa hariç tüm çalışma sayfasını gizleyecektir Sub HideAllExceptActiveSheet() Dim ws As Worksheet As Worksheet In ThisWorkbook.Worksheets Eğer ws.Name ActiveSheet.Name Sonra ws.Visible = xlSheetHidden ws End Sub

VBA Kullanarak Çalışma Sayfalarını Alfabetik Olarak Sıralayın

Çok sayıda çalışma sayfası içeren bir çalışma kitabınız varsa ve bunları alfabetik olarak sıralamak istiyorsanız, bu makro kodu gerçekten kullanışlı olabilir. Sayfa adlarınız yıl veya çalışan adları veya ürün adları olarak varsa bu durum söz konusu olabilir.

'Bu kod, çalışma sayfalarını alfabetik olarak sıralar Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name O zaman Sheets(j).Move Before:=Sheets(i) End If Next j Sonraki i Application.ScreenUpdating = True End Sub

Tüm Çalışma Sayfalarını Tek Seferde Koruyun

Bir çalışma kitabında çok sayıda çalışma sayfanız varsa ve tüm sayfaları korumak istiyorsanız bu makro kodunu kullanabilirsiniz.

Kod içerisinde şifre belirlemenizi sağlar. Çalışma sayfasının korumasını kaldırmak için bu parolaya ihtiyacınız olacak.

'Bu kod tüm sayfaları tek seferde koruyacaktır Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'Test123'ü istediğiniz parolayla değiştirin For Her ws In Worksheets ws.Protect password:=password Sonraki ws Alt Bitiş

Tek Seferde Tüm Çalışma Sayfalarının Korumasını Kaldırın

Çalışma sayfalarınızın bir kısmı veya tamamı korunuyorsa, korumayı kaldırmak için sayfaları korumak için kullanılan kodda küçük bir değişiklik yapabilirsiniz.

'Bu kod tek seferde tüm sayfaları koruyacaktır Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'Test123'ü istediğiniz parolayla değiştirin For Her ws In Worksheets ws.Unprotect password:=password Sonraki ws Alt Bitiş

Parolanın, çalışma sayfalarını kilitlemek için kullanılanla aynı olması gerektiğini unutmayın. Değilse, bir hata göreceksiniz.

Tüm Satırları ve Sütunları Göster

Bu makro kodu, tüm gizli satırları ve sütunları gösterecektir.

Başka birinden bir dosya alırsanız ve gizli satır/sütun olmadığından emin olmak istiyorsanız, bu gerçekten yardımcı olabilir.

'Bu kod, Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub'daki tüm satırları ve sütunları gösterecek.

Tüm Birleştirilmiş Hücreleri Ayır

Hücreleri birleştirmek için birleştirmek yaygın bir uygulamadır. İşi yaparken, hücreler birleştirildiğinde verileri sıralayamazsınız.

Birleştirilmiş hücrelere sahip bir çalışma sayfasıyla çalışıyorsanız, birleştirilmiş tüm hücreleri tek seferde ayırmak için aşağıdaki kodu kullanın.

'Bu kod, birleştirilmiş tüm hücreleri ayıracak Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Birleştir ve Ortala yerine Seçim Boyunca Ortala seçeneğini kullanmanızı önerdiğimi unutmayın.

Çalışma Kitabını Adında Zaman Damgası ile Kaydet

Çoğu zaman, çalışmanızın sürümlerini oluşturmanız gerekebilir. Bunlar, zamanla bir dosyayla çalıştığınız uzun projelerde oldukça faydalıdır.

Dosyayı zaman damgalarıyla kaydetmek iyi bir uygulamadır.

Zaman damgalarını kullanmak, hangi değişikliklerin yapıldığını veya hangi verilerin kullanıldığını görmek için belirli bir dosyaya geri dönmenizi sağlar.

Çalışma kitabını otomatik olarak belirtilen klasöre kaydedecek ve her kaydedildiğinde bir zaman damgası ekleyecek kod burada.

'Bu kod, Dosyayı kendi adında bir Zaman Damgası ile Kaydedecektir. ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" ve zaman damgası End Sub

Klasör konumunu ve dosya adını belirtmeniz gerekir.

Yukarıdaki kodda “C:UsersUsernameDesktop kullandığım klasör konumudur. Dosyayı kaydetmek istediğiniz klasör konumunu belirtmeniz gerekir. Ayrıca, dosya adı öneki olarak "Çalışma KitabıAdı" genel adını kullandım. Projeniz veya şirketinizle ilgili bir şey belirtebilirsiniz.

Her Çalışma Sayfasını Ayrı Bir PDF Olarak Kaydet

Farklı yıllara veya bölümlere veya ürünlere ait verilerle çalışıyorsanız, farklı çalışma sayfalarını PDF dosyaları olarak kaydetmeniz gerekebilir.

Manuel olarak yapıldığında zaman alıcı bir süreç olsa da, VBA gerçekten hızlandırabilir.

İşte her çalışma sayfasını ayrı bir PDF olarak kaydedecek bir VBA kodu.

'Bu kod, her çalışma sayfasını ayrı bir PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" olarak kaydeder Sonraki ws End Sub

Yukarıdaki kodda, PDF'leri kaydetmek istediğim klasör konumunun adresini belirttim. Ayrıca, her PDF, çalışma sayfasının adıyla aynı adı alacaktır. Bu klasör konumunu değiştirmeniz gerekecektir (adınız da Sumit değilse ve masaüstünde bir test klasörüne kaydetmiyorsanız).

Bu kodun yalnızca çalışma sayfaları için çalıştığını (grafik sayfaları için değil) unutmayın.

Her Çalışma Sayfasını Ayrı Bir PDF Olarak Kaydet

İşte tüm çalışma kitabınızı belirtilen klasöre PDF olarak kaydedecek kod.

'Bu kod tüm çalışma kitabını PDF Sub olarak kaydedecektir SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Bu kodu kullanmak için klasör konumunu değiştirmeniz gerekecektir.

Tüm Formülleri Değerlere Dönüştür

Çok sayıda formül içeren bir çalışma sayfanız olduğunda ve bu formülleri değerlere dönüştürmek istediğinizde bu kodu kullanın.

'Bu kod, tüm formülleri değerlere dönüştürecek Sub ConvertToValues() ile ActiveSheet.UsedRange .Value = .Value End With End Sub

Bu kod, kullanılan hücreleri otomatik olarak tanımlar ve değerlere dönüştürür.

Hücreleri Formüllerle Koruyun/Kilitleyin

Çok fazla hesaplamanız olduğunda ve yanlışlıkla silmek veya değiştirmek istemiyorsanız, hücreleri formüllerle kilitlemek isteyebilirsiniz.

İşte diğer tüm hücreler kilitli değilken formülleri olan tüm hücreleri kilitleyecek kod.

'Bu makro kodu, Sub LockCellsWithFormulas() ile ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

İlgili Eğitim: Excel'de Hücreler Nasıl Kilitlenir.

Çalışma Kitabındaki Tüm Çalışma Sayfalarını Koruyun

Bir çalışma kitabındaki tüm çalışma sayfalarını tek seferde korumak için aşağıdaki kodu kullanın.

'Bu kod çalışma kitabındaki tüm sayfaları koruyacaktır Sub ProtectAllSheets() Dim ws As Worksheet Her ws In Worksheets ws.Protect Next ws End Sub

Bu kod tüm çalışma sayfalarını tek tek inceleyecek ve koruyacaktır.

Tüm çalışma sayfalarının korumasını kaldırmak istiyorsanız, kodda ws.Protect yerine ws.Unprotect kullanın.

Seçimdeki Her Satırdan Sonra Bir Satır Ekle

Seçilen aralıktaki her satırdan sonra boş bir satır eklemek istediğinizde bu kodu kullanın.

'Bu kod, seçimdeki her satırdan sonra bir satır ekleyecektir Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 CountRow ActiveCell.EntireRow. ActiveCell.Offset(2, 0) ekleyin. İleri'yi seçin i End Sub

Benzer şekilde, seçilen aralıktaki her sütundan sonra boş bir sütun eklemek için bu kodu değiştirebilirsiniz.

Bitişik Hücreye Tarih ve Zaman Damgasını Otomatik Olarak Ekle

Zaman damgası, etkinlikleri izlemek istediğinizde kullandığınız bir şeydir.

Örneğin, belirli bir harcamanın ne zaman yapıldığı, satış faturasının ne zaman oluşturulduğu, hücreye veri girişinin ne zaman yapıldığı, raporun en son ne zaman güncellendiği gibi aktiviteleri izlemek isteyebilirsiniz.

Bir giriş yapıldığında veya mevcut içerikler düzenlendiğinde bitişik hücreye bir tarih ve saat damgası eklemek için bu kodu kullanın.

'Bu kod bitişik hücreye bir zaman damgası ekleyecektir Private Sub Worksheet_Change(ByVal Target As Range) Hatasında GoTo Handler If Target.Column = 1 And Target.Value "" Sonra Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "gg-aa-yyyy ss:dd:ss") Application.EnableEvents = True End If Handler: End Sub

Bu kodu çalışma sayfası kod penceresine eklemeniz gerektiğini unutmayın (ve şimdiye kadar diğer Excel makro örneklerinde yaptığımız gibi modül içi kod penceresine değil). Bunu yapmak için, VB Editöründe, bu işlevi istediğiniz sayfa adına çift tıklayın. Ardından bu kodu kopyalayıp o sayfanın kod penceresine yapıştırın.

Ayrıca, bu kod, A Sütununda veri girişi yapıldığında çalışacak şekilde yapılır (kodun Target.Column = 1 satırına sahip olduğunu unutmayın). Bunu buna göre değiştirebilirsiniz.

Seçimdeki Alternatif Satırları Vurgulayın

Alternatif satırları vurgulamak, verilerinizin okunabilirliğini büyük ölçüde artırabilir. Bu, bir çıktı almanız ve verileri gözden geçirmeniz gerektiğinde faydalı olabilir.

İşte seçimdeki alternatif satırları anında vurgulayacak bir kod.

'Bu kod, seçimdeki alternatif satırları vurgulayacaktır Sub HighlightAlternateRows() Myrange As Range Dim Myrow As Range Set Myrange = Myrange'daki Her Myrow için Seçim. Sonraki Myrow End Sub ise

Kodda rengi vbCyan olarak belirttiğimi unutmayın. Diğer renkleri de belirtebilirsiniz (vbRed, vbGreen, vbBlue gibi).

Yanlış Yazılan Sözcüklerle Hücreleri Vurgulayın

Excel'de Word veya PowerPoint'te olduğu gibi yazım denetimi yoktur. F7 tuşuna basarak yazım denetimi çalıştırabilirsiniz, ancak yazım hatası olduğunda görsel bir ipucu yoktur.

İçinde yazım hatası olan tüm hücreleri anında vurgulamak için bu kodu kullanın.

'Bu kod, yanlış yazılmış sözcükleri olan hücreleri vurgulayacaktır Sub HighlightMisspelledCells() Dim cl As Range As Range For ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) cl.Interior.Color = vbRed End If Next cl Bitiş Alt

Vurgulanan hücrelerin, Excel'in bir yazım hatası olarak kabul ettiği metin içeren hücreler olduğunu unutmayın. Çoğu durumda, anlamadığı adları veya marka terimlerini de vurgular.

Çalışma Kitabındaki Tüm Özet Tabloları Yenile

Çalışma kitabında birden fazla Özet Tablonuz varsa, tüm bu Özet tabloları bir kerede yenilemek için bu kodu kullanabilirsiniz.

'Bu kod, Workbook Sub RefreshAllPivotTables() Dim PT'deki ActiveSheet.PivotTables PT'deki Her PT için Dim Pivot Table'daki tüm Pivot Table'ı yenileyecektir.RefreshTable Next PT End Sub

Pivot Tabloları yenileme hakkında daha fazla bilgiyi burada bulabilirsiniz.

Seçili Hücrelerin Harf Harflerini Büyük Harf Olarak Değiştir

Excel, metnin büyük/küçük harflerini değiştirmek için formüllere sahip olsa da, bunu başka bir hücre kümesinde yapmanızı sağlar.

Seçili metindeki metnin büyük/küçük harf durumunu anında değiştirmek için bu kodu kullanın.

'Bu kod, Seçimi Büyük Harf Sub ChangeCase() Dim Rng As Range In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Bu durumda, metin büyüklüğünü Üst yapmak için UCase kullandığımı unutmayın. Küçük harf için LCase kullanabilirsiniz.

Tüm Hücreleri Yorumlarla Vurgula

İçinde yorum bulunan tüm hücreleri vurgulamak için aşağıdaki kodu kullanın.

'Bu kod, yorumları olan hücreleri vurgulayacaktır' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

Bu durumda hücrelere mavi renk vermek için vbBlue kullandım. İsterseniz bunu diğer renklerle değiştirebilirsiniz.

VBA ile Boş Hücreleri Vurgulayın

Koşullu biçimlendirmeyle veya Özel Git iletişim kutusunu kullanarak boş hücreyi vurgulayabilirsiniz, ancak bunu oldukça sık yapmanız gerekiyorsa bir makro kullanmak daha iyidir.

Oluşturulduktan sonra, bu makroyu Hızlı Erişim Araç Çubuğunda bulundurabilir veya kişisel makro çalışma kitabınıza kaydedebilirsiniz.

İşte VBA makro kodu:

'Bu kod, veri kümesindeki tüm boş hücreleri vurgulayacaktır Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

Bu kodda kırmızı renkle vurgulanacak boş hücreleri belirttim. Mavi, sarı, camgöbeği gibi diğer renkleri seçebilirsiniz.

Verileri Tek Sütuna Göre Sıralama

Verileri belirtilen sütuna göre sıralamak için aşağıdaki kodu kullanabilirsiniz.

Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlArtan, Header:=xlEvet End Sub

'DataRange' adıyla adlandırılmış bir aralık oluşturduğumu ve hücre referansları yerine onu kullandığımı unutmayın.

Ayrıca burada kullanılan üç anahtar parametre vardır:

• Anahtar1 - Bu, veri kümesini sıralamak istediğiniz şeydir. Yukarıdaki örnek kodda, veriler A sütunundaki değerlere göre sıralanacaktır.
• Sırala- Burada verileri artan veya azalan düzende sıralamak isteyip istemediğinizi belirtmeniz gerekir.
• Başlık - Burada verilerinizin üstbilgileri olup olmadığını belirtmeniz gerekir.

VBA kullanarak Excel'de verilerin nasıl sıralanacağı hakkında daha fazla bilgi edinin.

Verileri Birden Çok Sütuna Göre Sıralama

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım:

Verileri birden çok sütuna göre sıralayacak kod aşağıdadır:

Sub SortMultipleColumns() ActiveSheet.Sort ile .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlEvet .Apply End with End Sub

Burada, önce A sütununa göre, sonra B sütununa göre sıralamayı belirttiğimi unutmayın.

Çıktı aşağıda gösterildiği gibi bir şey olacaktır:

Excel'de Bir Dizeden Yalnızca Sayısal Parça Nasıl Alınır

Bir dizeden yalnızca sayısal bölümü veya yalnızca metin bölümünü çıkarmak istiyorsanız, VBA'da özel bir işlev oluşturabilirsiniz.

Daha sonra bu VBA işlevini çalışma sayfasında kullanabilirsiniz (tıpkı normal Excel işlevleri gibi) ve dizeden yalnızca sayısal veya metin bölümünü çıkaracaktır.

Aşağıda gösterildiği gibi bir şey:

Aşağıda, bir dizeden sayısal kısım çıkarmak için bir işlev oluşturacak VBA kodu verilmiştir:

'Bu VBA kodu, bir dizeden sayısal kısmı almak için bir işlev yaratacaktır Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Sonra Sonuç = Sonuç ve Orta(CellRef, i, 1) Sonraki i GetNumeric = Sonuç Bitiş Fonksiyonu

Bir modülde koda ihtiyacınız var ve ardından çalışma sayfasında =GetNumeric işlevini kullanabilirsiniz.

Bu işlev, sayısal kısmını almak istediğiniz hücrenin hücre başvurusu olan yalnızca bir argüman alacaktır.

Benzer şekilde, Excel'deki bir dizeden yalnızca metin bölümünü alacak işlev aşağıdadır:

'Bu VBA kodu, metin bölümünü bir dizeden almak için bir işlev yaratacaktır Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Sonra Sonuç = Sonuç ve Orta(CellRef, i, 1) Sonraki i GetText = Sonuç Bitiş Fonksiyonu

Bunlar, görevleri otomatikleştirmek ve çok daha üretken olmak için günlük işlerinizde kullanabileceğiniz bazı yararlı Excel makro kodlarıdır.