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

Excel makrolarını kullanmak işinizi hızlandırabilir ve çok zaman kazandırabilir.

VBA kodunu almanın bir yolu, bir makro kaydetmek ve oluşturduğu kodu almaktır.Ancak, makro kaydedicinin kodu genellikle gerçekten gerekli olmayan kodlarla doludur.Makro kaydedicinin de bazı sınırlamaları vardır.

Bu nedenle, arka cebinizde saklayabileceğiniz ve ihtiyacınız olduğunda kullanabileceğiniz kullanışlı bir VBA makroları setine sahip olmaya değer.

Başlangıçta bir Excel VBA makrosunu kodlamak biraz zaman alabilir, ancak işiniz bittiğinde onu referans olarak kullanabilir ve bir dahaki sefere 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üşündüğünüz bir şey varsa lütfen yorum bırakın.

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

Şimdi, makro örneklerine girmeden ve size VBA kodunu vermeden önce, örnek kodun nasıl kullanılacağını göstereyim.

Excel makro örneğindeki kodu kullanma

Örneklerden herhangi birindeki kodu kullanmak için izlenecek 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 açılacakVB düzenleyici.
• Proje Gezgini'nde herhangi bir nesneye sağ tıklayın.
• Ekle -> Modüller'e gidin.
• Modül kodu penceresine 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 kopyalayıp kod penceresine yapıştırın.

Kodu çalışma kitabına ekledikten sonra .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ı aşağıdaki gibidir:

• Geliştirici sekmesine gidin.
• Makro'yu tıklayın.

• Makrolar iletişim kutusunda, çalıştırılacak makroyu seçin.
• Çalıştır düğmesini tıklayın.

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

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

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

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

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

Excel Makro Örneği

Bu makalede aşağıdaki makro örnekleri açıklanmaktadır:

Tüm çalışma sayfalarını bir kerede göster

Birden çok gizli sayfa içeren bir çalışma kitabı üzerinde çalışıyorsanız, bu sayfaları birer birer göstermeniz gerekir.Çok sayıda gizli sayfa varsa bu biraz zaman alabilir.

İşte çalışma kitabındaki tüm sayfaları gösterme kodu.

'Bu kod, çalışma kitabındaki tüm sayfaları gösterecek Sub UnhideAllWoksheets() Dim ws As Worksheet In ActiveWorkbook'ta Her ws için. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

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

İşte çeşitli yöntemler kullanarak Excel'de bir çalışma sayfasının nasıl gösterileceğine dair ayrıntılı bir eğitim.

Etkin çalışma sayfası dışındaki tüm çalışma sayfalarını gizle

Bu makro kodu, bir rapor veya gösterge panosu üzerinde çalışıyorsanız ve raporun/gösterge panosunun bulunduğu sayfa dışındaki tüm sayfaları gizlemek istiyorsanız kullanılabilir.

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

Çalışma sayfasını VBA kullanarak alfabetik olarak sıralayın

Bu makro kodu, çok sayıda sayfa içeren bir çalışma kitabınız varsa ve bu sayfaları alfabetik olarak sıralamak istiyorsanız kullanışlı olabilir.Bu, yıl veya çalışan adı veya ürün adı olarak sayfa adınız varsa 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ı aynı anda koruyun

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

Kodda bir şifre belirlemenizi sağlar.Sayfanı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ş

Tüm çalışma sayfalarının korumasını bir kerede kaldırın

Çalışma sayfalarınızın bazılarını veya tamamını koruduysanız, çalışma sayfalarını korumak için kullanılan kodda yalnızca küçük bir değişiklik yaparak korumalarını kaldırabilirsiniz.

'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.Unprotect password:=password Sonraki ws Alt Bitiş

Parolanın, sayfayı kilitlemek için kullanılan parolayla 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österecek.

Dosyayı başka birinden alıyorsanız ve gizli satır/sütun olmadığından emin olmak istiyorsanız bu yararlı 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.

Birleştirilmiş tüm hücreleri ayır

