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

1. به عنوان یک فرمول در یک کاربرگ، جایی که می تواند آرگومان ها را بگیرد و مقادیر را برگرداند.
2. به عنوان بخشی از روال VBA شما. در یک رویه Sub یا داخل توابع دیگر.
3. در قوانین قالب بندی مشروط.

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

توجه داشته باشید که توابع تعریف شده توسط کاربر ایجاد شده با VBA عموماً به طور قابل توجهی کندتر از توابع داخلی هستند. بنابراین، آنها برای موقعیت‌هایی مناسب هستند که نمی‌توانید با استفاده از توابع داخلی به نتیجه برسید یا محاسبات زیادی وجود ندارد و جریمه عملکرد مهم نیست.

نه تنها شما، بلکه همکارانتان نیز می توانند از توابع داخلی استفاده کنند. توابعی که می نویسید در کنار سایر توابع در کادر محاوره ای Function Wizard ظاهر می شوند. ممکن است از روند ایجاد توابع مرعوب شوید، اما من عجله می کنم تا به شما اطمینان دهم که بسیار ساده است.

تفاوت بین یک رویه (Sub) و یک تابع (Function) چیست؟

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

برای نشان دادن، بیایید مثالی بزنیم. به عنوان مثال، یک سری اعداد از 1 تا 100 وجود دارد و باید زوج های زوج را از فرد جدا کنید.

با استفاده از رویه (sub)، می‌توانید برای مثال، از سلول‌ها عبور کرده و با استفاده از یک fill، موارد فرد را برجسته کنید. و تابع را می توان در ستون مجاور استفاده کرد و بسته به اینکه مقدار آن زوج باشد یا خیر، TRUE یا FALSE را برمی گرداند. آن ها شما نمی توانید رنگ پر را با استفاده از یک تابع در کاربرگ تغییر دهید.

ایجاد یک تابع ساده تعریف شده توسط کاربر در VBA

بیایید یک تابع سفارشی ساده در VBA ایجاد کنیم و ببینیم که چگونه کار می کند.

در زیر کد تابعی وجود دارد که فقط اعداد را از متن باقی می گذارد و مقادیر حروف را کنار می گذارد.

اعداد تابع (متن به عنوان رشته) به عنوان نتیجه کم تیرگی طولانی به عنوان رشته برای i = 1 به لنز(متن) اگر عددی است(میان(متن، i، 1)) سپس نتیجه = نتیجه و وسط (متن، i، 1) ) اعداد بعدی = CLng(نتیجه) تابع پایان

برای اینکه همه چیز برای شما کار کند، باید وارد کنید این کدبه ماژول کتاب اگر نمی دانید چگونه این کار را انجام دهید، با مقاله نحوه ضبط ماکرو در اکسل شروع کنید.

حالا بیایید ببینیم عملکرد چگونه کار می کند، بیایید سعی کنیم از آن در یک برگه استفاده کنیم:

قبل از تجزیه و تحلیل خود تابع، اجازه دهید 2 لحظه خوشایند را که پس از ایجاد آن ظاهر شد، یادداشت کنیم:

• مانند هر عملکرد داخلی دیگر در دسترس قرار گرفت (نحوه ایجاد عملکرد پنهان، در ادامه خواهیم گفت).
• وقتی علامت "=" را وارد می‌کنید و شروع به تایپ نام تابع می‌کنید، اکسل همه موارد مطابق را نمایش می‌دهد و نه تنها توابع داخلی، بلکه توابع سفارشی را نیز نشان می‌دهد.

بیایید گام به گام تابع را تجزیه و تحلیل کنیم

حالا بیایید یک شیرجه عمیق داشته باشیم و ببینیم این ویژگی چگونه ایجاد شده است. تابع با خط شروع می شود

اعداد تابع (متن به عنوان رشته) به مدت طولانی

کلمه تابعدر مورد شروع تابع و به دنبال آن نام آن در مورد ما صحبت می کند شماره.

