Makroları kullanarak Excel'de rutin görevler nasıl otomatikleştirilir. Mironov. VBA Excel'de Hazır Makrolar

Çok az kişi, popüler Microsoft Excel ürününün ilk sürümünün 1985'te ortaya çıktığını biliyor. O zamandan beri, birkaç değişiklik geçirdi ve dünya çapında milyonlarca kullanıcı tarafından talep edildi. Aynı zamanda, birçoğu bu elektronik tablo işlemcisinin yeteneklerinin yalnızca küçük bir kısmıyla çalışıyor ve Excel'de programlama yaparak hayatlarının nasıl daha kolay hale getirilebileceğini bile bilmiyorlar.

VBA nedir

Excel'de programlama, orijinal olarak Microsoft'un en ünlü elektronik tablo işlemcisinde yerleşik olan Visual Basic for Application programlama dili kullanılarak gerçekleştirilir.

Uzmanlar, göreceli geliştirme kolaylığını avantajlarına bağlıyor. Pratikte görüldüğü gibi, profesyonel programlama becerilerine sahip olmayan kullanıcılar bile VBA'nın temellerini öğrenebilirler. VBA'nın özellikleri, komut dosyasının ofis uygulamaları ortamında yürütülmesini içerir.

Programın dezavantajı, farklı sürümlerin uyumluluğu ile ilgili problemlerdir. Bunun nedeni, VBA program kodunun, ürünün yeni sürümünde bulunan ancak eski sürümde olmayan işlevselliğe atıfta bulunmasıdır. Ayrıca, dezavantajlar, yetkisiz bir kişi tarafından değiştirilmek üzere kodun aşırı derecede açık olmasını içerir. Ancak, Microsoft Office ve IBM Lotus Symphony, kullanıcının çekirdeği şifrelemesine ve onu görüntülemek için bir parola belirlemesine olanak tanır.

Nesneler, Koleksiyonlar, Özellikler ve Yöntemler

VBA ortamında çalışacak kişilerin anlaması gereken kavramlar bunlardır. Her şeyden önce, bir nesnenin ne olduğunu anlamanız gerekir. Excel'de bu bir sayfa, çalışma kitabı, hücre ve aralıktır. Bu nesnelerin özel bir hiyerarşisi vardır, yani. birbirinize itaat edin.

Bunlardan en önemlisi, Excel programının kendisine karşılık gelen Uygulama'dır. Bunu Çalışma Kitapları, Çalışma Sayfaları ve Aralık takip eder. Örneğin, belirli bir sayfada A1 hücresine başvurmak için hiyerarşiye dayalı bir yol belirtmelisiniz.

"Koleksiyon" kavramına gelince, aynı sınıftan, kayıtta ChartObjects formuna sahip olan bir nesne grubudur. Bireysel unsurları da nesnelerdir.

Bir sonraki kavram özelliklerdir. Herhangi bir nesnenin gerekli bir özelliğidir. Örneğin, Aralık için Değer veya Formül'dür.

Yöntemler, yapılması gerekenleri belirten komutlardır. VBA'da kod yazarken, nesneden bir nokta ile ayrılmalıdırlar. Örneğin, daha sonra gösterileceği gibi, Hücreler (1,1) .Seç komutu Excel'de programlama yapılırken çok sık kullanılır. Bu, koordinatları olan bir hücre seçmeniz gerektiği anlamına gelir.

Selection.ClearContents genellikle onunla birlikte kullanılır. Yürütülmesi, seçilen hücrenin içeriğinin temizlenmesi anlamına gelir.

nasıl başlamalı

Ardından, "Alt" ve "F11" tuşlarının kombinasyonunu kullanmanın yeterli olduğu VB uygulamasına gitmeniz gerekir. Daha öte:

• pencerenin üst kısmında bulunan menü çubuğunda Excel simgesinin yanındaki simgeye tıklayın;
• Mudule ekibini seçin;
• resimli simgeye tıklayarak kaydedin;
• diyelim ki kodun bir taslağını yaz.

Şuna benziyor:

Alt program ()

"Bizim kodumuz

Lütfen "" Kodumuz " satırının farklı bir renkle (yeşil) vurgulanacağını unutmayın. Nedeni satırın başında kesme işareti içindedir, bu da bir yorumun ardından geldiği anlamına gelir.

Artık VBA Excel'de herhangi bir kod yazabilir ve kendiniz için yeni bir araç oluşturabilirsiniz (aşağıdaki program örneklerine bakın). Tabii ki, Visual Basic'in temellerine aşina olanlar bunu çok daha kolay bulacaktır. Ancak, bunlara sahip olmayanlar bile, istenirse buna yeterince çabuk alışabilecekler.

Excel makroları

Bu ad, Visual Basic for Application dilinde yazılmış programları gizler. Bu nedenle, Excel'de programlama, doğru kodla makrolar oluşturmakla ilgilidir. Bu özellik sayesinde, Microsoft elektronik tablo işlemcisi, belirli bir kullanıcının gereksinimlerine uyum sağlayarak kendini geliştirir. Makro yazmak için modüllerin nasıl oluşturulacağını öğrendikten sonra, belirli VBA Excel program örneklerine bakmaya hazırsınız. En ilkel kodlarla başlamak en iyisidir.

örnek 1

Görev: Bir hücrenin içeriğinin değerini kopyalayacak ve ardından diğerine yazacak bir program yazın.