Hücreleri tek bir hücrede birleştirmek yaygın bir uygulamadır.Çalışırken, hücreler birleştirildiğinde verileri sıralayamazsınız.

Birleştirilmiş hücrelere sahip bir çalışma sayfası kullanıyorsanız, birleştirilmiş tüm hücreleri bir kerede 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" seçeneği yerine "Seçimde Ortala" seçeneğini kullanmanızı önerdiğimi unutmayın.

Adında zaman damgası olan çalışma kitabını kaydet

Çalışan bir sürüm oluşturmanız gerekebilecek birçok zaman vardır.Bunlar, zaman içinde dosyalarla ilgilenen uzun vadeli projeler için harikadır.

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

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

İşte çalışma kitabını belirtilen klasöre otomatik olarak kaydeden ve kaydederken zaman damgası ekleyen kod.

'Bu kod, Dosyayı Adında Zaman Damgası ile Kaydedecektir Sub SaveWorkbookWithTimeStamp() Dim zaman damgası As String timestamp = Format(Date, "gg-aa-yyyy") & "_" & Format(Time, "ss-ss") 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 şirketiniz ile ilgili içerikler belirtebilirsiniz.

Her çalışma sayfasını ayrı bir PDF olarak kaydedin

Farklı yıllara veya departmanlara veya ürünlere ait verileri kullanırsanız, farklı çalışma sayfalarını PDF dosyaları olarak kaydetmeniz gerekebilir.

Bu, manuel olarak yapıldığında zaman alıcı bir süreç olsa da, VBA işleri hızlandırır.

İşte her çalışma sayfasını ayrı bir PDF olarak kaydeden 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'yi kaydetmek istediğim klasör konumunun adresini belirttim.Ayrıca, her PDF, sayfayla aynı adı alacaktır.Bu klasör konumunu değiştirmeniz gerekecektir (adınız da Sumit değilse ve masaüstünüzdeki bir test klasörüne kaydetmediyseniz).

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

Her çalışma sayfasını ayrı bir PDF olarak kaydedin

İşte tüm çalışma kitabını belirtilen klasöre PDF olarak kaydetme kodu.

'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ştirmelisiniz.

Tüm formülleri değerlere dönüştürün

Ç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 ActiveSheet.UsedRange ile Sub ConvertToValues() ile değerlere dönüştürecektir.Value = .Value End With End Sub

Bu kod, kullanılan hücreyi otomatik olarak tanır ve onu bir değere 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 tüm hücreleri formüllerle kilitleyecek ve diğer tüm hücreleri açık bırakacak 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 sayfaları koruma

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

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

Bu kod tüm sayfaları tek tek inceleyecek ve onları koruyacaktır.

Tüm sayfaların korumasını kaldırmak istiyorsanız, kodunuzda ws.Protect yerine ws.Unprotect kullanın.

Seçimin her satırından sonra bir satır ekle

Seçili 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 için. ActiveCell.Offset(2, 0) ekleyin. İleri'yi seçin i End Sub

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

Bitişik hücrelere otomatik olarak tarih ve zaman damgası ekle

Zaman damgaları, etkinliği izlemek istediğinizde kullandığınız şeydir.

Örneğin, belirli bir giderin ne zaman gerçekleştiği, bir satış faturasının ne zaman oluşturulduğu, bir hücreye ne zaman veri girildiği, bir raporun en son ne zaman güncellendiği vb. gibi etkinlikleri izlemek isteyebilirsiniz.

Mevcut içeriği yazarken veya düzenlerken bitişik hücrelere 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 Ve 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 (şimdiye kadar diğer Excel makro örneklerinde yaptığımız gibi modül kodu penceresinde değil).Bunu yapmak için, VB düzenleyicide, 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ışır (kodun bir Target.Column = 1 satırı olduğunu unutmayın).Buna göre değiştirebilirsiniz.

Seçimdeki alternatif satırları vurgulayın