• نام تابع نمی تواند حاوی فاصله باشد. همچنین، اگر تابعی با نام مرجع سلول برخورد کند، نمی توانید آن را فراخوانی کنید. برای مثال، نمی‌توانید تابع ABC123 را نامگذاری کنید، زیرا آن نام به سلولی در کاربرگ اکسل نیز اشاره دارد.
• شما نباید نام تابع خود را با یک تابع موجود انتخاب کنید. اگر این کار را انجام دهید، اکسل به تابع داخلی ترجیح می دهد.
• اگر می خواهید کلمات را جدا کنید، می توانید از کاراکتر زیر خط استفاده کنید. مثلا، سوما در قیاسیک نام معتبر است

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

قسمت آخر خط اول تا زمانی کهنوع داده ای را که تابع برمی گرداند را مشخص می کند. در مورد ما، تابع مقادیر صحیح را برمی گرداند. این نیز ضروری نیست.

خط دوم و سوم تابع متغیرهای داخلی اضافی را که ما از آنها استفاده خواهیم کرد، اعلام می کنند.

Dim i به عنوان Long Dim نتیجه به عنوان رشته

متغیر منما از آن برای شمارش کاراکترها استفاده خواهیم کرد. یک متغیر نتیجهبرای ذخیره نتیجه میانی یک تابع.

وظیفه تابع این است که تمام کاراکترهای متغیر Text را مرور کند و فقط آنهایی را که عدد هستند ذخیره کند. بنابراین، ما حلقه را از 1 تا آخرین کاراکتر شروع می کنیم.

برای i = 1 To Len (متن)

Len تابعی است که تعداد کاراکترها را تعیین می کند.

خط اصلی تابع بررسی اینکه آیا کاراکتر بعدی متن یک عدد است یا خیر و اگر چنین است، آن را در متغیر نتیجه ذخیره کنید.

اگر IsNumeric(Mid(Text, i, 1)) سپس نتیجه = نتیجه و Mid(Text, i, 1)

برای این ما به یک تابع نیاز داریم عددی است- او برگشت درست است، واقعیاگر متن یک عدد باشد و نادرستدر غیر این صورت.

تابع اواسطاز آرگومان Text می گیرد منکاراکتر (به معنی 1 ، نشان می دهد که تابع اواسطفقط 1 کاراکتر می گیرد)/

تابع بعد- حلقه را می بندد برایاینجا همه چیز روشن است

اعداد = CLng (نتیجه)

با این خط متغیر متن را تبدیل می کنیم نتیجه، که شامل تمام ارقام آرگومان است متن، V مقدار عددی. و می گوییم که تابع ما چه نتیجه ای را باید خروجی دهد شماره.

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

کد بالا قسمت های مختلف یک تابع تعریف شده توسط کاربر معمولی ایجاد شده در VBA را توصیف می کند. در مقالات آتی با جزئیات بیشتری به این عناصر و همچنین نگاهی خواهیم داشت راه های مختلفاجرا توابع VBAدر اکسل

کم نور nنتیجه به عنوان عدد صحیح

nMult1 = CINT(InputBox("اولین عدد را وارد کنید:")) nMult2 = CINT(InputBox("عدد دوم را وارد کنید:")) nResult = fMultiply(nMult1، nMult2)

Selection.InsertAfter nResult Selection.Collapse wdCollapseEnd

4. برای نظر دادن کد AutoNew() ، تمام کدهای این رویه (از جمله Public Sub AutoNew() و End Sub) را برجسته کنید و روی دکمه Comment Block در نوار ابزار ویرایش کلیک کنید.

3.9. توابع زبان VBA داخلی

3.9.1. توابع داخلی چیست؟