Bunun için:

• "Görünüm" sekmesini açın;
• "Makrolar" simgesine gidin;
• "Makro Kaydet" e tıklayın;
• açılan formu doldurun.

Basit olması için, “Makro adı” alanına “Makro1”i bırakın ve örneğin “Klavye kısayolu” alanına hh ekleyin (bu, programın yıldırım komutu “Ctrl + h” ile başlatılabileceği anlamına gelir). Enter tuşuna basın.

Şimdi, makro kaydı zaten başladığında, bir hücrenin içeriği diğerine kopyalanır. Orijinal simgeye döner. "Makro Kaydet" e tıklayın. Bu eylem, programın sonu anlamına gelir.

• tekrar "Makrolar" satırına gidin;
• listeden "Makro 1"i seçin;
• "Çalıştır" ı tıklayın ("Ctrl + hh" klavye kısayolu başlatılarak aynı eylem başlatılır).

Sonuç olarak, makronun kaydı sırasında gerçekleştirilen eylem gerçekleşir.

Kodun neye benzediğini görmek mantıklı. Bunu yapmak için "Makrolar" satırına geri dönün ve "Değiştir" veya "Giriş" e tıklayın. Sonuç olarak kendilerini bir VBA ortamında bulurlar. Aslında, makro kodunun kendisi Sub Macro1 () ve End Sub satırları arasında bulunur.

Örneğin, A1 hücresinden C1 hücresine kopyalama yapıldıysa, kod satırlarından biri Aralık (“C1”) gibi görünecektir. Çevrilmiş, "Aralık (" C1 ") gibi görünüyor. Seç", başka bir deyişle VBA Excel'e, C1 hücresine geçiyor.

ActiveSheet.Paste komutu, kodun etkin bölümünü sonlandırır. Seçilen hücrenin (bu durumda A1) içeriğini seçilen C1 hücresine yazmak anlamına gelir.

Örnek 2

VBA döngüleri, Excel'de çeşitli makrolar oluşturmanıza yardımcı olur.

VBA döngüleri, çeşitli makrolar oluşturmanıza yardımcı olur. Diyelim ki y = x + x 2 + 3x 3 - cos (x) fonksiyonu var. Grafiği elde etmek için bir makro oluşturmak gerekir. Bu sadece VBA döngüleri kullanılarak yapılabilir.

İşlev argümanının ilk ve son değeri için x1 = 0 ve x2 = 10 alın. Ek olarak, bir sabit girmelisiniz - bağımsız değişkeni değiştirme adımının değeri ve sayacın başlangıç ​​değeri.

Tüm örnek VBA Excel makroları, yukarıda sunulan prosedürün aynısı kullanılarak oluşturulur. Bu özel durumda, kod şöyle görünür:

Alt program ()

sevişmek = 0.1

x1 iken yap< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Hücreler (i, 1) .Değer = x1 (x1 değeri (i, 1) koordinatlarıyla hücreye yazılır)

Hücreler (i, 2) .Değer = y (y değeri (i, 2) koordinatlarıyla hücreye yazılır)

i = i + 1 (sayaç aktif);

x1 = x1 + shag (argüman, adım boyutuna göre değiştirilir);

Alt Bitiş.

Bu makroyu Excel'de çalıştırmanın bir sonucu olarak, ilki x için, ikincisi y için değerleri içeren iki sütun elde ederiz.

Ardından, Excel için standart bir şekilde kullanılarak bir grafik çizilir.

Örnek 3

Diğer sürümlerde olduğu gibi VBA Excel 2010'da döngüleri uygulamak için, önceden verilen Do while yapısıyla birlikte For kullanılır.

Bir sütun oluşturacak bir program düşünün. Her hücre, karşılık gelen satırın sayısının karelerini içerecektir. For yapısını kullanmak, bir sayaç kullanmadan çok kısa bir süre yazmanıza izin verecektir.

Öncelikle yukarıda anlatıldığı gibi bir makro oluşturmanız gerekir. Ardından, kodun kendisini yazıyoruz. 10 hücre için değerlerle ilgilendiğimizi varsayıyoruz. Kod şuna benziyor.

i = 1 ila 10 için Sonraki

Komut, "insan" diline "1'den 10'a kadar birer artışlarla tekrarlayın" olarak çevrilir.

Görev, örneğin, 1'den 11'e kadar olan tüm tek sayıların kareleri olan bir sütun elde etmekse, şunu yazarız:

i = 1 ila 10 için adım 1 Sonraki.

Burada adım bir adımdır. Bu durumda, ikiye eşittir. Varsayılan olarak, bu kelimenin döngüde olmaması, adımın bir olduğu anlamına gelir.

Elde edilen sonuçlar (i, 1) numaralı hücrelere kaydedilmelidir. Daha sonra, i adım boyutunda bir artışla döngünün her başlangıcında, satır numarası otomatik olarak büyüyecektir. Böylece kod optimize edilmiş olacaktır.

Genel olarak, kod şöyle görünecektir:

Alt program ()

i = 1'den 10'a Adım 1 (sadece i = 1'den 10'a yazabilirsiniz)

Hücreler (i, 1) .Değer = i ^ 2 (yani i karesinin değeri (i, 1) hücresine yazılır)

Sonraki (bir anlamda, bir sayaç rolünü oynar ve döngünün başka bir başlangıcı anlamına gelir)