Değişen satırları vurgulamak, verilerinizin okunabilirliğini büyük ölçüde artırabilir.Bu, çıktısını almanız ve verilere göz atmanız gerektiğinde faydalı olabilir.

İşte seçimdeki alternatif satırları hemen 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 (örn. vbRed, vbGreen, vbBlue).

Yanlış yazılmış hücreleri vurgula

Excel, Word veya PowerPoint'te yazım denetimine sahip değildir.F7 tuşuna basarak yazım denetimi çalıştırabilirsiniz, ancak yazım hataları için görsel bir ipucu yoktur.

Tüm yanlış yazılmış 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 ActiveSheet'deki Her cl için.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl Bitiş Alt

Vurgulanan hücrelerin, Excel'in yanlış yazıldığını düşündüğü metin içeren hücreler olduğunu unutmayın.Çoğu durumda, anlamadığı adları veya marka terimlerini de vurgular.

Bir çalışma kitabındaki tüm pivot tabloları yenileme

Çalışma kitabında birden çok pivot tablonuz varsa, hepsini bir kerede yenilemek için bu kodu kullanabilirsiniz.

'Bu kod, Çalışma Kitabındaki tüm Özet Tabloyu yeniler RefreshAllPivotTables() Dim PT, ActiveSheet.PivotTables PT'deki Her PT için PivotTable Olarak.RefreshTable Sonraki PT End Sub

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

Seçili hücrelerin harflerini büyük harfe çevir

Excel, metin harflerinin büyük/küçük harf durumunu 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ük/küçük harfe ayarlamak için UCase kullandığımı unutmayın.Küçük harf için LCase kullanabilirsiniz.

Tüm hücreleri yorumlarla vurgulayın

İçinde yorumlar 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ücreye mavi bir renk vermek için vbBlue kullanıyorum.Gerekirse başka bir renge değiştirebilirsiniz.

VBA ile boş hücreleri vurgulayın

Boş hücreleri vurgulamak için koşullu biçimlendirmeyi kullanabilir veya Özel Git iletişim kutusunu kullanabilirsiniz, ancak bunu sık sık yapmanız gerekiyorsa bir makro kullanmak en iyisidir.

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ı ile vurgulanan boş hücreleri belirttim.Mavi, sarı, camgöbeği gibi diğer renkleri seçebilirsiniz.

Veriler tek bir sütuna göre nasıl sıralanır?

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

Alt SıralamaDataHeader() 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlArtan, Başlık:=xlEvet 
End Sub

"DataRange" adında bir adlandırılmış aralık oluşturduğumu ve bunu hücre referansı yerine kullandığımı unutmayın.

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

• Anahtar1 – Bu, veri kümesini sıralamak istediğiniz anahtardır.Yukarıdaki örnek kodda veriler A sütunundaki değerlere göre sıralanacaktır.
• Sıra1 – Burada verileri artan veya azalan düzende sıralamak isteyip istemediğinizi belirtmeniz gerekir.
• Başlık – Burada verilerinizin bir başlığı 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

Şuna benzeyen bir veri kümeniz olduğunu varsayalım:

Verileri birden çok sütuna göre sıralamak için kullanılan kod:

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 .Uygula End with End Sub

Burada önce A sütununda, sonra B sütununda sıralama yapmayı belirttiğimi unutmayın.

Çıktı şöyle görünecektir:

Excel'deki bir dizeden yalnızca sayısal kısım nasıl alınır

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

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

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

Sayısal kısmı bir dizeden çıkaran bir işlev oluşturacak VBA kodu:

'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

Kodu bir modüle koymanız gerekir ve ardından çalışma sayfasında =GetNumeric işlevini kullanabilirsiniz.

Bu işlev, sayısal kısmın alınacağı hücrenin hücre referansı olan yalnızca bir parametre alacaktır.

Benzer şekilde, aşağıdaki işlev yalnızca metin bölümünü Excel'deki bir dizeden alı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 verimliliğinizi artırmak için günlük işlerinizde kullanabileceğiniz bazı yararlı Excel makro kodlarıdır.