که در زبان برنامه نویسی VBA چندین ده را ارائه می دهدتوابع داخلی. آنها در هر برنامه ای به زبان VBA در دسترس هستند و مهم نیست که ما در چه محصول نرم افزاری هستیم - Excel، Word، Access یا مثلا اتوکد. آنها بسیار فعال استفاده می شوند و در بسیاری از موقعیت ها نمی توان بدون آنها کار کرد. برنامه نویسان حرفه ای از آنها کاملاً خودکار استفاده می کنند، اما من به کاربران عادی توصیه می کنم که چندین ساعت برای آشنایی با آنها وقت بگذارند، زیرا بدون آگاهی از این توابع نمی توانند به طور موثر در VBA کار کنند. یک استدلال اضافی به نفع مطالعه آنها این است که مجموعه تقریباً یکسانی از توابع در حالت معمول یافت می شود ویژوال بیسیکو VBScript، و بسیاری از این توابع با نام ها و نحو مشابه در سایر زبان های برنامه نویسی - C++، Delphi، Java، JavaScript و غیره یافت می شوند.

که در در راهنمای VBA، توابع داخلی بر اساس حرف گروه بندی می شوند (شکل 3.2).

بسیاری از شرکت کنندگان در دوره ها این سوال را مطرح کردند: آیا در مورد این توابع به زبان روسی کمکی وجود دارد؟ متأسفانه نتوانستم چنین گواهینامه ای پیدا کنم، بنابراین سعی می کنم ارائه کنم اطلاعات مختصردر این کتاب در ادامه در مورد اکثر توابع فعال زبان VBA صحبت خواهیم کرد (توابع ریاضی مانند کسینوس یا مماس، که عبارتند از کار عملیتقریباً هرگز استفاده نمی شوند، و ما عملکردهای مالی را در نظر نخواهیم گرفت). برای صرفه جویی در فضا، نحو کامل توابع داده نمی شود: نکته اصلی این است که بفهمیم هر تابع چه کاری انجام می دهد و در چه موقعیت هایی می توان از آن استفاده کرد.

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

برنج. 3.2. توابع داخلی راهنما

3.9.2. توابع بررسی تبدیل و نوع داده

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

تبدیل از یک مقدار رشته به یک مقدار عددی هنگام دریافت یک مقدار از کاربر از طریق InputBox() ;

تبدیل یک مقدار تاریخ/زمان به یک مقدار رشته زمانی که نیاز داریم تاریخ یا زمان را بدون توجه به تنظیمات منطقه ای در رایانه کاربران به طور مداوم نمایش دهیم.

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

اغلب برای تبدیل انواع داده ها از توابعی استفاده می شود که نام آنها از پیشوند "C" (از کلمه Convert) و نام نوع داده تشکیل شده است. لیست این توابع به شرح زیر است: CBool()، CByte()، CCur()، CDate()،

CDbl()، CDec()، CInt()، CLng()، CSng()، CStr()، CVar()، CVDate()، CVERr().

با استفاده از تابع TypeName() که نام نوع داده استفاده شده را برمی گرداند، می توانید ببینید که در پایان چه اتفاقی افتاد، به عنوان مثال:

nVar1 = CINT(InputBox("مقداری را وارد کنید")) MsgBox TypeName(nVar1)

در این مورد، این تابع "عدد صحیح" را برمی گرداند.

علاوه بر این، چندین توابع دیگر برای تبدیل مفید هستند.

Str() - به شما امکان می دهد یک مقدار عددی را به یک رشته تبدیل کنید. تقریباً همان کاری را که CStr() انجام می دهد، قبل از اعداد مثبت یک فاصله درج می کند.

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

IsNumeric() و IsDate() - مقادیر را برای سازگاری بررسی کنید تا در هنگام تبدیل خطایی وجود نداشته باشد. برای بررسی انطباق با مقادیر ویژه، می توانید از توابع IsArray()، IsEmpty() استفاده کنید.

IsError()، IsMissing()، IsNull() و IsObject() . همه این توابع برمی گردند

درست یا نادرست بسته به نتایج بررسی مقدار ارسال شده به آنها.

Hex() و Oct() - تبدیل داده های اعشاری به نمایش رشته ای از مقادیر هگزا دسیمال و اکتال.

3.9.3. توابع رشته

اینها پرکاربردترین توابع هستند. آنها دائما مورد نیاز هستند و شما باید آنها را به خوبی بشناسید.