Alt Bitiş.

Makro kaydetme ve çalıştırma dahil her şey doğru yapılırsa (yukarıdaki talimatlara bakın), her çağrıldığında belirtilen boyutta bir sütun elde edilir (bu durumda 10 hücreden oluşur).

Örnek 4

Günlük yaşamda, bazı koşullara bağlı olarak, genellikle şu veya bu kararı vermeye ihtiyaç vardır. VBA Excel'de onlarsız yapamazsınız. Algoritma yürütmesinin daha sonraki yolunun seçildiği ve başlangıçta önceden tanımlanmadığı program örnekleri, çoğunlukla If… Then yapısını (karmaşık durumlar için) kullanır If… Then… END If.

Belirli bir durumu ele alalım. (1,1) koordinatlarına sahip hücrenin yazılması için Excel için bir makro oluşturmanız gerektiğini varsayalım:

1 argüman olumluysa;

0 argüman boşsa;

-1 argüman olumsuzsa.

Excel için böyle bir makronun oluşturulması, Alt ve F11 kısayol tuşlarını kullanarak standart şekilde başlar. Ardından, aşağıdaki kod yazılır:

Alt program ()

x = Hücreler (1, 1) .Değer (bu komut x'e hücre içeriğinin değerini (1, 1) koordinatlarında atar)

x> 0 ise Hücreler (1, 1) .Value = 1

x = 0 ise Hücreler (1, 1) .Value = 0

eğer x<0 Then Cells(1, 1).Value = -1

Alt Bitiş.

Makroyu çalıştırmak ve Excel'deki argüman için istenen değeri elde etmek için kalır.

VBA işlevleri

Fark etmiş olabileceğiniz gibi, Microsoft'un en ünlü elektronik tablo işlemcisinde programlama yapmak o kadar da zor değil. Özellikle VBA işlevlerini kullanmayı öğrenirseniz. Toplamda, Excel ve Word'de uygulama yazmak için özel olarak oluşturulan bu programlama dili, 160'a yakın fonksiyona sahiptir. Birkaç büyük gruba ayrılabilirler. Bilişim Teknoloji:

• Matematiksel fonksiyonlar. Bunları argümana uygulayarak kosinüs, doğal logaritma, tamsayı kısmı vb.'nin değerini elde edersiniz.
• Finansal fonksiyonlar. Varlıkları ve Excel'de programlama kullanmaları sayesinde, muhasebe ve finansal hesaplamalar için etkili araçlar elde edebilirsiniz.
• Dizi işleme işlevleri. Bunlar Array, IsArray; LBound; UBound.
• Dize için VBA Excel işlevleri. Bu oldukça büyük bir gruptur. Örneğin, bir tamsayı bağımsız değişkenine eşit sayıda boşluk içeren bir dize oluşturmak için Boşluk işlevlerini veya karakterleri ANSI koduna çevirmek için Asc işlevlerini içerir. Hepsi yaygın olarak kullanılır ve Excel'de satırlarla çalışmanıza izin vererek, bu tablolarla çalışmayı büyük ölçüde kolaylaştıran uygulamalar oluşturur.
• Veri türü dönüştürme işlevleri. Örneğin CVar, Değişken veri türüne dönüştürerek İfade bağımsız değişkeninin değerini döndürür.
• Tarihlerle çalışmak için işlevler. Standart olanları önemli ölçüde genişletirler.Örneğin, WeekdayName işlevi, numarasına göre haftanın gününün adını (tam veya kısmi) döndürür. Zamanlayıcı daha da kullanışlıdır. Gece yarısından günün belirli bir noktasına kadar geçen saniye sayısını verir.
• Sayısal bir argümanı farklı sayı sistemlerine dönüştürmek için işlevler. Örneğin, Oct, sekizli gösterimde bir sayı verir.
• Biçimlendirme işlevleri. Bunlardan en önemlisi Formattır. Biçim açıklamasında verilen talimatlara göre biçimlendirilmiş bir ifadeye sahip bir Varyant döndürür.
• vesaire.

Bu işlevlerin özelliklerini ve uygulamalarını incelemek, Excel'in kapsamını önemli ölçüde genişletecektir.

Örnek 5

Daha karmaşık sorunları çözmeye devam etmeye çalışalım. Örneğin:

İşletmenin gerçek maliyet seviyesinin raporunun bir kağıt belgesi verildi. Gerekli:

• şablon kısmını Excel elektronik tablo işlemcisi aracılığıyla geliştirmek;
• İlk verileri doldurmak için talep edecek bir VBA programı oluşturun, gerekli hesaplamaları yapın ve bunları şablonun ilgili hücrelerine doldurun.

Çözümlerden birini düşünelim.

Şablon oluşturma

Tüm eylemler standart bir Excel sayfasında gerçekleştirilir. Serbest hücreler, tüketici şirketinin adı, maliyet miktarı, seviyeleri ve cirosu hakkında veri girmek için ayrılmıştır. Raporun düzenlendiği firma (firma) sayısı sabit olmadığı için, sonuçlar için değerlerin girileceği hücreler ve uzmanın tam adı önceden rezerve edilmez. Çalışma sayfasına yeni bir ad verilir. Örneğin, "Օ raporları".

Değişkenler

Şablonun otomatik olarak doldurulması için bir program yazmak için atamaları seçmelisiniz. Değişkenler için kullanılacaklar:

• NN - tablonun geçerli satırının numarası;
• TP ve TF - planlı ve fiili ciro;
• SF ve SP - fiili ve planlanan maliyetler;
• IP ve IF - planlanan ve gerçek maliyet seviyesi.

Aynı harflerle belirtelim, ancak "ön ek" Itog ile bu sütun için toplamın birikimi. Örneğin, ItogTP - "Öngörülen Ciro" başlıklı bir tablo sütununu ifade eder.

VBA programlama kullanarak bir sorunu çözme

Tanıtılan tanımlamaları kullanarak sapmalar için formüller elde ederiz. % olarak hesaplamak isterseniz, elimizde (F - P) / P * 100 ve toplamda - (F - P) var.

Bu hesaplamaların sonuçları en iyi şekilde doğrudan Excel elektronik tablosundaki uygun hücrelere girilebilir.

Olgu ve tahminle ilgili sonuçlar için, ItogP = ItogP + P ve ItogF = ItogF + F formülleri kullanılarak elde edilirler.

Sapmalar için, hesaplama yüzde olarak yapılırsa = (ItogF - ItogP) / ItogP * 100 kullanın ve toplam değer durumunda - (ItogF - ItogP).

Sonuçlar yine hemen uygun hücrelere yazılır, bu nedenle bunları değişkenlere atamaya gerek yoktur.

Oluşturulan programı başlatmadan önce çalışma kitabını örneğin "Report1.xls" adı altında kaydetmeniz gerekir.

Başlık bilgileri girildikten sonra "Raporlama tablosu oluştur" butonuna sadece 1 kez basılması gerekmektedir. Dikkat edilmesi gereken başka kurallar da var. Özellikle her aktivite türü için değerleri tabloya girdikten sonra her seferinde "Satır Ekle" butonuna basılmalıdır. Tüm verileri girdikten sonra "Bitir" düğmesine tıklamanız ve ardından "Excel" penceresine geçmeniz gerekir.

Artık makroları kullanarak Excel görevlerini nasıl çözeceğinizi biliyorsunuz. Şu anda en popüler metin editörü "Word" ortamında çalışmak için vba excel kullanma yeteneği (yukarıdaki program örneklerine bakın) da gerekli olabilir. Özellikle yazının en başında gösterildiği gibi yazarak veya kod yazarak menü butonları oluşturabilirsiniz, bu sayede metin üzerinde bir çok işlem görevdeyken veya Görünüm sekmesi üzerinden butonlara basılarak yapılabilir. Makrolar simgesi.

Microsoft Excel makroları, bu elektronik tablo düzenleyicideki belgelerle çalışmayı önemli ölçüde hızlandırabilir. Bunu, özel kodla yazılmış tekrarlayan eylemleri otomatikleştirerek yapar. Excel'de makroların nasıl oluşturulacağına ve bunları nasıl düzenleyebileceğinize bir göz atalım.

Bir makro iki şekilde yazılabilir:

• otomatik olarak;
• manuel olarak.

İlk seçeneği kullanarak, belirli bir zamanda gerçekleştirdiğiniz belirli eylemleri Microsoft Excel'e kaydetmeniz yeterlidir. Ardından, bu kaydı oynatabilirsiniz. Bu yöntem çok kolaydır ve kod bilgisi gerektirmez, ancak pratikte uygulaması oldukça sınırlıdır.

Aksine, makroların manuel olarak kaydedilmesi, kod klavyeden manuel olarak yazıldığından programlama bilgisi gerektirir. Ancak bu şekilde iyi yazılmış kod, işlemlerin yürütülmesini önemli ölçüde hızlandırabilir.

Otomatik makro kaydı

Makroların otomatik kaydını başlatmadan önce yapmanız gerekir.

Makro kayıt ayarları penceresi açılır. Varsayılanı size uymuyorsa, burada makro için herhangi bir ad belirleyebilirsiniz. Ana şey, bu ismin bir sayı ile değil bir harfle başlamasıdır. Ayrıca başlık boşluk içermemelidir. Varsayılan adı bıraktık - "Macro1".

İsterseniz hemen bir klavye kısayolu ayarlayabilirsiniz, basıldığında makro çalışacaktır. İlk tuş Ctrl tuşu olmalı ve ikinci tuş kullanıcının kendisi tarafından belirlenir. Örneğin M tuşunu örnek olarak belirledik.

Ardından, makronun nerede saklanacağını belirlemeniz gerekir. Varsayılan olarak aynı kitapta (dosyada) saklanır, ancak dilerseniz depolamayı yeni bir kitapta veya ayrı bir makro kitabında ayarlayabilirsiniz. Varsayılanı bırakacağız.

Makro ayarlarının en alt alanında, bu makronun bağlama uygun herhangi bir açıklamasını bırakabilirsiniz. Ancak bu gerekli değildir.

Tüm ayarlar yapıldığında, "Tamam" düğmesine tıklayın.

Bundan sonra, bu Excel çalışma kitabındaki (dosya) tüm eylemleriniz, siz kendiniz kaydetmeyi bırakana kadar bir makroda kaydedilecektir.

Örneğin, en basit aritmetik işlemi yazalım: üç hücrenin içeriğini toplamak (= C4 + C5 + C6).

Bundan sonra, "Kaydı durdur" düğmesine tıklayın. Bu düğme, kayıt etkinleştirildikten sonra "Makro Kaydet" düğmesinden dönüştürülür.

Makro çalıştırma

Kaydedilen makronun nasıl çalıştığını kontrol etmek için aynı “Kod” araç kutusundaki “Makrolar” butonuna tıklayın veya Alt + F8 tuş kombinasyonuna basın.

Bundan sonra, kayıtlı makroların listesini içeren bir pencere açılır. Kaydettiğimiz makroyu arıyoruz, seçin ve "Çalıştır" düğmesine tıklayın.

Bunu daha da kolay yapabilir ve makro seçim penceresini bile çağıramazsınız. Hızlı bir şekilde makro çağırmak için bir "kısayol tuşları" kombinasyonu kaydettiğimizi hatırlıyoruz. Bizim durumumuzda, bu Ctrl + M'dir. Bu kombinasyonu klavyede yazıyoruz, ardından makro başlıyor.

Gördüğünüz gibi, makro daha önce kaydedilen tüm eylemleri tam olarak gerçekleştirdi.

Makro düzenleme

Makroyu düzenlemek için tekrar "Makrolar" butonuna tıklayın. Açılan pencerede gerekli makroyu seçin ve "Değiştir" düğmesine tıklayın.

Makroların düzenlendiği ortam olan Microsoft Visual Basic'i (VBE) açar.

Her makro kaydı bir Alt komutuyla başlar ve bir Alt Komutu Sonlandır komutuyla biter. Makronun adı, Sub komutundan hemen sonra belirtilir. "Aralık (" ... ") operatörü, bir hücrenin seçimini belirtir. Örneğin, "Aralık (" C4 "). Seç" komutu C4 hücresini seçer. Operatör "ActiveCell.FormulaR1C1" formüllerdeki eylemleri kaydetmek ve diğer hesaplamalar için kullanılır.

Makroyu biraz değiştirmeye çalışalım. Bunu yapmak için makroya bir ifade ekleyin:

Aralık ("C3").
ActiveCell.FormulaR1C1 = "11"

"ActiveCell.FormulaR1C1 =" = R [-3] C + R [-2] C + R [-1] C "" ifadesinin yerine "ActiveCell.FormulaR1C1 =" = R [-4] C + R [ -3 ] C + R [-2] C + R [-1] C "".

Düzenleyiciyi kapatın ve makroyu geçen seferki gibi çalıştırın. Gördüğünüz gibi, yaptığımız değişiklikler nedeniyle, veri içeren başka bir hücre eklendi. Ayrıca toplam hesaplamaya dahil edildi.

Makro çok büyükse, yürütülmesi uzun zaman alabilir. Ancak kodda manuel değişiklikler yaparak süreci hızlandırabiliriz. "Application.ScreenUpdating = False" komutunu ekleyin. Bilgi işlem gücünden tasarruf etmenizi sağlar ve bu nedenle işinizi hızlandırır. Bu, hesaplama adımları sırasında ekranın güncellenmemesiyle gerçekleştirilir. Makroyu çalıştırdıktan sonra güncellemeye devam etmek için sonuna "Application.ScreenUpdating = True" komutunu yazıyoruz.

Ayrıca kodun başına "Application.Calculation = xlCalculationManual" komutunu, kodun sonuna da "Application.Calculation = xlCalculationAutomatic" ekleyelim. Bunu yaparak makronun başında her hücre değişikliğinden sonra sonucun otomatik olarak yeniden hesaplanmasını kapatıyoruz ve makro sonunda açıyoruz. Böylece, Excel sonucu yalnızca bir kez hesaplayacak ve sürekli olarak yeniden hesaplamayacaktır, bu da zamandan tasarruf sağlayacaktır.

Sıfırdan makro kodu yazma

Gelişmiş kullanıcılar yalnızca kayıtlı makroları düzenleyip optimize etmekle kalmaz, aynı zamanda sıfırdan makro kodu da yazabilir. Bunu başlatmak için geliştirici şeridinin en başında bulunan "Visual Basic" düğmesine tıklamanız gerekir.

Bundan sonra tanıdık VBE düzenleyici penceresi açılır.

Programcı makro kodunu oraya manuel olarak yazar.

Gördüğünüz gibi, Microsoft Excel'deki makrolar, rutin ve tekrarlayan işlemlerin yürütülmesini önemli ölçüde hızlandırabilir. Ancak, çoğu durumda, otomatik olarak kaydedilen eylemler yerine kodu elle yazılan makrolar bunun için daha uygundur. Ek olarak, görev yürütme sürecini hızlandırmak için makro kodu VBE düzenleyicisi aracılığıyla optimize edilebilir.

Excel'in kullanışlı özellikleri üzerine bir defter olarak bu siteyi oluşturmaya başladım. İstediğiniz zaman siteye giderek programın belirli bir özelliği hakkındaki bilgilerinizi tazeleyebilir, ayrıca Excel'i kullanarak yardım isteyen arkadaşlarınıza veya iş arkadaşlarınıza makalenin bağlantısını gönderebilirsiniz. Sitenin izleyici kitlesi oldukça hızlı bir şekilde büyüdü ve gördüğüm kadarıyla kaydedilen bilgiler sadece bana ve birkaç kişiye değil, tanımadığım birçok kişiye de faydalı oluyor ve arama motorlarında yüksek puanlar alıyor. Excel'in yeteneklerine güveniyorsam, kendime çok üst düzey bir VBA uzmanı diyemem. Bu nedenle sıklıkla kullandığım faydalı makroları yazacağım harika bir yardımcı makale oluşturmak istiyorum. Böyle bir VBA özellikleri koleksiyonunun sadece benim için yararlı olmayacağından eminim.

Sitede VBA da dahil olmak üzere zaten birkaç makale var, ancak sıklıkla kullanılan kod gadget'larını tek bir yerde tutmanın faydalı olacağını düşünüyorum.

Basit bir işlevle ekranda makro eylemlerin görüntülenmesini kapatın. Bu, yalnızca görsel performansı iyileştirmekle kalmayacak, aynı zamanda makronun yürütülmesini de önemli ölçüde hızlandıracaktır.

Sub Makros1 () Application.ScreenUpdating = 0 "Kodunuz Application.ScreenUpdating = 1 End Sub

Makronun sonuna işlevi eklemeyi unutmayın

Makro yürütüldükten sonra kopya seçimi nasıl kaldırılır?

Hepiniz makronuzda böyleyseniz, kopyalama ve yapıştırma için bir aralık ayırın. Makronun sonunda seçimi kapatmayı unutmayın

Sub Makros1 () "Kodunuz Application.CutCopyMode = 0 End Sub

Kullanışlı makrolar. Bir aralığın son satırı veya sütunu nasıl bulunur?

Bu yapı, doldurulmuş aralığın son satırının veya sütununun numarasını kolayca bulmanıza yardımcı olacaktır. Döngülerde kullanmak özellikle uygundur, bir kenar boşluğu ile 1000 döngü satırı belirtmeniz gerekmez, Excel, aralığın sonunun aşağıdaki yapıyı kullandığı yerde kendini bulur:

Alt makros1 () Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row "Dolu aralığın son satır numarasını bulur nLastCol = Cells (1, Columns.Count) . End (xlToLeft ) .Column "En son doldurulan sütunun numarasını End Sub aralığında bulur

Ayrıca, popüler Tamsayı'nın büyük tablolar için yeterli olmayabileceği (32.767) bir duruma girmemek için hemen değişkeni Uzun (uzunluk 2.147.483.647) olarak ilan ediyorum.

Ayrıca, aramanın ilk sütunda veya ilk satırda gerçekleştiğine dikkat edilmelidir, doğru tanımlama için istediğiniz sayıyı seçin.

Döngü İçin ve Döngüdeki Bir Koşulu Kontrol Etme

Aralıkların son satır ve sütun numaralarını bulursanız, bunları döngülerde kullanabilirsiniz. Örneğin, ilk sütunu gözden geçirmeniz ve boş hücrelerin sayısını saymanız gerekir.

Alt makros1 () Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Değişken miktar Dim i As Long" Döngü değişkeni mLastRow = Hücreler (Rows.Count, 1) .End (xlUp) .Row Kol = 0 For i = 1 mLastRow If Hücreler (i, 1) .Value = "" Sonra Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

Burada Msgbox da bu özelliği ile kullanılır, verileri ayrı bir pencerede görüntüleyebilirsiniz. Benim örneğim için şöyle görünecek:

Bir makronun yürütme süresini sayma

Alt makros1 () TimeStart = Now TimeFinish = Now MsgBox "Time:" & Format (TimeFinish - TimeStart, "s: aa: ss") End Sub

MsgBox şu çıktıyı üretir:

Sonraki optimizasyon için makroların yürütme süresini izlemek çok uygundur.

Bunlar çok sık kullandığım bazı yararlı makrolar. Bu listeyi yavaş yavaş genişleteceğim. Tüm etkili otomasyon!

Makalemizi sosyal ağlarınızda paylaşın:

Etkin çalışma kitabındaki her sayfada A1 hücresini vurgulamak için makro. Bu da ekranı hareket ettirir.

Sub A1SelectionEachSheet () Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets (i) .Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Aralık ("a1"). Sonraki Sayfaları Seç (1) .Uygulama Seçin.ScreenUpdating = True End Sub

Geçerli sayfayı belirli sayıda kopyalamak için makro. Bazı makroları test etmek için kullanışlıdır - yapılan düzenlemeler, verilerin bir kopyası üzerinde kontrol edilmiştir. Kopya tükendi - makroyu yeniden çalıştırın

Sub SimpleCopy () Dim i As Integer, j As Integer i = Application.InputBox Application.ScreenUpdating = False For j = 1 için i ActiveSheet.Copy after: = Sheets (Sheets.Count) ActiveSheet .Name = "Copy" & j Sonraki j Application.ScreenUpdating = True End Sub

Bir sayfada belirli bir aralıktaki başlıklara sahip sayfalar oluşturun

Sub CreateFromList () Seçim Sayfalarında Her hücre için Aralık Olarak Hücreyi Dim.Add after: = Sheets (Sheets.Count) ActiveSheet.Name = cell.Value Sonraki hücre End Sub

Gecikmeli posta gönderme işaretleri. John Walkenbach'ın Profesyonel VBA Programlama kitabından değiştirilmiş makro

Alt E-posta () OutApp As Nesne Olarak Dim OutMail Nesne Olarak Dim OutApp = CreateObject ("Outlook.Application") OutApp.Session.Logon On Error GoTo temizleme Set OutMail = OutApp.CreateItem (0) Hatada OutMail İle Devam Et Devam Et .To = " [e-posta korumalı]".Subject =" Satış raporu ".Attachments.Add" C: \ Test.txt ".Body =" E-posta gövdesi ".DeferredDeliveryTime = Değiştir (Tarih,". "," / ") &" 11:00:00 " .send" .Display Bir mesaj oluşturmak ve onu açmak için End With Error GoTo 0 Set OutMail = Hiçbir şey temizleme: Set OutApp = Hiçbir Şey End Sub

Nikolai Pavlov'dan biraz değiştirilmiş içindekiler makrosu.
Kitapta "İçindekiler" sayfası zaten varsa, makro onu silmenizi önerir. Değilse, bir "İçindekiler" sayfası oluşturur ve sayfa adlarıyla bağlantılar ekler

Sub TableOfContent () Dim sayfa Çalışma Sayfası Olarak Dim hücre Aralık Dim Olarak Yanıtla Tamsayı Application.ScreenUpdating = ActiveWorkbook ile Yanlış ActiveWorkbook.Worksheets'teki Her Çalışma Sayfası İçin Eğer Worksheet.Name = "İçindekiler" Sonra Cevap = MsgBox ("Çalışma kitabında bir İçindekiler Tablosu adlı sayfa. Silinsin mi? ", VbYesNo) Eğer Cevap = vbNo ise, Cevap = vbYes ise Sub'dan çıkın. Sayfaları Seçin. Sayfa Ekle (1) .Name = ActiveWorkbook ile "İçindekiler Tablosu" ActiveWorkbook'taki Her Sayfa İçin.<>"İçindekiler" Ardından Hücreyi Ayarla = Çalışma Sayfaları (1) .Hücreler (sayfa.İndeks, 1) .Çalışma Sayfaları (1) .Köprüler.Çapa ekle: = hücre, Adres: = "", AltAdres: = "" "& sayfa .Name & "" "&"! A1 "hücre.Formula = sayfa.Ad Sonu Sonraki Sayfa Satırlarla Biterse (" 1: 1").Delete Application.ScreenUpdating = True End Sub

VBA sihirbazlarından sayfaları sıralayın. Makro ayrıca gizli sayfaları da sıralar. Kitap korumalı bir yapıya sahipse çalışmaz

Sub SORT_ALL_LETS () Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject ("Scripting.Dictionary") "her sayfanın görünürlük durumunu hatırla ve yap görünen her şey ActiveWorkbook.Sheets oDict.Item (iSht.Name) = iSht.Visible: iSht.Visible = True Next with ActiveWorkbook "görünür sayfaları sırala For i = 1 To .Sheets.Count - 1 For j = i + 1 .Sayfalar.Sayım If UCase (.Sayfalar (i) .Ad)> UCase (.Sayfalar (j) .Ad) Sonra .Sayfalar (j) .Önceden Taşı: =. Sayfalar (i) Sonraki j Sonraki i Bitiş " sayfaların her birinin orijinal görünürlük durumunu geri yükle ActiveWorkbook.Sheets'teki Her iSht için iSht.Visible = oDict.Item (iSht.Name) Sonraki Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

"C: \ Manager.xls" Excel dosyasının "Sayfa1" sayfasından "Alan1" ve "Alan2" sütunlarının ADODB bağlantısı aracılığıyla içe aktarılması ve geçerli sayfanın A1 hücresinden başlayarak içeriğin yapıştırılması

Tekrarlanan görevleri otomatikleştirmek için Microsoft Excel'deki Makro Kaydediciyi kullanarak bir makro kaydedebilirsiniz. Rastgele biçimlerde tarihleriniz olduğunu ve hepsine tek bir biçim uygulamak istediğinizi hayal edin. Bu bir makro kullanılarak yapılabilir. İstediğiniz formatı kullanarak bir makro kaydedebilir ve gerektiğinde makroyu yeniden oynatabilirsiniz.

Bir makro kaydettiğinizde yaptığınız her şey Visual Basic for Applications (VBA) kodu olarak kaydedilir. Bu eylemler, metin veya sayı girmeyi, şerit veya menülerdeki hücreleri veya komutları seçmeyi, hücreleri, satırları veya sütunları biçimlendirmeyi ve hatta Microsoft Access gibi harici bir kaynaktan veri almayı içerebilir. Visual Basic for Applications (VBA), Visual Basic programlama dilinin bir parçasıdır. Çoğu Office uygulamasında bulunur. VBA, Office uygulamaları içindeki ve arasındaki işlemleri otomatikleştirmenize izin verse de, programlama konusunda uzman olmanıza veya VBA dilini bilmenize gerek yoktur, çünkü makro kaydedici yapmanız gereken her şeyi halledecektir.

Bir makro kaydettiğinizde, yaptığınız hemen hemen her şeyin günlüğe kaydedildiğini bilmek önemlidir. Bu nedenle, yanlış düğmeye tıklamak gibi bir hata yaparsanız, makro kaydedici bu eylemi günlüğe kaydeder. Bu durumda, tüm sırayı yeniden yazabilir veya VBA kodunu değiştirebilirsiniz. Bu nedenle, süreci kaydetmeden önce iyi çalışmalısınız. Sıralamayı ne kadar doğru kaydederseniz, makro o kadar verimli çalışacaktır.

Geliştirici varsayılan olarak gizlidir, bu nedenle önce etkinleştirmeniz gerekir. Daha fazla bilgi için Geliştirici Sekmesini Görüntüleme makalesine bakın.

makro kayıt

sekmesinde Geliştirici Tıklayın makrolarçalışma kitabıyla ilişkili makroları görüntülemek için Alternatif olarak, tuşlara basabilirsiniz. ALT + F8... Bu bir iletişim kutusu açacak Makro.

Dikkat:

		Makroların güvenlik parametreleri ve anlamları hakkında bilgi edinin.
	Makrolar, klavye kısayolu, grafik nesnesi, Hızlı Erişim Araç Çubuğu, bir düğme veya hatta bir çalışma kitabını açtığınızda olduğu gibi çeşitli şekillerde çalıştırılabilir.
	Visual Basic Düzenleyicisi'ni kullanarak, çalışma kitabına eklenen makroları değiştirebilirsiniz.
	makro ata. alanında Makro ata
	Office dosyalarında makroları nasıl açıp kapatacağınızı öğrenin.
	tuşlara basın ALT + F11.

Visual Basic Düzenleyicisi'nde (VBE) kayıtlı kodla çalışma

Visual Basic Düzenleyicisi (VBE) ile, makro kaydedicinin desteklemediği kayıtlı koda kendi değişkenlerinizi, kontrol yapılarınızı ve diğer öğeleri ekleyebilirsiniz. Makro kaydedici, kayıt sırasında gerçekleşen hemen hemen her adımı yakaladığından, gereksiz kodu da kaldırmanız gerekebilir. Kaydedilen kodu gözden geçirmek, VBA'da nasıl programlanacağını öğrenmenin veya becerilerinizi geliştirmenin harika bir yoludur.

Kaydedilen kodu değiştirmeye ilişkin bir örnek için bkz. Excel'de VBA'yı kullanmaya başlama.

makro kayıt

Makroları kaydetmeden önce aşağıdakileri bilmek faydalıdır:

Bir Excel aralığıyla çalışmak üzere yazılmış bir makro, yalnızca o aralıktaki hücrelerde çalışır. Bu nedenle, aralığa yeni bir satır eklerseniz, makro buna uygulanmaz.

Uzun bir görev dizisi kaydetmeniz gerekiyorsa, bunun yerine birkaç küçük makro kullanmanızı öneririz.

Makro, Excel ile ilgili olmayan görevleri de içerebilir. Makro işlemi, diğer Office uygulamalarını ve Visual Basic for Applications'ı (VBA) destekleyen diğer programları kapsayabilir. Örneğin, önce Excel'de bir tabloyu güncelleyen ve ardından e-postayla göndermek için Outlook'u açan bir makro kaydedebilirsiniz.

VBA makroları ve araçları sekmede Geliştirici varsayılan olarak gizlidir, bu nedenle önce etkinleştirmeniz gerekir.

Lütfen seçin Excel > Seçenekler> Şerit ve Panel.

Bir makro kaydetmek için aşağıdaki talimatları izleyin.

Excel'de kayıtlı makrolarla çalışma

sekmesinde Geliştirici Tıklayın makrolarçalışma kitabıyla ilişkili makroları görüntülemek için Bu bir iletişim kutusu açacak Makro.

Not: Makrolar iptal edilemez. Kaydedilen makroyu ilk kez çalıştırmadan önce, istenmeyen değişiklikleri önlemek için çalışma kitabının bir kopyasını kaydedin veya oluşturun. Makro yürütmenin sonuçlarından memnun değilseniz, çalışma kitabını kaydetmeden kapatabilirsiniz.

Excel'de makrolarla çalışma hakkında daha fazla bilgi için aşağıya bakın.

		Mac için Excel'de makroları nasıl açıp kapatacağınızı öğrenin.
	Sık tekrarlanan görevlerde zaman kazanmak için, karşılık gelen eylem sırasını bir makro biçiminde kaydedebilirsiniz. Makroları nasıl oluşturacağınızı ve çalıştıracağınızı öğrenin.
	Çalışma kitabı başka bir yerde kullanmak istediğiniz bir VBA makrosu içeriyorsa, Microsoft Visual Basic Düzenleyicisi'ni kullanarak modülü başka bir çalışma kitabına kopyalayabilirsiniz.
Bir nesneye, şekle veya grafiğe makro atama	Sayfada, varolan bir makroyu atamak istediğiniz nesneye, resme, şekle veya öğeye sağ tıklayın ve ardından makro ata. alanında Makro ata atamak istediğiniz makroyu seçin.
	Bir simgeye bir makro atayabilir ve bunu Hızlı Erişim Araç Çubuğuna veya Şeride ekleyebilirsiniz.
	Bir sayfadaki formlara ve ActiveX denetimlerine makro atayabilirsiniz.
Visual Basic Düzenleyicisini Açma	sekmesinde Geliştirici Tıklayın Visual Basic veya seçin Hizmet > Makro > Visual Basic Düzenleyici.
	Visual Basic öğeleri için nasıl yardım bulacağınızı öğrenin.

ek bilgi

Excel Teknik Topluluğuna her zaman bir soru sorabilir, Yanıtlar topluluğundan yardım isteyebilir veya web sitesinde yeni bir özellik veya iyileştirme önerebilirsiniz.