کتابخانه کد VBA Excel – ماکروهای مفید برای مبتدیان
VBA یا ماکرو در اکسل
کدهای ماکرو اکسل می توانند برای خودکارسازی کارهای عادی اکسل و جلوگیری از انجام دستی آن استفاده شوند. این باعث صرفه جویی در زمان و کار قابل اطمینان تر می شود.
این کتابخانه کد VBA لیستی از کدهای مفید VBA برای اکسل است که می توانید بلافاصله استفاده از آنها را شروع کنید.
این کدها برای مبتدیانی که قبلا از Excel VBA استفاده نکرده‌اند ، یا تازه شروع به کار کرده‌اند، عالی هستند .
اجازه دهید ابتدا توضیح دهیم که کد ماکرو چیست و چگونه می توانید از آنها استفاده کنید. سپس به قسمت هیجان انگیز می رسیم و نمونه های ماکرو اکسل را در این لیست بررسی می کنیم.
کد ماکرو چیست؟
کد ماکرو به کد VBA (Visual Basic for Applications) اشاره داره که برای ماکروها استفاده می‌شه.
شما می‌تونید در اکسل ماکروها رو با ضبط کردن مراحل مورد نظرتون ایجاد کنید (در این صورت VBA به‌طور خودکار نوشته می‌شه) یا می‌تونید خودتون کد VBA رو بنویسید.
نوشتن کد VBA یا ماکرو به‌صورت دستی به شما کنترل بیشتری روی ماکروهاتون می‌ده.
اVBA می‌تونه خیلی ساده باشه و کارهای پایه‌ای مثل فرمت‌بندی یا قفل کردن پنجره‌ها رو انجام بده. یا می‌تونه کارهای بزرگ‌تری مثل خروجی گرفتن از تمام شیت‌های یک فایل اکسل به‌عنوان PDFهای جداگانه رو انجام بده.
این کتابخانه کد VBA، فهرستی از کدهای ماکرو رو ارائه می‌ده که می‌تونید کپی و پیست کنید تا کارتون رو شروع کنید.
این کار هم از نیاز به نوشتن کدها به‌طور دستی صرفه‌جویی می‌کنه و هم راه خوبی برای شروع یادگیری VBA هست.
نشان داده تب Developer
برای استفاده از کدهای VBA، نیاز به دسترسی به تب Developer (توسعه‌دهنده) دارید.
اگر تب Developer در نوار ابزار شما وجود نداره، می‌تونید با انجام مراحل زیر اون رو فعال کنید:
1. روی “File” کلیک کنید و بعد “Options” رو انتخاب کنید.
2. دسته “Customize Ribbon” رو انتخاب کنید و سپس گزینه مربوط به تب Developer رو تیک بزنید.
3. روی “OK” کلیک کنید.
نحوه استفاده از کد ماکرو
استفاده از کدهای ماکرو فقط به یک کپی و پیست ساده نیاز داره. با این حال، باید اطمینان حاصل کنید که آنها را در محل صحیح بگذارید.
1.روی Developer > Visual Basic کلیک کنید یا Alt + F11 را فشار دهید .
ویرایشگر Visual Basic باز می‌شه. در سمت چپ، پنجره Project Explorer نمایش داده می‌شه که لیست فایل‌های باز و پروژه‌های دیگه رو نشون می‌ده.
2. داخل کاربک‌هایی که می‌خواید کد VBA رو وارد کنید، کلیک کنید و بعد از منوی “Insert” گزینه “Module” رو انتخاب کنید.
3. کد مورد نظر از این مقاله رو کپی کنید و در پنجره کد پیست کنید.
حالا ماکرو برای اجرا در پنجره ماکروها در دسترس خواهد بود.
4. روی “Developer” کلیک کنید و سپس “Macros” رو انتخاب کنید. ماکروی مورد نظر رو از لیست انتخاب کنید و روی “Run” کلیک کنید.
در این کتابخانه کد VBA، یک دسته به نام Events وجود داره. این ماکروها در ماژول‌ها ذخیره نمی‌شن، بلکه در شیئی که رویداد در اون اتفاق می‌افته، ذخیره می‌شن.
این موضوع وقتی کدها رو معرفی کنیم بیشتر توضیح داده می‌شه. بیشتر ماکروهای موجود در این لیست در ماژول‌ها ذخیره می‌شن و طبق روال توضیح داده شده بالا عمل می‌کنن.
اضافه کردن دکمه برای فعال کردن ماکروهای شما
اگرچه می‌تونید ماکرو رو از پنجره ماکروها اجرا کنید، اما اختصاص دادن ماکرو به یک دکمه که بتونید روی اون کلیک کنید، کار رو راحت‌تر می‌کنه.
شما می‌تونید دکمه‌ها رو در نوار ابزار دسترسی سریع (جایی که دکمه‌های ذخیره و بازگشت وجود دارن)، در نوار ابزار Ribbon یا روی یک شیت اکسل اضافه کنید.
روش انتخابی شما بستگی به این داره که ماکرو چه کاری انجام می‌ده و کی می‌خواید اون رو اجرا کنید.
برای این مثال، نحوه اضافه کردن دکمه به نوار ابزار دسترسی سریع برای اجرای ماکروهاتون رو نشون می‌دیم.
1. روی فلش “Customize Quick Access Toolbar” کلیک کنید و “More Commands” رو انتخاب کنید.
2. روی فلش فهرست “Popular Commands” کلیک کنید و گزینه “Macros” رو انتخاب کنید. سپس ماکروی مورد نظرتون رو انتخاب کرده و روی “Add” کلیک کنید.
3. دکمه “Modify” کلیک کنید تا تصویر و نام نمایشی دکمه رو تغییر بدید.
این دکمه در نوار ابزار دسترسی سریع ظاهر می شود و با کلیک روی ماکرو شما اجرا می شود.
هنگامی که ماوس خود را روی دکمه قرار می دهید، نام ماکرو نمایش داده می شود.
نمونه‌های کد ماکرو در این فهرست به دسته‌هایی تقسیم شده‌اند تا راحت‌تر آنها را پیدا کنید و از آنها استفاده کنید.
نمونه کدهای VBA اکسل
وظایف متداول در اکسل
*تنظیم خودکار عرض ستون‌ها(AutoFit)
*کپی و پیست
*پاک کردن تمام هایپرلینک‌ها در یک شیت
*فرمت‌بندی سلول‌ها با فرمول‌ها
*تبدیل فرمول‌ها به مقادیر
کدهای شیت
*نمایش همه ستون‌ها
*محافظت از یک شیت
*عبور از تمام شیت‌های یک فایل اکسل
کدهای فایل اکسل
*نمایش همه شیت‌ها
*محافظت از یک فایل اکسل
*باز کردن و بستن یک فایل اکسل
*ایمیل کردن فایل اکتیو با Outlook
فایل‌ها و پوشه‌ها
*خروجی گرفتن از هر شیت به‌صورت PDF جداگانه
*گرفتن خروجی از شیت فعال به‌صورت PDF
*خروجی گرفتن از چند شیت به‌صورت یک PDF واحد
*عبور از تمام فایل‌های یک پوشه
*انتخاب یک فایل با FileDialog
ویژگی‌های مفید اکسل
*مرتب‌سازی ستون‌ها
*فیلتر کردن داده‌ها
*ایجاد نمودار
رویدادها
*رفتن به یک شیت خاص در زمان باز شدن
*انجام یک عمل در زمان تغییر سلول
وظایف متداول در اکسل
این کدهای VBA وظایف متداول اکسل را به‌سرعت انجام می‌دهند.
تنظیم خودکار عرض ستون‌ها
این کد به‌طور خودکار عرض تمام ستون‌های یک شیت را تنظیم می‌کند:
Sub AutofitAllColumns()
Cells.EntireColumn.AutoFit
End Subاین کد به‌طور خودکار عرض ستون‌های خاصی در یک شیت را تنظیم می‌کند. در این مثال، ستون‌های D و F:
Sub AutofitSpecificColumns()
Range("D:D,F:F").EntireColumn.AutoFit
End Subکپی و پیست
کپی و پیست یکی از رایج‌ترین اقدام‌ها در اکسل است و می‌توان آن را با یک خط کد VBA نوشت.
این کد، محدوده A1
را به A1 یک شیت دیگر کپی می‌کند:
Sub CopyAndPaste()
Range("A1:B6").Copy Worksheets("Sheet2").Range("A1")
End Subاگر شما محدوده مشخصی برای پیست کردن محتوا ندارید و معمولاً می‌خواهید داده‌های کپی شده را به انتهای یک لیست دیگر اضافه کنید، می‌توانید از کد زیر استفاده کنید:
این کد، محدوده استفاده‌شده اطراف سلول A2 را کپی کرده و آن را به اولین سلول خالی در انتهای ستون A در شیتی به نام “Archive” پیست می‌کند:
Sub CopyAndPaste()
Range("A2").CurrentRegion.Copy Worksheets("Archive").Range("A1").End(xlDown).Offset(1, 0)
End Subاگر بخواهید از برخی از گزینه‌های خاص پیست در اکسل استفاده کنید، می‌توانید عملیات کپی و پیست را به دو خط کد جدا کنید.
این کد از متد PasteSpecial برای پیست کردن فقط مقادیر استفاده می‌کند:
Sub CopyAndPasteValues()
Range("A1:B6").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
End Subپاک کردن تمام هایپرلینک‌ها در یک شیت
کد ماکرو زیر، تمام هایپرلینک‌ها را از یک شیت پاک می‌کند:
Sub ClearHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Subفرمت‌بندی سلول‌ها با فرمول‌ها
فرمت‌بندی سلول‌هایی که شامل فرمول هستند، شناسایی آنها را در یک شیت آسان‌تر می‌کند. کد ماکرو زیر، سلول‌های حاوی فرمول را با رنگ پس‌زمینه زرد (ColorIndex = 6) فرمت‌بندی می‌کند:
این کد یه متغیر محدوده به اسم `rng` تعریف می‌کنه و با استفاده از اون، از بین هر سلولی که فرمول داره رد می‌شه.
Sub FormatFormulas()
Dim rng As Range
For Each rng In Cells.SpecialCells(xlCellTypeFormulas)
rng.Interior.ColorIndex = 6
Next rng
End Subتبدیل فرمول‌ها به مقادیر
یکی دیگه از اقدام‌های رایج با فرمول‌ها، تبدیل کردنشون به مقادیر هست. کد VBA زیر این کار رو برای تمام فرمول‌ها در شیت انجام می‌ده:
Sub ConvertFormulastoValues()
Dim rng As Range
For Each rng In Cells.SpecialCells(xlCellTypeFormulas)
rng.Formula = rng.Value
Next rng
End Subکد های شیت
این کدها برخی از وظایف معمولی شیت‌ها رو انجام می‌دن.
نمایش همه ستون‌ها
پنهان کردن ستون‌ها برای کاهش شلوغی شیت و محافظت از داده‌ها خیلی خوبه. کد ماکرو زیر با یک کلیک، تمام ستون‌های پنهان رو نمایش می‌ده:
Sub UnhideAllColumns()
Columns.EntireColumn.Hidden = False
End Subاز شیت محافظت کن
کد زیر، شیت فعالی که داری رو محافظت می‌کنه.
Sub ProtectWS()
ActiveSheet.Protect
End Subشاید بخوای این کار رو بیشتر ادامه بدی و یه رمز بذاری یا مشخص کنی که چه کارهایی مجاز هستن. این کد ماکرو، رمز ‘Excel’ رو تعیین می‌کنه و فقط اجازه می‌ده که ردیف‌ها اضافه بشن.
اگه با محافظت از شیت‌ها آشنا باشی، می‌دونی که کارهای مختلفی هستن که می‌تونی مجاز کنی یا جلوی انجامشون رو بگیری. ما می‌تونیم این کار رو با کد VBA انجام بدیم.
Sub ProtectWS()
ActiveSheet.Protect Password:=”Excel”, AllowInsertingRows:=True
End Subیکی از دلایل معمول برای محافظت از شیت، حفظ فرمول‌ها از آسیب‌های تصادفی هست. این کد فقط سلول‌هایی که فرمول دارن رو محافظت می‌کنه.
کد با غیرفعال کردن محافظت شیت و باز کردن همه سلول‌ها شروع می‌شه. بعدش سلول‌های حاوی فرمول رو قفل می‌کنه و در نهایت محافظت رو اعمال می‌کنه.
Sub ProtectFormulas()
With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect
End With
End Subحلقه‌ای برای مرور همه شیت‌های یک ورک‌بوک
تنظیم یک حلقه برای انجام یک کار روی همه شیت‌های یک ورک‌بوک خیلی ساده‌ست. می‌تونی هر کاری که نیاز داری رو داخلش بذاری.
در این مثال، شیت‌ها رو محافظت می‌کنیم. فقط کافیه خط ws.Protect رو با کارهایی که می‌خوای عوض کنی. وقتی به شیت اشاره می‌کنی، از متغیر ws استفاده کن.
Sub LoopAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect
Next ws
کد های ورک بوک
کدهای زیر برخی از کارهای معمولی روی ورک‌بوک رو انجام می‌دن.
نمایش همه شیت‌ها
در اکسل، فقط می‌تونی یک شیت رو در یک زمان نمایش بدی. بنابراین، این کد همه شیت‌ها رو با یک کلیک نمایش می‌ده.
Sub UnhideAllWorksheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Subمحافظت از ورک‌بوک
این کد، یک ورک‌بوک رو محافظت می‌کنه تا از تغییرات ساختاری جلوگیری کنه. یه رمز عبور تعیین می‌کنه، اما این کار اختیاریه و می‌تونی اون رو حذف کنی.
Sub ProtectWorkbook()
"ThisWorkbook.Protect Password:="Excel
End Subباز کردن و بستن یک ورک‌بوک
این کد یک روند رو دنبال می‌کنه تا داده‌ها رو از ورک‌بوک فعلی به یک ورک‌بوک به نام ‘North’ کپی کنه.
ابتدا، ورک‌بوک فعلی رو به یک متغیر اختصاص می‌ده، بعد ورک‌بوک ‘North’ رو باز می‌کنه تا داده‌ها رو از محدوده A1:C250 به اون کپی کنه. سپس این ورک‌بوک ذخیره و بسته می‌شه.
روش‌های زیادی برای ارجاع به ورک‌بوک‌ها وجود داره که چندتا از اون‌ها در این کد کوچک نشون داده شده.
Sub OpenCloseWorkbooks()
Dim wbk As Workbook
Set wbk = ActiveWorkbook
Workbooks.Open "C:\Users\Admin\OneDrive\Desktop\Sales\North.xlsx"
wbk.Sheets("Sheet1").Range("A1:C250").Copy Destination:=Range("A1")
ActiveWorkbook.Close SaveChanges:=True
End Subایمیل کردن ورک‌بوک فعال با Outlook
این کد VBA یک ایمیل جدید در Microsoft Outlook ایجاد می‌کنه که شامل آدرس ایمیل گیرنده، موضوع و متن بدنه است. ورک‌بوک فعال به عنوان ضمیمه اضافه می‌شه.
ایمیل نمایش داده می‌شه تا بتونی اون رو بررسی کنی و تغییرات لازم رو قبل از ارسال انجام بدی. اگر بخوای ایمیل رو با یک کلیک ارسال کنی، کافیه `.display` رو به `.send` تغییر بدی.
Sub AttachToEmail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = "admin@computergaga.com"
.Subject = "A Fabulous Spreadsheet"
.Body = "Hello, I hope you enjoy the fabulous spreadsheet that is attached to this email."
.Attachments.Add ActiveWorkbook.FullName
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Subفایل‌ها و پوشه‌ها
استفاده از VBA برای خودکار کردن کار با فایل‌ها و پوشه‌ها در حین یک فرآیند خیلی مفیده.
صادر کردن هر شیت به عنوان یک PDF جداگانه
این کد همه شیت‌ها رو به صورت PDF جداگانه صادر می‌کنه. نام شیت به عنوان نام فایل PDF استفاده می‌شه و فایل‌ها در پوشه‌ای که به متغیر `FolderPath` اختصاص داده شده، ذخیره می‌شن. فقط کافیه این مسیر رو به چیزی که نیاز داری تغییر بدی.
Sub ExportAsPDF()
Dim FolderPath As String
Dim ws As Worksheet
FolderPath = "C:\Users\Computergaga\Desktop\Sales"
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" &
ws.Name, openafterpublish:=False
Next
MsgBox "All PDF's have been successfully exported."
End Subصادر کردن شیت فعال به عنوان PDF
اگر فقط بخوای شیت فعال رو صادر کنی، به همین چند خط کد نیاز دارید.
Sub ExportAsPDF()
FolderPath = "C:\Users\Computergaga\Desktop\Sales"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" &
Activesheet.Name, openafterpublish:=False
End Subصادرات چند شیت به یک PDF واحد
شاید بخوای شیت‌ها رو در یک PDF واحد صادر کنی. این کد شیت‌های به نام‌های ‘London’ و ‘Berlin’ رو به یک فایل PDF صادر می‌کنه.
در این مثال، شیت‌ها با نامشون ارجاع داده می‌شن، اما می‌تونی از شماره ایندکس هم استفاده کنی. مثلاً، می‌تونی از Sheets(Array(3, 6)).Select استفاده کنی.
Sub ExportAsPDF()
Dim FolderPath As String
FolderPath = "C:\Users\Trainee1\Desktop\PDFs"
Sheets(Array("London", "Berlin")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", openafterpublish:=False, ignoreprintareas:=False
MsgBox "All PDF's have been successfully exported."
End Subحلقه زدن روی همه فایل‌های یک پوشه
این کد VBA هر فایل در پوشه فروش (Sales) روی دسکتاپ رو بررسی می‌کنه.
هر فایل باز می‌شه، عدد ۲۰ در سلول A1 شیت1 وارد می‌شه و سپس ذخیره و بسته می‌شه. این یک عملیات ساده‌ست، اما می‌تونی این کد رو با هر اقدام دیگه‌ای که می‌خوای روی هر فایل در پوشه انجام بدی، جایگزین کنی.
برای این فرآیند از تابع Dir استفاده می‌شه. اولین بار، تابع Dir با مسیر فایل استفاده می‌شه تا نام اولین فایل در پوشه رو بگیره.
سپس در انتهای حلقه دوباره استفاده می‌شه تا نام فایل بعدی رو بگیره.
Sub LoopAllFiles()
Dim fileName As Variant
fileName = Dir("C:\Users\admin\OneDrive\Desktop\Sales\")
Do While fileName <> ""
Workbooks.Open fileName
Worksheets("Sheet1").Range("A1").Value = 20
ActiveWorkbook.Close savechanges:=True
fileName = Dir
Loop
End Subمی‌تونی فقط فایل‌های خاصی رو با استفاده از کاراکترهای جایگزین (wildcards) حلقه بزنی. کد زیر می‌تونه به جای اولین استفاده از تابع Dir به کار بره و فقط فایل‌های .xlsx رو بررسی کنه.
fileName = Dir("C:\Users\admin\OneDrive\Desktop\Sales\*.xlsx")انتخاب یک فایل با FileDialog
وقتی با فایل‌ها کار می‌کنی، FileDialogها در Excel VBA خیلی مفیدن. این‌ها محیطی هستن که کاربر به راحتی باهاش آشناست.
FileDialogهای مختلفی برای باز کردن فایل، ذخیره، انتخاب فایل و انتخاب پوشه وجود داره. در این مثال، از دیالوگ باز کردن فایل استفاده شده که بهش msoFileDialogOpen می‌گن.
این ممکنه کمی گمراه‌کننده باشه، چون این دیالوگ واقعاً فایل رو باز نمی‌کنه. بلکه فقط محیطی رو فراهم می‌کنه که به راحتی بتونی فایل رو پیدا و انتخاب کنی. بعدش می‌تونی هر عملی که می‌خوای رو روش انجام بدی.
در این کد VBA، ویژگی‌های مختلف FileDialog تغییر داده می‌شن تا با ساختار With استفاده بشه.
ورک‌بوک انتخاب شده باز می‌شه و عدد ۲۰ در سلول A1 نوشته می‌شه. دوباره، این فقط یک عمل نمونه‌ست که می‌تونی با هر چیزی که می‌خوای جایگزین کنی.
Sub UsingFileDialog()
Dim Filename As String
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Title = "Select a workbook to use"
.Show
Filename = .SelectedItems(1)
End With
Workbooks.Open Filename
Worksheets("Sheet1").Range("A1").Value = 20
ActiveWorkbook.Close SaveChanges:=True
MsgBox "Workbook updated"
End Subویژگی های مفید اکسل
این کدهای VBA استفاده از برخی از مفیدترین ویژگی‌های اکسل رو ساده می‌کنن.
مرتب‌سازی ستون‌ها
این کد، محدوده A1:K250 رو بر اساس یک ستون مرتب می‌کنه. فرض بر اینه که این محدوده در ردیف اول عنوان داره.
آرگومان Key1، ستونی رو که می‌خوای بر اساسش مرتب‌سازی کنی مشخص می‌کنه. در این مثال، این آرگومان به C1 تنظیم شده، بنابراین محدوده بر اساس ستون C به صورت نزولی مرتب می‌شه.
Sub SortSingleColumn()
Range("A1:K250").Sort Key1:=Range("C1"), Order1:=xlDescending
End Subدر کد ماکرو زیر، نام تعریف‌شده ‘Sales’ به عنوان محدوده‌ای که می‌خوای بر اساسش مرتب‌سازی کنی استفاده می‌شه. این نام تعریف‌شده شامل عنوان‌ها نیست.
این بار، محدوده بر اساس ستون B به صورت صعودی مرتب می‌شه.
Sub SortSingleColumn()
Range("Sales").Sort Key1:=Range("B1"), Order1:=xlAscending
End Subآرگومان‌های اضافی Key و Order می‌تونن برای مرتب‌سازی بر اساس چندین ستون اضافه بشن.
در این مثال، نام تعریف‌شده ‘Sales’ به عنوان محدوده استفاده شده. محدوده ابتدا بر اساس ستون D به صورت صعودی مرتب می‌شه و سپس بر اساس ستون J به صورت نزولی.
Sub SortMultipleColumns()
Range("Sales").Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("J1"), Order2:=xlDescending
End Subدر نهایت، اگر داده‌های شما به عنوان یک جدول فرمت شده باشند، می‌تونی از کد VBA زیر استفاده کنی.
در این مثال، جدول به نام ‘Sales’ هست و جدول بر اساس فیلد ‘Country’ به صورت صعودی مرتب می‌شه.
Sub SortTable()
With ActiveSheet.ListObjects("Sales").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("Sales[Country]"), Order:=xlAscending
.Apply
End With
End Subفیلتر کردن داده ها
کد ماکرو زیر برای فعال کردن ویژگی AutoFilter استفاده می‌شه. این کد به شیت فعال اعمال می‌شه و از ناحیه فعلی محدوده A1 استفاده می‌کنه.
Sub TurnFilterOn()
Range("A1").AutoFilter
End Subبرای غیرفعال کردن ویژگی AutoFilter، می‌تونی از کد زیر استفاده کنی.
Sub TurnFilterOff()
ActiveSheet.AutoFilterMode = False
End Subدر این مثال، یک فیلتر بر روی ستون D (Field:=4) اعمال شده تا فقط رکوردهای مربوط به دانمارک نمایش داده بشن.
Sub FilterByText()
Range("A1").AutoFilter Field:=4, Criteria1:="Denmark"
End Subبرای فیلتر کردن بر اساس بیش از یک مقدار متنی، می‌تونی از اپراتور `xlOr` استفاده کنی. در اینجا، داده‌ها طوری فیلتر می‌شن که فقط ردیف‌های مربوط به دانمارک و بریتانیا نمایش داده بشن.
Sub FilterByText()
Range("A1").AutoFilter Field:=4, Criteria1:="Denmark", Operator:=xlOr, Criteria2:="UK"
End Subبرای استفاده، اپراتورهای زیادی در دسترس هستن، مثل xlFilterIcon و xlTop10Items.
برای فیلتر کردن با اعداد، مطمئن شو که اپراتورهای منطقی رو در رشته معیار وارد کنی. کد زیر ستون H رو طوری فیلتر می‌کنه که فقط ردیف‌هایی نمایش داده بشن که عددشون بزرگ‌تر از 5 و کمتر از 20 باشه.
Sub FilterByNumber()
Range("A1").AutoFilter Field:=8, Criteria1:=">5", Operator:=xlAnd, Criteria2:="<20"
End Subمثال زیر لیست رو بر اساس چندین ستون فیلتر می‌کنه. این کد ستون D رو فقط برای دانمارک و ستون H رو برای اعدادی بزرگ‌تر از 5 و کمتر از 20 فیلتر می‌کنه.
Sub FilterByTwoColumns()
With Range("A1")
.AutoFilter Field:=4, Criteria1:="Denmark"
.AutoFilter Field:=8, Criteria1:=">5", Operator:=xlAnd, Criteria2:="<20"
End With
End Subبرای پاک کردن هر فیلتر اعمال شده و نمایش همه داده‌ها، از کد زیر استفاده کن. این کد ابتدا بررسی می‌کنه که آیا فیلترهای خاصی اعمال شده یا نه و اگر این‌طور باشه، اون‌ها رو پاک می‌کنه تا همه داده‌ها نمایش داده بشن.
Sub ClearFilters()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Subفیلتر پیشرفته
AutoFilter تنها فیلتر موجود در اکسل نیست. یاد بگیر که چطور با VBA از فیلتر پیشرفته استفاده کنی.
ایجاد یک نمودار
می‌تونی از VBA اکسل برای ایجاد نمودارها با یک کلیک استفاده کنی.
این کد VBA یک نمودار ستونی (نوع نمودار پیش‌فرض) از محدوده C3:D8 ایجاد می‌کنه. از متغیر شیء نمودار استفاده می‌شه، بنابراین پارامترهایی برای اندازه و موقعیت ناحیه نمودار تنظیم می‌شن. سپس داده‌ها برای نمودار تعیین می‌شن.
Sub CreateChart()
Dim MChart As ChartObject
Set MyChart = ActiveSheet.ChartObjects.Add(Top:=50, Left:=100, Width:=450, Height:=250)
MyChart.Chart.SetSourceData Range("C3:D8")
End Subشاید بخوای از نوع متفاوتی از نمودار استفاده کنی، بنابراین این کد نمودار رو به نمودار خطی تغییر می‌ده. هنگام تایپ کردن، لیستی از تمام نوع‌های نمودار ظاهر می‌شه.
Sub ChangeChartType()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(1).Chart
MyChart.Chart.ChartType = xlLine
End Subدر نهایت، ممکنه بخوای عناصر نمودار رو اضافه یا حذف کنی. این کد یک عنوان برای نمودار اضافه می‌کنه و همچنین برچسب‌های داده رو هم وارد می‌کنه.
وقتی از متد SetElement استفاده می‌کنی، لیستی از عناصر نمودار ظاهر می‌شه. مهمه که یکی رو انتخاب کنی که با نوع نمودار تو سازگار باشه. در این مثال، msoElementDataLabelOutSideEnd با نمودارهای ستونی کار می‌کنه، اما با نمودارهای خطی نه، بنابراین باعث ایجاد خطا می‌شه.
Sub EditChartElements()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(1).Chart
MyChart.HasTitle = True
MyChart.ChartTitle.Text = "Product Sales"
MyChart.SetElement msoElementDataLabelOutSideEnd
End Subکد زیر یک راهنمای ابتداییه که به تو پایه‌ای برای کار با کدها می‌ده. حتماً نیاز به برخی ویرایش‌های جزئی داره تا برای صفحه‌گسترده‌های تو کار کنه.
رویدادها
این کدهای ماکرو وقتی اجرا می‌شن که رویدادهایی در حین استفاده از اکسل تحریک بشن. این رویدادها می‌تونن شامل تغییر مقادیر سلول‌ها، باز کردن ورک‌بوک‌های اکسل و تغییر زبانه‌های شیت باشن.
این کدها نیازی به این ندارن که کاربر به‌طور عمدی روی دکمه‌ای کلیک کنه تا ماکرو اجرا بشه. این رویدادها به طور طبیعی وقتی که رویداد مشخص شده اتفاق می‌افته، رخ می‌دن.
رفتن به یک شیت مشخص هنگام باز شدن
این مثال کد VBA به‌طور خودکار زمانی که ورک‌بوک اکسل باز می‌شه، اجرا می‌شه. برای این کار، باید از رویه Workbook Open استفاده کنیم.
1. در ویرایشگر Visual Basic، بر روی شیء ورک‌بوک در پنجره Project Explorer دوبار کلیک کن که می‌خوای با کد کار کنی.
2. از لیست شیء‌ها (Object list) گزینه Workbook رو انتخاب کن.
3. از لیست رویه‌ها (Procedure list) گزینه Open رو انتخاب کن.
4. کد مورد نیاز رو در رویه‌ای که در پنجره کد (Code window) نمایش داده می‌شه، جای‌گذاری کن.
برای اینکه این کد زمانی که ورک‌بوک باز می‌شه فعال بشه، نام رویه را ویرایش نکن.
می‌تونی هر کدی که می‌خوای اینجا استفاده کنی. در این مثال، به‌طور خودکار به شیتی به نام ‘Table of Contents’ و سلول A2 هدایت می‌شی.
Private Sub Workbook_Open()
Worksheets("Table of Contents").Select
Range("A2").Select
End Subرویدادهای دیگری برای ورک‌بوک وجود دارن، از جمله BeforeSave، NewSheet و SheetActivate. این رویدادها می‌تونن در خودکار کردن فرآیندهایی مثل اعتبارسنجی ورود داده‌ها، تنظیمات چاپ و غیره بسیار مفید باشن.
انجام یک عمل هنگام تغییر سلول
رویداد Change در شیء Worksheet یافت می‌شه. این رویداد خیلی مفیده چون وقتی که مقدار یک سلول تغییر می‌کنه، فعال می‌شه.
برای ایجاد رویه Worksheet_Change:
1. بر روی شیتی که می‌خوای ازش استفاده کنی در پنجره Project Explorer دوبار کلیک کن تا پنجره کدش باز بشه.
2. از لیست شیء‌ها (Object list) گزینه Worksheet رو انتخاب کن، از لیست رویه‌ها (Procedure list) گزینه Change رو انتخاب کن و رویه در پنجره کد (Code window) ظاهر می‌شه.
در این کد، سلول تغییر یافته با یک دستور If بررسی می‌شه تا چک کنه آیا سلول تغییر یافته در ستون 5 بوده و آیا نتیجه تغییر برابر با ‘Yes’ بوده است.
متغیر Target که توسط رویداد تغییر سلول ارائه می‌شه، نشان‌دهنده سلول تغییر یافته است.
اگر این شرایط برآورده بشه، محتویات ردیف به انتهای لیست در شیت 2 کپی می‌شه و رنگ پس‌زمینه سلول به رنگ زرد تغییر می‌کنه.
عبارت `Application.EnableEvents` به عنوان یک بهترین روش استفاده می‌شه چون اقدامات رویه ممکنه باعث تحریک رویداد تغییر دیگری بشن در حالی که این یکی هنوز در حال اجراست.
در این مثال، این کار ضروری نیست، اما می‌خواستم این رو با کد ارائه بدم تا اقدامات تنظیم‌شده‌ات از تولید خطا محافظت بشه.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target = "Yes" Then
Application.EnableEvents = False
Target.EntireRow.Copy Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0)
Target.Interior.ColorIndex = 6
End If
Application.EnableEvents = True
End Sub