Asc() - این تابع به شما امکان می دهد کد عددی کاراکتر ارسال شده را برگردانید. به عنوان مثال، Asc("D") 68 را برمی گرداند. این تابع برای تعیین حرف بعدی یا قبلی مفید است. معمولاً همراه با تابع Chr() که عملیات معکوس را انجام می دهد استفاده می شود - کاراکتر را مطابق کد عددی آن برمی گرداند. به عنوان مثال، این کد در اکسل به شما امکان می دهد حروف الفبای روسی را به ترتیب از A تا U در سلول های A1 تا A20 بنویسید:

Dim n، nCharCode به عنوان عدد صحیح n = 1

nCharCode = Asc("A") Do while n<= 20

ActiveWorkbook.ActiveSheet.Range("A" & n).Value = Chr(nCharCode)

ساختار VBA نحو و برنامه نویسی

nCharCode = nCharCode + 1 حلقه

انواع این تابع AscB() و AscW() هستند. AscB() تنها اولین بایت کد عددی کاراکتر را برمی گرداند، در حالی که AscW() کد یونیکد را برای کاراکتر برمی گرداند.

Chr() - یک کاراکتر را با کد عددی آن برمی گرداند. علاوه بر استفاده همراه با تابع Asc() (به مثال قبلی مراجعه کنید)، در یک موقعیت دیگر نمی توانید بدون آن کار کنید: زمانی که نیاز به خروجی یک کاراکتر سرویس دارید. به عنوان مثال، باید مقدار Gazprom را (به صورت نقل قول) در Word تایپ کنیم. نقل قول یک کاراکتر سرویس است و تلاشی برای استفاده از رشته ای مانند:

Selection.Text = "Gazprom""

منجر به یک خطای نحوی خواهد شد. و به این ترتیب همه چیز خوب خواهد بود:

Selection.Text = Chr(34) & "Gazprom" & Chr(34)

انواع مختلفی از این تابع وجود دارد - ChrB() و ChrW(). آنها مشابه گزینه های مشابه برای تابع Asc() کار می کنند.

InStr() و InStrRev() برخی از محبوب ترین توابع هستند. به شما امکان می دهد دنباله ای از کاراکترها را در بدنه یک متغیر رشته شناسایی کنید و موقعیت آن را برگردانید. اگر دنباله یافت نشد، 0 برگردانده می شود. تابع InStr() از ابتدای رشته و InStrRev() از انتهای آن جستجو می کند.

Left() , Right() , Mid() - به شما امکان می دهد تعداد کاراکترهایی را که از یک متغیر رشته موجود در سمت چپ، راست یا وسط تعیین می کنید، بگیرید.

Len() - تعداد کاراکترهای رشته (طول رشته) را برمی گرداند. اغلب با حلقه ها، عملیات جایگزینی و غیره استفاده می شود.

LCase() و UCase() - یک رشته را به ترتیب به حروف کوچک و بزرگ تبدیل می کنند. اغلب برای تهیه مقادیر برای مقایسه زمانی که مورد مهم نیست (نام خانوادگی، نام شرکت ها، شهرها و غیره) استفاده می شود.

LSet() و RSet() - یک متغیر را با نمادهای دیگری بدون تغییر طول آن پر کنید (به ترتیب چپ و راست). کاراکترهای اضافی قطع می شوند و فاصله ها جایگزین کاراکترهای از دست رفته می شوند.

LTrim() ، RTrim() ، Trim() - به ترتیب فاصله ها را از چپ، راست یا هر دو سمت چپ و راست حذف کنید.

Replace() - یک دنباله از کاراکترها را با دیگری در یک رشته جایگزین می کند.

Space() و String() - رشته ای از تعداد فاصله ها یا کاراکترهایی را که مشخص می کنید به ترتیب برمی گرداند. به طور معمول برای تشکیل استفاده می شود

