رویه های "عملکرد" ​​و "زیر" در VBA

توابع VBA داخلی

قبل از شروع ایجاد توابع VBA خود، خوب است بدانید که Excel VBA دارای مجموعه ای غنی از توابع داخلی از پیش ساخته شده است که می توانید هنگام نوشتن کد خود از آنها استفاده کنید.

لیستی از این توابع را می توان در ویرایشگر VBA مشاهده کرد:

• یک کتاب کار اکسل را باز کنید و ویرایشگر VBA را اجرا کنید (برای انجام این کار کلیک کنید Alt + F11) ، و سپس فشار دهید F2.
• یک کتابخانه را از لیست کشویی در سمت چپ بالای صفحه انتخاب کنید VBA.
• لیستی از کلاس ها و توابع داخلی VBA ظاهر می شود. روی نام تابع کلیک کنید تا توضیحات مختصر آن در پایین پنجره نمایش داده شود. فشار دادن F1 صفحه راهنمای آنلاین آن ویژگی را باز می کند.

علاوه بر این، فهرست کاملی از توابع داخلی VBA با مثال‌هایی را می‌توانید در مرکز توسعه‌دهنده ویژوال بیسیک پیدا کنید.

رویه های سفارشی "Function" و "Sub" در VBA

در اکسل ویژوال بیسیک مجموعه ای از دستورات که وظیفه خاصی را انجام می دهند در یک رویه قرار می گیرند. عملکرد (تابع) یا زیر (زیر روال). تفاوت اصلی بین رویه ها عملکرد и زیر این روش است عملکرد نتیجه، رویه را برمی گرداند زیر - نه

بنابراین، اگر باید اقداماتی را انجام دهید و نتیجه بگیرید (مثلاً چندین عدد را جمع کنید)، معمولاً از این روش استفاده می شود. عملکرد، و برای انجام برخی اقدامات (به عنوان مثال، تغییر قالب بندی یک گروه از سلول ها)، باید رویه را انتخاب کنید. زیر.

استدلال

داده های مختلف را می توان با استفاده از آرگومان ها به رویه های VBA منتقل کرد. لیست آرگومان هنگام اعلان یک رویه مشخص می شود. به عنوان مثال، رویه زیر در VBA عدد صحیح داده شده (Integer) را به هر سلول در محدوده انتخاب شده اضافه می کند. می توانید این عدد را با استفاده از یک آرگومان به رویه ارسال کنید، مانند این:

Sub AddToCells(i As Integer) ... End Sub

به خاطر داشته باشید که داشتن استدلال برای رویه ها عملکرد и زیر در VBA اختیاری است. برخی از رویه ها به آرگومان نیاز ندارند.

استدلال های اختیاری

رویه های VBA می توانند آرگومان های اختیاری داشته باشند. این‌ها آرگومان‌هایی هستند که کاربر در صورت تمایل می‌تواند آن‌ها را مشخص کند و اگر حذف شوند، رویه از مقادیر پیش‌فرض برای آنها استفاده می‌کند.

با بازگشت به مثال قبلی، برای اختیاری کردن یک آرگومان عدد صحیح به یک تابع، به صورت زیر اعلام می شود:

Sub AddToCells (اختیاری i As Integer = 0)

در این مورد، آرگومان عدد صحیح i پیش فرض 0 خواهد بود.

ممکن است چندین آرگومان اختیاری در یک رویه وجود داشته باشد که همه آنها در انتهای لیست آرگومان فهرست شده اند.

انتقال آرگومان ها بر اساس مقدار و با مرجع

آرگومان ها در VBA می توانند به دو روش به یک رویه منتقل شوند:

• ByVal - تصویب یک آرگومان بر اساس مقدار. این بدان معنی است که فقط مقدار (یعنی یک کپی از آرگومان) به رویه ارسال می شود و بنابراین هر تغییری که در آرگومان داخل رویه ایجاد شود، با خروج از رویه از بین می رود.
• ByRef - انتقال یک استدلال با مرجع. یعنی آدرس واقعی محل آرگومان در حافظه به رویه داده می شود. هر تغییری که در یک آرگومان در داخل رویه ایجاد شود، با خروج از رویه ذخیره می‌شود.

استفاده از کلمات کلیدی ByVal or ByRef در اعلان رویه، می توانید نحوه ارسال آرگومان به رویه را مشخص کنید. این در مثال های زیر نشان داده شده است:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

به یاد داشته باشید که آرگومان ها در VBA به صورت پیش فرض به صورت مرجع ارسال می شوند. به عبارت دیگر اگر از کلمات کلیدی استفاده نشود ByVal or ByRef، سپس آرگومان با مرجع ارسال می شود.

قبل از ادامه مراحل عملکرد и زیر با جزئیات بیشتر، نگاهی دوباره به ویژگی ها و تفاوت های بین این دو نوع روش مفید خواهد بود. در زیر بحث های مختصری در مورد رویه های VBA وجود دارد عملکرد и زیر و نمونه های ساده نشان داده شده است.

روش VBA «عملکرد»

