دنبال مثال ماکرو VBA اکسل و کد های آماده VBA میگردید؟ استفاده از ماکروهای اکسل می تواند سرعت کار را افزایش دهد و در زمان شما صرفه جویی زیادی کند.
یکی از راه های دریافت کد VBA، ضبط ماکرو و گرفتن کدی است که تولید می کند. با این حال، آن کد ضبط کننده ماکرو اغلب مملو از کدهایی است که واقعاً مورد نیاز نیستند. همچنین ضبط کننده ماکرو محدودیت هایی دارد.
بنابراین، داشتن مجموعه ای از کدهای ماکرو VBA برای اکسل مفید است که می توانید در جیب خود داشته باشید و در صورت نیاز از آن استفاده کنید.
در حالی که نوشتن یک کد ماکرو اکسل ممکن است در ابتدا کمی طول بکشد، پس از انجام آن، می توانید آن را به عنوان یک مرجع در دسترس نگه دارید و هر زمان که به آن نیاز داشتید از آن استفاده کنید.
در این مقاله بزرگ و جامع، من قصد دارم چند نمونه مفید ماکرو اکسل را لیست کنم که اغلب به آنها نیاز دارم و در فایل های پشتیبان خود نگهداری می کنم.
حالا قبل از اینکه وارد مثال ماکرو بشوم و کد VBA را به شما بدهم، اجازه دهید ابتدا نحوه استفاده از این کدهای نمونه را به شما نشان دهم.
آموزش استفاده از کد های ماکرو در اکسل
در اینجا مراحلی وجود دارد که باید برای استفاده از کد هر یک از مثال ها دنبال کنید:
- Workbook را که می خواهید در آن از ماکرو استفاده کنید باز کنید.
- کلید ALT را نگه داشته و F11 را فشار دهید. با این کار ویرایشگر VB باز می شود.
- بر روی هر یک از آبجکت ها در کاوشگر پروژه کلیک راست کنید.
- به Insert –> Module بروید.
- کد را در پنجره کد ماژول کپی و جایگذاری کنید.
اگر مثال می گوید که باید کد را در پنجره کد worksheet جایگذاری کنید، روی ابجکت worksheet دوبار کلیک کنید و کد را در پنجره کد کپی کنید.
هنگامی که کد را در workbook وارد کردید، باید آن را با پسوند .XLSM یا XLS. ذخیره کنید.
نحوه اجرای ماکرو در اکسل
هنگامی که کد را در ویرایشگر VB کپی کردید، در اینجا مراحل اجرای ماکرو وجود دارد:
- به تب Developer بروید.
- روی Macros کلیک کنید.
- در کادر محاوره ای ماکرو، ماکرویی را که می خواهید اجرا کنید انتخاب کنید.
- روی دکمه Run کلیک کنید.
در صورتی که کد در پنجره کد worksheet جایگذاری شده باشد، لازم نیست نگران اجرای کد باشید. زمانی که عمل مشخص شده انجام شود به طور خودکار اجرا می شود.
اکنون، بیایید به مثالهای ماکرو مفیدی بپردازیم که میتوانند به شما در خودکارسازی کار و صرفهجویی در زمان کمک کنند.
توجه: نمونههای بسیاری از آپاستروف (‘) را خواهید دید که یک یا دو خط به دنبال آن است. اینها نظراتی هستند که هنگام اجرای کد نادیده گرفته می شوند و به عنوان یادداشت برای خود/خواننده قرار می گیرند.
در صورت مشاهده هر گونه خطایی در مقاله یا کد، لطفاً به من اطلاع دهید.
نمونه مثال ماکرو VBA اکسل
۱٫ همه Worksheet ها را با یک حرکت از مخفی بودن خارج کنید
اگر در Worksheet کار میکنید که دارای چندین برگه مخفی است، باید این برگهها را یکی یکی باز کنید. در صورتی که صفحات پنهان زیادی وجود داشته باشد، ممکن است کمی طول بکشد.
در اینجا کدی وجود دارد که همه کاربرگ های کتاب کار را آشکار می کند.
'This code will unhide all sheets in the workbook Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
۲٫ پنهان کردن همه Worksheet ها به جز برگه فعال
اگر روی یک گزارش یا داشبورد کار میکنید و میخواهید همه کاربرگها را به جز صفحهای که گزارش/داشبورد دارد پنهان کنید، میتوانید از این کد ماکرو استفاده کنید.
'This macro will hide all the worksheet except the active sheet Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub
۳٫ Worksheet ها را بر اساس حروف الفبا با استفاده از VBA مرتب کنید
اگر یک کتاب کار با کاربرگ های زیادی دارید و می خواهید آنها را بر اساس حروف الفبا مرتب کنید، این کد ماکرو می تواند بسیار مفید باشد. اگر نام برگهها را بهعنوان سال یا نام کارمندان یا نام محصولات داشته باشید، میتواند چنین باشد.
'This code will sort the worksheets alphabetically 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 Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub
۴٫ همه Worksheet ها را با یک حرکت قفل کنید
اگر کاربرگ های زیادی در یک کتاب کار دارید و می خواهید از همه برگه ها محافظت کنید، می توانید از این کد ماکرو استفاده کنید.
این به شما امکان می دهد رمز عبور را در کد مشخص کنید. برای حذف محافظت از کاربرگ به این رمز عبور نیاز دارید.
'This code will protect all the sheets at one go Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'replace Test123 with the password you want For Each ws In Worksheets ws.Protect password:=password Next ws End Sub
۵٫ قفل همه Worksheet ها را با یک حرکت باز کنید
اگر برخی یا همه کاربرگها را محافظت کردهاید، میتوانید فقط از یک تغییر جزئی در کد مورد استفاده برای محافظت از برگهها استفاده کنید تا قفل آن را باز کنید.
'This code will protect all the sheets at one go Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'replace Test123 with the password you want For Each ws In Worksheets ws.Unprotect password:=password Next ws End Sub
توجه داشته باشید که رمز عبور باید همان رمز عبوری باشد که برای قفل کردن کاربرگ ها استفاده شده است. اگر اینطور نیست، یک خطا خواهید دید.
۶٫ نمایش همه سطرها و ستون ها
این کد ماکرو تمام سطرها و ستون های پنهان را آشکار می کند.
اگر فایلی را از شخص دیگری دریافت می کنید و می خواهید مطمئن شوید که ردیف/ستون پنهانی وجود ندارد، این می تواند واقعا مفید باشد.
'This code will unhide all the rows and columns in the Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
۷٫ لغو ادغام تمام سلول های ادغام شده
در قسمت دیگری از مقاله مثال ماکرو VBA اکسل، به نحوه خارج کردن سلول ها از حالت مرج به صورت خودکار خواهیم پرداخت.
ادغام سلول ها برای تبدیل شدن به آن یک سلول، معمول است. در حالی که کار را انجام می دهد، وقتی سلول ها ادغام می شوند، نمی توانید داده ها را مرتب کنید.
در صورتی که با یک کاربرگ با سلولهای ادغام شده کار میکنید، از کد زیر استفاده کنید تا همه سلولهای ادغامشده را یکجا از حالت ادغام خارج کنید.
'This code will unmerge all the merged cells Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
توجه داشته باشید که به جای Merge and Center، توصیه می کنم از گزینه Center Across Selection استفاده کنید.
۸٫ Workbook را با مهر زمانی به نام آن ذخیره کنید
ممکن است نیاز به زمان زیادی داشته باشید تا نسخه هایی از کار خود را ایجاد کنید. اینها در پروژه های طولانی که در آن با یک فایل در طول زمان کار می کنید بسیار مفید هستند.
یک تمرین خوب این است که فایل را با مهر زمانی ذخیره کنید.
استفاده از مهر زمانی به شما این امکان را می دهد که به یک فایل خاص برگردید تا ببینید چه تغییراتی ایجاد شده است یا از چه داده هایی استفاده شده است.
در اینجا کدی وجود دارد که به طور خودکار کتاب کار را در پوشه مشخص شده ذخیره می کند و هر زمان که ذخیره شد یک مهر زمانی اضافه می کند.
'This code will Save the File With a Timestamp in its name Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp End Sub
در کد بالا باید محل پوشه و نام فایل را مشخص کنید.
در کد بالا، “C:UsersUsernameDesktop محل پوشه ای است که من استفاده کرده ام. باید محل پوشه ای را که می خواهید فایل را در آن ذخیره کنید مشخص کنید. همچنین، من از نام عمومی “WorkbookName” به عنوان پیشوند نام فایل استفاده کرده ام. شما می توانید چیزی مربوط به پروژه یا شرکت خود را مشخص کنید.
۹٫ هر Worksheet را به عنوان یک PDF جداگانه ذخیره کنید
اگر با دادههای سالها یا بخشها یا محصولات مختلف کار میکنید، ممکن است نیاز به ذخیره کاربرگهای مختلف به عنوان فایلهای PDF داشته باشید.
در حالی که اگر به صورت دستی انجام شود می تواند یک فرآیند زمان بر باشد، VBA واقعا می تواند سرعت آن را افزایش دهد.
در اینجا یک کد VBA وجود دارد که هر کاربرگ را به عنوان یک PDF جداگانه ذخیره می کند.
'This code will save each worsheet as a separate PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
در کد بالا آدرس محل پوشه ای که می خواهم PDF ها را در آن ذخیره کنم را مشخص کرده ام. همچنین، هر PDF همان نام کاربرگ را دریافت می کند. شما باید مکان این پوشه را تغییر دهید.
توجه داشته باشید که این کد فقط برای کاربرگ ها (و نه برگه های نمودار) کار می کند.
۱۰٫ هر Worksheet را به عنوان یک PDF جداگانه ذخیره کنید
در اینجا کدی وجود دارد که کل کتاب کار شما را به عنوان PDF در پوشه مشخص شده ذخیره می کند.
'This code will save the entire workbook as PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
برای استفاده از این کد باید مکان پوشه را تغییر دهید.
۱۱٫ همه فرمول ها را به مقادیر تبدیل کنید
از این کد زمانی استفاده کنید که یک کاربرگ حاوی فرمول های زیادی دارید و می خواهید این فرمول ها را به مقادیر تبدیل کنید.
'This code will convert all formulas into values Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub
این کد به طور خودکار سلول های استفاده شده را شناسایی می کند و آن را به مقادیر تبدیل می کند.
۱۲٫ محافظت/قفل کردن سلول های دارای فرمول
یکی از مهم ترین کد های آماده از مثال ماکرو VBA اکسل، قفل کردن سلول های دارای فرمول به صورت خودکار میباشد.
هنگامی که محاسبات زیادی دارید و نمی خواهید تصادفاً آن را حذف کنید یا تغییر دهید، ممکن است بخواهید سلول ها را با فرمول قفل کنید.
در اینجا کدی وجود دارد که تمام سلول های دارای فرمول را قفل می کند، در حالی که تمام سلول های دیگر قفل نیستند.
'This macro code will lock all the cells with formulas Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub
۱۳٫ از همه Worksheet های Workbook محافظت کنید
از کد زیر برای محافظت از همه کاربرگ های یک کتاب کار در یک لحظه استفاده کنید.
'This code will protect all sheets in the workbook Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub
این کد تمامی کاربرگ ها را یکی یکی مرور می کند و از آن محافظت می کند.
در صورتی که میخواهید همه کاربرگها را لغو محافظت کنید، از ws.Unprotect به جای ws.Protect در کد استفاده کنید.
۱۴٫ یک ردیف را بعد از هر ردیف انتخابی قرار دهید
زمانی که می خواهید بعد از هر ردیف در محدوده انتخاب شده یک ردیف خالی وارد کنید از این کد استفاده کنید.
'This code will insert a row after every row in the selection Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next i End Sub
به طور مشابه، می توانید این کد را تغییر دهید تا بعد از هر ستون در محدوده انتخاب شده، یک ستون خالی درج کنید.
۱۵٫ درج خودکار تاریخ و مهر زمانی در سلول مجاور
زمانی که میخواهید فعالیتها را ردیابی کنید، از مهر زمانی استفاده میکنید.
به عنوان مثال، ممکن است بخواهید فعالیتهایی مانند زمان انجام یک هزینه خاص، زمان ایجاد فاکتور فروش، زمان ورود دادهها در یک سلول، آخرین زمان بهروزرسانی گزارش و غیره را ردیابی کنید.
از این کد برای درج مهر تاریخ و زمان در سلول مجاور هنگام ورود یا ویرایش محتوای موجود استفاده کنید.
'This code will insert a timestamp in the adjacent cell Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub
توجه داشته باشید که باید این کد را در پنجره کد worksheet وارد کنید (و نه پنجره کد درون ماژول همانطور که در دیگر نمونههای ماکرو اکسل تاکنون انجام دادهایم). برای انجام این کار، در ویرایشگر VB، روی نام برگه ای که روی آن این قابلیت را می خواهید دوبار کلیک کنید. سپس این کد را در پنجره کد آن برگه کپی و جایگذاری کنید.
همچنین، این کد زمانی کار می کند که وارد کردن داده ها در ستون A انجام شود (توجه داشته باشید که کد دارای خط Target.Column = 1 است). بر این اساس می توانید این را تغییر دهید.
۱۶٫ ردیف های جایگزین را در قسمت انتخاب برجسته کنید
برجسته کردن ردیف های جایگزین می تواند خوانایی داده های شما را به شدت افزایش دهد. این می تواند زمانی مفید باشد که نیاز به پرینت گرفتن و مرور داده ها دارید.
در اینجا کدی وجود دارد که بلافاصله ردیف های جایگزین را در انتخاب برجسته می کند.
'This code would highlight alternate rows in the selection Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
توجه داشته باشید که رنگ را به صورت vbCyan در کد مشخص کرده ام. می توانید رنگ های دیگری را نیز مشخص کنید (مانند vbRed، vbGreen، vbBlue).
۱۷٫ سلول هایی را با کلمات غلط املایی برجسته کنید
یکی دیگر از مثال ماکرو VBA اکسل، هایلایت کردن کلماتی که دارای غلط املایی هستند میباشد.
اکسل مانند ورد یا پاورپوینت چک املا ندارد. در حالی که می توانید با زدن کلید F7 املا را اجرا کنید، اما در صورت وجود یک اشتباه املایی هیچ نشانه بصری وجود ندارد.
از این کد برای برجسته کردن فورا تمام سلول هایی که دارای اشتباه املایی هستند استفاده کنید.
'This code will highlight the cells that have misspelled words Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub
توجه داشته باشید که سلول هایی که هایلایت می شوند آنهایی هستند که متنی دارند که اکسل آن را به عنوان یک خطای املایی در نظر می گیرد. در بسیاری از موارد، نامها یا اصطلاحات برند را که متوجه نمیشود نیز برجسته میکند.
۱۸٫ همه جداول محوری را در Workbook بازخوانی کنید
اگر بیش از یک Pivot Table در کتاب کار دارید، میتوانید از این کد برای بازخوانی همه این جدولهای محوری به طور همزمان استفاده کنید.
'This code will refresh all the Pivot Table in the Workbook Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
۱۹٫ حروف کوچک سلول های انتخاب شده را به حروف بزرگ تغییر دهید
در حالی که اکسل فرمول هایی برای تغییر حروف کوچک متن دارد، شما را مجبور می کند این کار را در مجموعه دیگری از سلول ها انجام دهید.
از این کد برای تغییر فوری حروف کوچک متن در متن انتخاب شده استفاده کنید.
'This code will change the Selection to Upper Case Sub ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub
توجه داشته باشید که در این مورد، من از UCase برای ایجاد حروف متنی Upper استفاده کرده ام. برای حروف کوچک می توانید از LCase استفاده کنید.
۲۰٫ تمام سلول هایی که کامنت دارند را برجسته کنید
از کد زیر برای برجسته کردن تمام سلول هایی که در آن نظرات وجود دارد استفاده کنید.
'This code will highlight cells that have comments` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub
در این مورد من از vbBlue برای دادن رنگ آبی به سلول ها استفاده کرده ام. در صورت تمایل می توانید این رنگ را به رنگ های دیگر تغییر دهید.
۲۱٫ سلول های خالی را با VBA برجسته کنید
یکی دیگر از مجموعه مثال ماکرو VBA اکسل، نحوه هایلایت کردن سلول های خالی میباشد.
در حالی که می توانید سلول خالی را با قالب بندی شرطی یا با استفاده از کادر گفتگوی Go to Special برجسته کنید، اگر مجبور هستید این کار را اغلب انجام دهید، بهتر است از یک ماکرو استفاده کنید.
پس از ایجاد، می توانید این ماکرو را در نوار ابزار دسترسی سریع داشته باشید یا آن را در کتاب کار ماکرو شخصی خود ذخیره کنید.
این کد ماکرو VBA است :
'This code will highlight all the blank cells in the dataset Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub
در این کد سلول های خالی را مشخص کرده ام که با رنگ قرمز برجسته شوند. می توانید رنگ های دیگری مانند آبی، زرد، فیروزه ای و … را انتخاب کنید.
۲۲٫ چگونه داده ها را بر اساس تک ستون مرتب کنیم
می توانید از کد زیر برای مرتب سازی داده ها بر اساس ستون مشخص شده استفاده کنید.
Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End Sub
توجه داشته باشید که من یک محدوده با نام با نام “DataRange” ایجاد کرده ام و از آن به جای مراجع سلولی استفاده کرده ام.
همچنین سه پارامتر کلیدی وجود دارد که در اینجا استفاده می شود:
- Key1 – این همان جایی است که می خواهید مجموعه داده ها را بر روی آن مرتب کنید. در کد مثال بالا، داده ها بر اساس مقادیر ستون A مرتب می شوند.
- Order- در اینجا باید مشخص کنید که می خواهید داده ها را به ترتیب صعودی یا نزولی مرتب کنید.
- Header – در اینجا باید مشخص کنید که آیا داده های شما هدر دارند یا خیر
۲۳٫ چگونه داده ها را بر اساس چند ستون مرتب کنیم
فرض کنید شما یک مجموعه داده مطابق شکل زیر دارید :
در زیر کدی وجود دارد که داده ها را بر اساس چندین ستون مرتب می کند :
Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1:C13") .Header = xlYes .Apply End With End Sub
توجه داشته باشید که در اینجا مشخص کرده ام که ابتدا بر اساس ستون A و سپس بر اساس ستون B مرتب شوند.
خروجی چیزی مانند شکل زیر خواهد بود :
۲۴٫ چگونه فقط قسمت عددی را از یک رشته در اکسل دریافت کنیم
در آخرین مثال ماکرو VBA اکسل به نحوه استخراج و جداسازی عدد و متن از هم خواهیم پرداخت.
اگر می خواهید فقط قسمت عددی یا فقط قسمت متن را از یک رشته استخراج کنید، می توانید یک تابع سفارشی در VBA ایجاد کنید.
سپس میتوانید از این تابع VBA در کاربرگ استفاده کنید (درست مانند توابع معمولی اکسل) و فقط قسمت عددی یا متنی را از رشته استخراج میکند.
چیزی که در زیر نشان داده شده است :
در زیر کد VBA وجود دارد که تابعی را برای استخراج بخش عددی از یک رشته ایجاد می کند:
'This VBA code will create a function to get the numeric part from a string Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function
شما باید کد را در یک ماژول قرار دهید و سپس می توانید از تابع =GetNumeric در کاربرگ استفاده کنید.
این تابع فقط یک آرگومان می گیرد، که مرجع سلولی است که می خواهید قسمت عددی را از آن دریافت کنید.
به طور مشابه، در زیر تابعی است که تنها قسمت متنی را از یک رشته در اکسل دریافت می کند :
'This VBA code will create a function to get the text part from a string Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function
بنابراین اینها برخی از کدهای ماکرو مفید اکسل هستند که می توانید در کارهای روزمره خود برای خودکارسازی وظایف و بهره وری بسیار بیشتر از آنها استفاده کنید.
آیا در رابطه با این مقاله در مورد ۲۴ مثال ماکرو VBA اکسل برای مبتدیان (آماده برای استفاده) سوالی دارید؟ اگر بله، لطفاً در بخش نظرات مقاله به ما اطلاع دهید. تیم متخصص ما به شما کمک می کند تا سوالات خود را بلافاصله حل کنید.