عملکرد-رویهنوع خاصی از رویه VBA است که یک نتیجه را برمی گرداند. توابع رویه تعریف شده توسط کاربر، مانند توابع داخلی VBA، می توانند آرگومان های اختیاری و نامگذاری شده داشته باشند. شما نمی توانید از ضبط کننده ماکرو برای ضبط عملکرد رویه استفاده کنید، اگرچه می توانید یک ماکرو ضبط شده توسط ضبط کننده را ویرایش کرده و آن را به یک تابع رویه تبدیل کنید.

تفاوت اصلی بین یک تابع رویه و سایر رویه‌ها، علاوه بر این واقعیت که توابع مقداری را برمی‌گردانند و رویه‌ها ارزشی ندارند، این است که یک تابع رویه از کلمات کلیدی استفاده می‌کند. تابعو تابع پایان.

نحو:

نام تابع ()
" بیانیه های VBA
تابع پایان

تابع کلمه کلیدی است که شروع یک تابع را اعلام می کند.

نام - نام تابع. نام توابع از قوانین مشابهی با نام‌های دیگر شناسه VBA پیروی می‌کند.

Arglist - فهرست آرگومان های این تابع، عنصر اختیاری.

نوع - هر نوع مقدار بازگشتی تابع. اگر نوع تعریف نشده باشد، نتیجه برگردانده شده توسط تابع رویه از نوع Variant است.

نام = عبارت یک تخصیص تابع است که به VBA می گوید که تابع چه مقداری را باید برگرداند، یک عنصر اختیاری. با این حال، همیشه باید یک عملگر انتساب را در توابع رویه قرار دهید.

End Function - کلمات کلیدی که به یک تابع پایان می دهند.

حتی اگر تابع هیچ آرگومان نداشته باشد (مثلاً Now، Date)، باید از پرانتز در اعلان تابع استفاده کنید.

معمولاً یک تابع برای انجام یک محاسبه و برگرداندن نتیجه طراحی شده است. هنگام اعلان تابع رویه، نام هر آرگومان ارسال شده به تابع را مشخص می کنید. نام آرگومان ها در لیست با کاما از هم جدا می شوند و باید از قوانینی پیروی کنند که برای هر شناسه VBA اعمال می شود.

در زیر فهرستی از استفاده از یک تابع رویه ساده است که فضاهای پیشرو و انتهایی را حذف می کند و تعداد کاراکترهای یک عبارت را می شمارد.

بدون استفاده از تابع، لیست به صورت زیر خواهد بود:

از این مثال ساده، فکر می‌کنم ایده اصلی استفاده از توابع رویه واضح است - بهبود خوانایی کد برنامه و کاهش آن (به عبارت دیگر، یک تابع رویه زمانی نوشته می‌شود که همان «تکه» کد بیش از 2 مورد اتفاق بیفتد. -3 بار در کد برنامه). در واقع، اگر تابع رویه ما نه از یک خط، بلکه مثلاً 10 خط تشکیل شده باشد. و کد برنامه 5 بار از این تابع رویه استفاده می کند، سپس فهرست کلی برنامه 38 خط کوتاهتر خواهد بود.

همانطور که قبلاً گفته شد، VBA همه آرگومان ها را به عنوان انواع Variant به یک رویه تابع منتقل می کند. شما می توانید انواع داده های خاصی را برای هر آرگومان در لیست آرگومان اعلام کنید.

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

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

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

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

تابع سفارشی و مراحل فرعی در VBA

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

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

استدلال ها

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

رویه VBA "عملکرد"

ویرایشگر VBA رویه را تشخیص می دهد تابع

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

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

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

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

نمونه ای از یک روش VBA "تابع": انجام یک عملیات ریاضی با 3 عدد

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

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

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

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

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

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

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

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

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

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

جمع منهای(10، 5، 2)

روش VBA "Sub"

ویرایشگر VBA می‌داند که رویه‌ای پیش روی او قرار دارد زیر، هنگامی که با گروهی از دستورات محصور شده بین دستورهای باز و بسته زیر روبرو می شود:

Sub...End Sub

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

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

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

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

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

روش VBA "Sub": مثال 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" از یک کاربرگ

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