ویرایشگر VBA رویه را تشخیص می دهد عملکردهنگامی که با گروهی از دستورات محصور شده بین دستورهای باز و بسته زیر روبرو می شود:

تابع ... تابع پایان

همانطور که قبلا ذکر شد، روش عملکرد در VBA (برخلاف زیر) یک مقدار را برمی گرداند. قوانین زیر برای مقادیر بازگشتی اعمال می شود:

• نوع داده مقدار برگشتی باید در هدر رویه اعلام شود عملکرد.
• متغیری که حاوی مقدار بازگشتی است باید مانند رویه نامگذاری شود عملکرد. این متغیر نیازی به تعریف جداگانه ندارد، زیرا همیشه به عنوان بخشی جدایی ناپذیر از رویه وجود دارد. عملکرد.

این موضوع در مثال زیر به خوبی نشان داده شده است.

مثال تابع VBA: انجام یک عملیات ریاضی بر روی 3 عدد

در زیر نمونه ای از کد رویه VBA آورده شده است عملکرد، که سه آرگومان از نوع را می گیرد دوبار (اعداد ممیز شناور با دقت دوگانه). در نتیجه، رویه تعداد دیگری از نوع را برمی گرداند دوباربرابر با مجموع دو آرگومان اول منهای آرگومان سوم:

تابع SumMinus(dNum1 به عنوان دو برابر، dNum2 به عنوان دو برابر، dNum3 به عنوان دو برابر) به عنوان Double SumMinus = dNum1 + dNum2 - dNum3 تابع پایان

این روش بسیار ساده VBA عملکرد نشان می دهد که چگونه داده ها از طریق آرگومان ها به یک رویه منتقل می شوند. می بینید که نوع داده ای که توسط رویه برگردانده می شود به صورت تعریف شده است دوبار (کلمات می گویند به عنوان دوبل پس از فهرست استدلال ها). این مثال همچنین نشان می دهد که چگونه نتیجه روش عملکرد در متغیری با نام مشابه با نام رویه ذخیره می شود.

فراخوانی رویه VBA "Function"

اگر روش ساده بالا عملکرد در یک ماژول در ویرایشگر ویژوال بیسیک قرار داده شده است، می توان آن را از رویه های دیگر VBA فراخوانی کرد یا در یک کاربرگ در یک کتاب کار اکسل استفاده کرد.

رویه VBA را "عملکرد" ​​از رویه دیگر فراخوانی کنید

روش عملکرد را می توان از رویه VBA دیگر با اختصاص دادن آن رویه به یک متغیر فراخوانی کرد. مثال زیر فراخوانی یک رویه را نشان می دهد Summinus، که در بالا تعریف شد.

Sub main() Dim total به عنوان Double total = SumMinus(5, 4, 3) End Sub

رویه VBA را "Function" از یک کاربرگ فراخوانی کنید

رویه VBA عملکرد را می توان از یک کاربرگ اکسل به همان روشی که هر تابع داخلی اکسل دیگری فراخوانی کرد. بنابراین، رویه ایجاد شده در مثال قبلی عملکرد - Summinus را می توان با وارد کردن عبارت زیر در یک سلول کاربرگ فراخوانی کرد:

=SumMinus(10, 5, 2)

روش VBA «Sub»

ویرایشگر VBA متوجه می شود که رویه ای در مقابل آن وجود دارد زیرهنگامی که با گروهی از دستورات محصور شده بین دستورهای باز و بسته زیر روبرو می شود:

زیر ... پایان ساب

روش VBA "Sub": مثال 1. تغییر تراز وسط و اندازه فونت در محدوده انتخاب شده از سلول ها

نمونه ای از یک روش ساده VBA را در نظر بگیرید زیر، که وظیفه آن تغییر قالب بندی محدوده انتخاب شده سلول ها است. سلول ها در مرکز قرار می گیرند (هم به صورت عمودی و هم به صورت افقی) و اندازه فونت به مقدار مشخص شده توسط کاربر تغییر می کند:

