آیا می خواهید کارهای تکراری را در اکسل با استفاده از VBA خودکار کنید؟ نحوه استفاده از حلقه For Every را برای دستکاری موثر داده ها در صفحات گسترده خود بیاموزید.
حلقه ها و VBA با هم هستند و دلیل خوبی هم دارند. هنگامی که با اشیاء مایکروسافت آفیس مانند کتاب‌های کار، کاربرگ‌ها و محدوده‌ها سروکار دارید، حلقه‌ها می‌توانند به شما کمک کنند تا به راحتی بین هر یک از آنها جابجا شوید.
در حالی که کم و بیش تمام حلقه های VBA به خوبی با اشیاء کار می کنند، حلقه “for each” یکی از بهترین گزینه ها برای کار با مجموعه اشیاء است. اگر در Excel VBA تازه کار هستید و به دنبال تسلط بر هر حلقه هستید، می توانید به سرعت از برخی از این مثال ها یاد بگیرید. در اینجا همه چیزهایی است که باید در مورد این حلقه اساسی بدانید.
برای هر نحو حلقه
نحو برای هر حلقه کاملاً شبیه حلقه for معمول در Excel VBA است. در اینجا نحو است:
For each variable_name in object_collection
[statement]
[statement]
[statement]
Next variable_name
حلقه با کلمه کلیدی “برای هر” شروع می شود. شما می توانید از هر نام متغیر و به دنبال آن مجموعه شی استفاده کنید. شیء موجود در هر حلقه می تواند سلول، محدوده، برگه و حتی کتاب کار باشد. زیبایی این حلقه چنین است. این انعطاف پذیری را برای کار با مجموعه های مختلف اکسل به شما می دهد.
حلقه در هر مقدار مجموعه می چرخد و مرجع را در نام متغیر تعریف شده ذخیره می کند. پس از اجرا، VBA عبارات ذخیره شده را در حلقه اجرا می کند و به شی بعدی در مجموعه می رود (کلید کلیدی بعدی در اینجا مفید است). بیایید ساختار کد را با یک مثال اساسی درک کنیم.
نحوه استفاده از For Every Loop در Excel VBA
فرض کنید می خواهید عددی را در سلول های A1 تا A10 چاپ کنید. بهترین رویکرد این است که از for هر حلقه با تابع range استفاده کنید و اجازه دهید کد کارهای لازم را انجام دهد. در اینجا نحوه انجام این کار ساده آمده است:
- ویرایشگر کد VBA را با فشار دادن Alt + F11 باز کنید.
- با کلیک بر روی گزینه Module در تب Insert یک ماژول را وارد کنید.
- یک روال فرعی با استفاده از دستور sub() در پنجره ماژول ویرایشگر کد ایجاد کنید. اطمینان حاصل کنید که یک نام معنادار به برنامه فرعی اختصاص داده اید. برای این مثال می توانید از نام for_each_loop استفاده کنید.
اکنون که اصول اولیه از بین رفته است، زمان شروع نوشتن کد است. در زیر روال، دستورات زیر را تایپ کنید:
Dim cell as range
For each cell in Sheets("Sheet1").Range("A1:A10")
cell.value = 10
Next cell
هنگامی که کد اجرا می شود، متغیر c مقدار A1 را ذخیره می کند. در مرحله بعد، همانطور که به دستور داخل حلقه می رود، دستور را ارزیابی می کند و مقدار 10 را در سلول تعریف شده، یعنی سلول A1 وارد می کند.
در نهایت، با رفتن به کلمه کلیدی Next، به مقدار بعدی یعنی سلول A2 منتقل می شود. حلقه اجرا می شود تا به سلول A10 برسد. این خروجی نهایی است:
استفاده از حلقه با اشیاء: سلول ها، برگه ها و کتاب های کاری
اکسل دارای سه نوع شی اصلی است که شما به طور منظم با آنها کار می کنید. اینها سلول ها، برگه ها و کتاب های کار هستند. در اینجا نحوه استفاده از حلقه با هر سه نوع شیء آورده شده است.
کار با سلول ها و حلقه ها با هم
فرض کنید می خواهید یک مقدار و شرایط قالب بندی را به محدوده ای از سلول ها در Sheet1 اضافه کنید. به عنوان اولین گام، باید شرایط را در حلقه تعریف کنید و سپس دستورات قالب بندی را دنبال کنید.
کد زیر را در یک زیر روال وارد کنید.
Sub for_each_loop()
Dim c As Range
For Each c In Sheets("Sheet1").Range("A1:A10")
With c
.Value = 10
.Font.Color = vbRed
.Font.Bold = True
.Font.Strikethrough = True
End With
Next c
End Sub
تابع with هنگام انجام چندین عملکرد با یک شی خاص مفید است. از آنجایی که می خواهید یک سری کارها را با متغیر c انجام دهید، می توانید همه آنها را با استفاده از یک تابع با..end با ترکیب کنید.
حلقه مقدار c را با هر مقدار سلول برابر می‌کند و مقدار آن را 10 می‌کند. علاوه بر این، رنگ سلول را به قرمز تغییر می‌دهد، مقدار را پررنگ می‌کند و آن را از بین می‌برد. هنگامی که تمام مراحل را کامل کرد، به مقدار تعریف شده بعدی در محدوده منتقل می شود.
استفاده از حلقه برای کنترل برگه ها
مشابه مثال بالا، می‌توانید برای هر حلقه از حلقه برای کنترل برگه‌ها استفاده کنید. در مورد تغییر نام Sheet1 به Sheet3 با استفاده از VBA چطور؟
می توانید از کد زیر برای تغییر نام کاربرگ موجود در اکسل با استفاده از VBA استفاده کنید:
Sub for_each_loop_sheets()
For Each sht In ThisWorkbook.Sheets
If sht.Name = "Sheet1" Then
sht.Name = "Sheet3"
End If
Next sht
End Sub
کد از طریق هر صفحه در کتاب کار حلقه زده و نام هر برگه را بررسی می کند. اگر با نام Sheet1 مواجه شد، آن را به Sheet3 تغییر می دهد. از طریق برگه های باقی مانده، در صورت وجود، در کتاب کار پیشرفت می کند. هنگامی که اجرای کد به آخرین صفحه رسید، از حلقه و روال فرعی خارج می شود.
در میان برخی کاربردهای رایج دیگر، می‌توانید چندین کاربرگ را با استفاده از VBA ادغام کنید و با استفاده از حلقه‌ها، محتوا را در یک صفحه به‌روزرسانی کنید.
جابجایی از طریق Workbooks با حلقه
در نهایت، می‌توانید برای هر حلقه برای جابه‌جایی در کتاب‌های مختلف و انجام وظایف خاص استفاده کنید. اجازه دهید این تابع را با یک مثال نشان دهیم.
می‌توانید از دستورات VBA برای اضافه کردن سه کتاب کار جدید و بستن همه کتاب‌های باز با هم استفاده کنید. در اینجا نحوه انجام این کار آمده است:
Sub loop_wrkbook()
Dim wrkbook as workbook
Workbooks.Add
Workbooks.Add
Workbooks.Add
For Each wrkbook In Workbooks
wrkbook.Close
Next wrkbook
End Sub
کد بالا کتاب کار ماکرو شما را نیز می بندد. قبل از اجرای این کد مطمئن شوید که کدهای خود را ذخیره کرده اید.
از آنجایی که حلقه‌ها چندین کاربرد دارند، می‌توانید داده‌ها را از چندین کتاب کار در یک کتاب کار ادغام کنید.
استفاده از یک دستور IF تودرتو با حلقه
مانند مثال بالا، می توانید از یک دستور IF در حلقه برای بررسی شرایط خاص استفاده کنید. بیایید رنگ پس زمینه سلول را بر اساس مقادیر سلول تغییر دهیم.
چند عدد تصادفی در سلول های A1:A20 وجود دارد. شما می توانید یک حلقه بنویسید تا در هر سلول معین در محدوده بچرخد. اگر مقدار سلول کمتر از 10 باشد، رنگ سلول باید به قرمز تغییر کند. اگر مقدار سلول از 10 بیشتر شود، باید سبز شود. برای این منظور از کد زیر استفاده کنید:
Sub loop_w_if()
Dim c As Range
For Each c In Sheets("Sheet4").Range("A1:A20")
If c.Value < 10 Then
c.Interior.ColorIndex = 3
Else: c.Interior.ColorIndex = 4
End If
Next c
End Sub
خروجی به این صورت است:
استفاده از Loops در Excel VBA
Excel VBA فقط به حلقه برای هر حلقه محدود نمی شود. حلقه های مفید مختلفی وجود دارد که به شما امکان می دهد عملکردهای مختلف را به راحتی انجام دهید. از کارهای پیش پا افتاده و دستی خداحافظی کنید، زیرا حلقه های VBA مانند حلقه for، این کار را انجام دهید، و تا زمانی که حلقه ها وارد شوند تا زندگی شما را آسان تر کنند.