در اینجا یک راه ساده برای اجرای (یا اجرای) رویه وجود دارد زیر، موجود در کاربرگ:

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

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

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

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

دامنه رویه VBA

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

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

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

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

تابع VAT_Amount(sVAT_Rate As Single) به عنوان Single VAT_Amount = 0 اگر sVAT_Rate<= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function

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

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

عملگرهای ریاضی

عملگرهای اصلی ریاضی VBA در جدول زیر فهرست شده اند.

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

عملگرهای رشته ای

عملگر رشته اصلی در Excel VBA عملگر الحاق است & (ادغام):

اپراتورهای مقایسه

عملگرهای مقایسه برای مقایسه دو عدد یا رشته و برگرداندن یک مقدار بولی مانند استفاده می‌شوند بولی(درست یا غلط). اپراتورهای اصلی مقایسه Excel VBA در این جدول آورده شده است:

عملگرهای منطقی

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

جدول بالا تمام عملگرهای Boolean موجود در VBA را فهرست نمی کند. لیست کاملی از عملگرهای منطقی را می توان در مرکز توسعه دهندگان ویژوال بیسیک یافت.

توابع داخلی

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

تابع	عمل
شکم	قدر مطلق یک عدد معین را برمی گرداند. شکم (-20)مقدار 20 را برمی گرداند. شکم (20)مقدار 20 را برمی گرداند.
Chr	کاراکتر ANSI مربوط به مقدار عددی پارامتر را برمی‌گرداند. Chr(10)شکست خط را برمی گرداند. Chr(97)شخصیت را برمی گرداند آ.
تاریخ	تاریخ فعلی سیستم را برمی‌گرداند.
تاریخ افزودن	یک بازه زمانی مشخص را به یک تاریخ معین اضافه می کند. نحو توابع: تاریخ افزودن( فاصله , عدد , تاریخ ) بحث کجاست فاصلهنوع فاصله زمانی اضافه شده به یک داده را مشخص می کند تاریخبه مقدار مشخص شده در استدلال عدد . بحث و جدل فاصلهمی تواند یکی از مقادیر زیر را بگیرد: DateAdd("d"، 32، "01/01/2015") 32 روز به تاریخ 01/01/2015 اضافه می کند و بنابراین تاریخ 02/02/2015 را برمی گرداند. DateAdd ("ww"، 36، "01/01/2015") 36 هفته به تاریخ 01/01/2015 اضافه می کند و تاریخ 09/09/2015 را برمی گرداند.
DateDiff	تعداد فواصل زمانی مشخص شده بین دو تاریخ معین را محاسبه می کند. DateDiff("d"، "01/01/2015"، "02/02/2015")تعداد روزهای بین تاریخ های 01/01/2015 و 02/02/2015 را محاسبه می کند و نتیجه را 32 برمی گرداند. DateDiff ("ww"، "01/01/2015"، "03/03/2016")تعداد هفته های بین تاریخ های 01/01/2015 و 03/03/2016 را محاسبه می کند و نتیجه را 61 برمی گرداند.
روز	یک عدد صحیح مربوط به روز ماه در تاریخ معین را برمی گرداند. مثال: روز ("01/29/2015")عدد 29 را برمی گرداند.
ساعت	یک عدد صحیح مربوط به تعداد ساعت در زمان معین را برمی گرداند. مثال: ساعت ("22:45:00")عدد 22 را برمی گرداند.
InStr	یک عدد صحیح و دو رشته را به عنوان آرگومان می گیرد. موقعیت وقوع رشته دوم را در رشته اول برمی گرداند و جستجو را در موقعیت مشخص شده توسط عدد صحیح شروع می کند. InStr(1، "اینجا کلمه ای است که به دنبالش هستید"، "کلمه")عدد 13 را برمی گرداند. InStr(14، "در اینجا کلمه جستجو، و در اینجا یک کلمه جستجوی دیگر"، "کلمه")عدد 38 را برمی گرداند. توجه داشته باشید:ممکن است آرگومان عدد مشخص نباشد، در این صورت جستجو از اولین کاراکتر رشته مشخص شده در آرگومان دوم تابع شروع می شود.
بین المللی	قسمت صحیح عدد داده شده را برمی گرداند. مثال: Int(5.79)نتیجه 5 را برمی گرداند.
Isdate	برمی گرداند درست است، واقعیاگر مقدار داده شده یک تاریخ باشد، یا نادرست- اگر تاریخ نباشد. IsDate («01/01/2015»)برمی گرداند درست است، واقعی; IsDate(100)برمی گرداند نادرست.
IsError	برمی گرداند درست است، واقعی، اگر مقدار داده شده یک خطا باشد یا نادرست- اگر خطا نباشد.
مفقود شده است	نام آرگومان رویه اختیاری به عنوان آرگومان به تابع ارسال می شود. مفقود شده استبرمی گرداند درست است، واقعی، اگر هیچ مقداری برای آرگومان رویه مورد نظر ارسال نشود.
عددی است	برمی گرداند درست است، واقعیاگر مقدار داده شده را بتوان به عنوان یک عدد در نظر گرفت، در غیر این صورت برمی گردد نادرست.
ترک کرد	تعداد مشخص شده کاراکتر را از ابتدای رشته ارسال شده برمی گرداند. سینتکس تابع به این صورت است: ترک کرد( خط , طول ) جایی که خطرشته اصلی است و طول- تعداد کاراکترهای بازگشتی، از ابتدای خط شمارش می شود. چپ ("abvgdezziklmn"، 4)رشته "abvg" را برمی گرداند. چپ ("abvgdezziklmn"، 1)رشته "a" را برمی گرداند.
لن	تعداد کاراکترهای یک رشته را برمی گرداند. مثال: لن ("abvgdez")عدد 7 را برمی گرداند.
ماه	یک عدد صحیح مربوط به ماه در تاریخ داده شده را برمی گرداند. مثال: ماه ("01/29/2015")مقدار 1 را برمی گرداند.
اواسط	تعداد مشخصی از کاراکترها را از وسط رشته ارسال شده برمی گرداند. نحو توابع: اواسط( خط , شروع کنید , طول ) جایی که خطرشته اصلی است شروع کنید- موقعیت ابتدای خط استخراج شده، طول- تعداد کاراکترهایی که باید استخراج شوند. Mid («abvgdezziklmn»، 4، 5)رشته "where" را برمی گرداند. Mid («abvgdezziklmn»، 10، 2)رشته "cl" را برمی گرداند.
دقیقه	یک عدد صحیح مربوط به تعداد دقیقه در یک زمان معین را برمی‌گرداند. مثال: دقیقه («22:45:15»)مقدار 45 را برمی گرداند.
اکنون	تاریخ و زمان فعلی سیستم را برمی‌گرداند.
درست	تعداد مشخص شده کاراکتر را از انتهای رشته ارسال شده برمی گرداند. نحو توابع: درست( خط , طول ) جایی که خطرشته اصلی است و طول– این تعداد کاراکترهایی است که باید از انتهای رشته داده شده استخراج شوند. راست ("abvgdezziklmn"، 4)رشته "clmn" را برمی گرداند. راست ("abvgdezziklmn"، 1)رشته "n" را برمی گرداند.
دومین	یک عدد صحیح مربوط به تعداد ثانیه در زمان معین را برمی‌گرداند. مثال: دوم («22:45:15»)مقدار 15 را برمی گرداند.
مربع	جذر مقدار عددی ارسال شده به عنوان آرگومان را برمی گرداند. مربع (4)مقدار 2 را برمی گرداند. مربع (16)مقدار 4 را برمی گرداند.
زمان	زمان فعلی سیستم را برمی‌گرداند.
باز شده	بالانویس بعد آرایه مشخص شده را برمی گرداند. توجه داشته باشید:برای آرایه های چند بعدی، یک آرگومان اختیاری می تواند مشخص کند که کدام بعد باید برگردانده شود. اگر مشخص نشده باشد، پیش‌فرض ۱ است.