Sub Format_Centered_And_Sized (اختیاری iFontSize به عنوان عدد صحیح = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

این روش زیر اقداماتی را انجام می دهد اما نتیجه ای را بر نمی گرداند.

این مثال همچنین از یک آرگومان Optional استفاده می کند اندازه فونت. اگر استدلال اندازه فونت به رویه منتقل نشده است زیر، سپس مقدار پیش فرض آن 10 است. اما اگر آرگومان اندازه فونت به رویه منتقل شد زیر، سپس محدوده انتخاب شده سلول ها به اندازه فونت تعیین شده توسط کاربر تنظیم می شود.

روش فرعی VBA: مثال 2: تراز وسط و قلم پررنگ در محدوده انتخابی سلول ها

روش زیر شبیه به روشی است که قبلاً در مورد آن بحث شد، اما این بار به جای تغییر اندازه، یک سبک قلم پررنگ را در محدوده انتخاب شده از سلول ها اعمال می کند. این یک روش نمونه است زیر، که هیچ استدلالی ندارد:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub Sub

فراخوانی روش "Sub" در Excel VBA

رویه VBA را «Sub» از رویه دیگری فراخوانی کنید

برای فراخوانی یک روش VBA زیر از روش دیگر VBA، باید کلمه کلیدی را بنویسید صدا، نام روش زیر و در داخل پرانتز، استدلال های رویه وجود دارد. این در مثال زیر نشان داده شده است:

Sub main() Call Format_Centred_And_Sized(20) End Sub

اگر رویه قالب_مرکز_و_اندازه بیش از یک آرگومان دارد، آنها باید با کاما از هم جدا شوند. مثل این:

Sub main() Call Format_Centred_And_Sized(arg1, arg2, ...) End Sub

رویه VBA را «Sub» از کاربرگ فراخوانی کنید

روش زیر را نمی توان مستقیماً به سلول برگه اکسل وارد کرد، همانطور که می توان با یک رویه انجام داد عملکردزیرا رویه زیر مقداری را بر نمی گرداند. با این حال، رویه ها زیر، که هیچ آرگومانی ندارند و به صورت اعلام می شوند عمومی (مطابق شکل زیر) در اختیار کاربران کاربرگ قرار خواهد گرفت. بنابراین، اگر روش های ساده ای که در بالا مورد بحث قرار گرفت زیر این رویه در یک ماژول در ویرایشگر ویژوال بیسیک قرار داده شده است قالب_مرکز_و_پررنگ برای استفاده در کاربرگ اکسل و رویه در دسترس خواهد بود قالب_مرکز_و_اندازه - در دسترس نخواهد بود زیرا دارای استدلال است.

در اینجا یک راه آسان برای اجرای (یا اجرای) یک رویه وجود دارد زیر، قابل دسترسی از کاربرگ:

• رسانه ها و مطبوعات Alt + F8 (کلید را فشار دهید دگرساز و در حالی که آن را پایین نگه داشته اید، کلید را فشار دهید F8).
• در لیست ماکروهایی که ظاهر می شود، ماکرو مورد نظر خود را برای اجرا انتخاب کنید.
• رسانه ها و مطبوعات دویدن (اجرا کن)

برای انجام یک روش زیر به سرعت و به راحتی می توانید میانبر صفحه کلید را به آن اختصاص دهید. برای این:

• رسانه ها و مطبوعات Alt + F8.
• در لیست ماکروهایی که ظاهر می شود، یکی را که می خواهید میانبر صفحه کلید به آن اختصاص دهید، انتخاب کنید.
• رسانه ها و مطبوعات پارامترهای (گزینه ها) و در کادر محاوره ای ظاهر شده، میانبر صفحه کلید را وارد کنید.
• رسانه ها و مطبوعات OK و دیالوگ را ببندید درشت دستور (ماکرو).

توجه: هنگام اختصاص دادن یک میانبر صفحه کلید به یک ماکرو، مطمئن شوید که به طور استاندارد در اکسل استفاده نمی شود (به عنوان مثال، کلیدهای Ctrl + C). اگر یک میانبر صفحه کلید از قبل موجود را انتخاب کنید، دوباره به ماکرو اختصاص داده می شود و در نتیجه ممکن است کاربر به طور تصادفی ماکرو را شروع کند.

دامنه رویه VBA

قسمت دوم این آموزش در مورد دامنه متغیرها و ثابت ها و نقش کلمات کلیدی بحث می کند. عمومی и خصوصی. این کلمات کلیدی را می توان با رویه های VBA نیز استفاده کرد:

Public Sub AddToCells(i As Integer) ... End Sub

Private Sub AddToCells(i As Integer) ... End Sub

به یاد داشته باشید که اگر قبل از اعلام یک رویه VBA عملکرد or زیر کلمه کلیدی درج نشده است، ویژگی پیش فرض برای رویه تنظیم شده است عمومی (یعنی در همه جای این پروژه VBA در دسترس خواهد بود). این برخلاف اعلان های متغیر است که به طور پیش فرض چنین هستند خصوصی.

خروج زودهنگام از رویه های VBA "Function" و "Sub"

اگر نیاز به خاتمه اجرای یک رویه VBA دارید عملکرد or زیر، بدون انتظار برای پایان طبیعی آن، سپس برای این عملگرهایی وجود دارد عملکرد خروج и خروج از زیر. استفاده از این عملگرها با استفاده از یک روش ساده به عنوان مثال در زیر نشان داده شده است. عملکردA که انتظار دریافت آرگومان مثبت برای انجام عملیات بیشتر را دارد. اگر یک مقدار غیر مثبت به رویه ارسال شود، هیچ عملیات دیگری نمی‌تواند انجام شود، بنابراین باید یک پیام خطا به کاربر نشان داده شود و رویه باید بلافاصله خارج شود:

تابع VAT_Amount(sVAT_Rate As Single) به عنوان Single VAT_Amount = 0 If sVAT_Rate <= 0 سپس MsgBox "مقدار مثبت sVAT_Rate را انتظار داشت اما دریافت شد" و sVAT_Rate تابع خروج پایان یافت اگر ... تابع پایان

لطفا توجه داشته باشید که قبل از تکمیل روش عملکرد - VAT_Amount، یک تابع VBA داخلی در کد درج می شود MsgBox، که یک پنجره هشدار به کاربر نمایش می دهد.