هر کس می تونه به شما اکسل یاد بده. اما من ، آنچه را که می توانيد با آن انجام دهيد ...

اشتراک در خبرنامه

جهت عضویت در خبرنامه لطفا ایمیل خود را ثبت نمائید

Captcha

آمار بازدید

  • بازدید امروز : 1
  • بازدید دیروز : 224
  • بازدید کل : 79380

ایجاد تابع سفارشی با استفاده از VBA


ایجاد تابع سفارشی با استفاده از VBA

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

همچنین…

اکسل به شما امکان ایجاد تابع سفارشی با استفاده از VBA را نیز می دهد.

بله ، درست است!

تابع تعریف شده توسط کاربر ، به طور خلاصه UDF.

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

شما نمی خواهید؟

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

بنابراین ، امروز ، می خواهم مواردی را که باید در مورد ایجاد UDF بدانید به اشتراک بگذارم.

البته ، این یک راهنمای کلی است.

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

... پس با من باشید اگر می خواهید در دو دقیقه آینده سطح VBA خود را یک گام افزایش دهید.

چرا باید یک تابع سفارشی ایجاد کنید

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

ولی…

... گاهی اوقات ، در موقعیت های خاص ، باید UDF ایجاد کنید.

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

1. هنگامی که هیچ تابع استاندارد اکسل برای این کار وجود ندارد

گاهی اتفاق می افتد که باید چیزی را محاسبه کنید و تابع خاصی برای این کار وجود ندارد.

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

Function MyWordCount(rng As Range) As Integer

MyWordCount = UBound(Split(rng.Value، " ")، 1) + 1

End Function

چرا باید یک تابع سفارشی ایجاد کنید

2. جایگزین فرمول پیچیده کنید

اگر با فرمول ها کار می کنید ، من مطمئن هستم که شما این موضوع را می دانید که نوشتن فرمول های پیچیده دشوار است و بعضی اوقات درک آن توسط دیگر کاربران نیز دشوار است.

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

چرا باید یک تابع سفارشی ایجاد کنید

پس از ایجاد UDF ، دیگر لازم نیست که آن فرمول پیچیده را دوباره و دوباره بنویسید.

3. هنگامی که  نمی خواهید از فرآیند SUB استفاده کنید

می توانید برای انجام محاسبه از کد VBA استفاده کنید اما کدهای VBA پویا نیستند.

اگر می خواهید محاسبه خود را به روز کنید ، مجدداً باید آن کد را اجرا کنید.

اما اگر کد را به یک تابع تبدیل کنید ، دیگر لازم نیست که آن را هر بار اجرا کنید.

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

به طور معمول ، برای ایجاد یک تابع VBA ، باید مراحل زیر را دنبال کنید:

  • فرآیند خود را به عنوان یک تابع اعلام کنید.
  • مولفه ها و نوع داده آنها را تعریف کنید.
  • برای محاسبه مقدار مورد نظر , کد را اضافه کنید.

بگذارید یک مثال ساده ذکر کنم ، اما قبل از آن ، فایل اکسل مربوطه را بارگیری کنید تا در ادامه همراه باشید.

 توجه کنید:

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

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

منظورم را دریافت کردید؟

بنابراین اجازه دهید با استفاده از مراحل زیر این تابع VBA را ایجاد کنیم.

  • اول از همه ، ویرایشگر ویژوال بیسیک را با استفاده از کلید میانبر ALT + F11 باز کنید , یا به زبانه Developer بروید و  روی دکمه Visual Basic کلیک کنید.

دسترسی به ویرایشگر ویژوال بیسیک در اکسل

  • درج یک ماژول: روش اول- در پنجره Project Explorer پروژه ای را انتخاب کنید که می خواهید ماژول به آن اضافه شود. روی یکی از اشیاء فایل اکسل که در پنجره Project Explorer مشاهده می کنید راست کلیک کنید. در لیست باز شده مکان نما را روی گزینه Insert قرار دهید و از لیست کشویی روی Module کلیک کنید.

درج یک ماژول در اکسل

روش دوم- در پنجره Project Explorer پروژه ای را انتخاب کنید که می خواهید ماژول به آن اضافه شود. سپس از زبانه Insert نوار ابزار استاندارد گزینه Module را انتخاب کنید.

درج یک ماژول در اکسل

  • نکته بعدی تعریف یک نام برای تابع است و در اینجا من از myDayName استفاده می کنم. بنابراین شما باید Function mydayName را بنویسید.

تعریف یک نام برای تابع سفارشی در اکسل

چرا Function قبل از نام تابع؟

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

  • پس از آن ، شما باید مولفه هایی را برای UDF تعریف کنید. بنابراین پرانتز شروع را وارد کنید و InputDate as Date را تایپ کنید.

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

  • در اینجا InputDate نام مولفه است و date نوع داده آن است. همیشه بهتر است نوع داده را برای مولفه تعریف کنید.
  • اکنون ، پرانتز را ببندید و As String بنویسید. در اینجا شما نوع داده نتیجه تابع را تعریف می کنید. همانطور که ذکر شد , می خواهیم نام روز به صورت متن بازگردانده شود ، بنابراین نوع داده آن باید به صورت String باشد.

ایجاد تابع سفارشی در اکسلدر پایان ، کلید ENTER را فشار دهید.

تا این مرحله ، نام تابع ، مولفه آن ، نوع داده مولفه و نوع داده خروجی تابع , تعریف شده است و شما در ماژول خود چیزی شبیه به این دارید:

دستور اعلان تابع سفارشی در اکسل

اکنون در قسمت ما بین Function و End Function ، باید کد محاسبات خود را وارد کنید.

در اکسل یک تابع برگه با نام Text وجود دارد و در اینجا از همان استفاده می کنیم.

برای این کار ، باید کد زیر را بنویسید:

myDayName = WorksheetFunction.Text(InputDate, "dddddd")

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

ایجاد تابع سفارشی در اکسل

  • اکنون کد خود را ذخیره و به برگه اکسل بازگردید.

اگر در سلول B2 ، فرمول  myDayName(A2) = را وارد کرده و اینتر را فشار دهید , نام روز را خواهید داشت.

ایجاد تابع سفارشی در اکسل

تبریک می گویم! شما اولین تابع سفارشی خود را ایجاد کردید.

این لحظه شادی واقعی است.

اینطور نیست؟

نظر خود را در بخش دیدگاه کاربران تایپ کنید.

نحوه عملکرد این تابع و مقدار برگشتی

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

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

بگذارید آن را به سه قسمت تقسیم کنیم:

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

حال بیایید آن را با جزئیات بیان کنیم؛

لحظه ای که نام تابع را در یک سلول وارد می کنید و به سلول دیگر برای مقدار مولفه (ها) ارجاع می دهید , کد اجرا می ­شود و از مقداری که به عنوان مولفه ارجاع داده­ اید برای بازگشت نتیجه استفاده می کند.

دقیق­ تر بیان می کنم:

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

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

تاریخ را از مولفه InputDate گرفته و نام روز را برمی گرداند.


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

نکته مهم: وقتی کدی را برای یک تابع سفارشی می نویسید باید مراقب باشید که حتما مقدار مورد نظر برای بازگشت آن کد , به نام تابع اختصاص یابد.

نحوه بهبود تابع سفارشی

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

ولی…

نکته ای وجود دارد که باید مراقب باشید , کدی که شما برای تابع استفاده کرده اید باید به اندازه کافی خوب باشد تا بتواند تمام احتمالات را در نظر بگیرد.

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

ولی…

اگر مقداری که مشخص می کنید تاریخ نباشد ؟

و یا اگر سلولی که به آن ارجاع می­ دهید خالی باشد , چه؟

ممکن است احتمالات دیگری نیز وجود داشته باشد ، اما  مطمئن هستم که شما منظور من را گرفتید.

درسته؟

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

بسیار خوب!

اول از همه ، شما باید نوع داده مولفه را تغییر داده و کد زیر را استفاده کنید:

InputDate As Variant

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

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

بررسی اول این است که سلول خالی است یا خیر.

و برای این کار ، باید از کد زیر استفاده کنید:

If InputDate = "" Then

"" = myDayName

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

یک مشکل حل شد ، بگذارید به مسئله بعدی برویم.

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

بنابراین ، برای این کار ، باید شرایطی را نیز ایجاد کنید که باید بررسی کند که مقدار ذکر شده یک تاریخ واقعی است یا خیر.

کد:

If IsDate(InputDate) = False Then

 "" = myDayName 

توجه: در اینجا من برای هر دو شرط از یک blank , ("") استفاده می کنم ، به طوری که در صورت داشتن داده های طولانی می توانید به راحتی مقادیر را فیلتر کنید که مقدار ورودی خالی نباشد.

بنابراین پس از افزودن شرایط فوق ، کد به صورت زیر خواهد بود:

Function myDayName(InputDate As Variant) As String

If InputDate = "" Then

 "" = myDayName

Else

If IsDate(InputDate) = False Then

"" = myDayName

Else

myDayName = WorksheetFunction.Text(InputDate, "dddddd")

End If

End If

End Function

اطمینان دارم هنوز هم می توانید تغییراتی در این تابع ایجاد کنید. بیایید در بخش دیدگاه کاربران صحبت کنیم.

نحوه استفاده از یک تابع سفارشی VBA

تا این مرحله ، با نحوه ایجاد یک تابع VBA در اکسل کاملاً آشنا شدید.

اما پس از ایجاد ، باید بدانید که چگونه می توانید از آن استفاده کنید.

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

بیایید شروع کنیم:

1. به سادگی در یک برگه اکسل

چرا ما یک تابع سفارشی ایجاد می کنیم؟

برای استفاده از آن در برگه اکسل.

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

 

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

از زبانه Formula گزینه Insert Function را انتخاب کنید , در پنجره محاوره ای باز شده دسته بندی User Defined را انتخاب کنید.از این لیست می توانید UDF مورد نظر خود را وارد کنید.

2. استفاده در سایر فرآیندها و توابع

همچنین می توانید از یک تابع در توابع دیگر یا در یک فرآیند Sub استفاده کنید.

از کد VBA زیر برای دریافت نام روز تاریخ جاری می توانید استفاده کنید.

()Sub todayDay

MsgBox "Today is" & myDayName(Date)

End Sub

 

 توجه: حتماً در این پست دامنه یك UDF را بخوانید تا در مورد استفاده توابع در سایر فرآیند­ها اطلاعات بیشتری کسب كنید.

3. دسترسی به توابع از فایل های دیگر

اگر UDF را در یک فایل دارید و می خواهید از آن در فایل دیگری یا در تمام فایل­ها استفاده کنید این کار را با ایجاد یک افزونه برای آن انجام می دهید.

این مراحل ساده را دنبال کنید:

اول از همه ، شما باید فایل اکسل را (که در آن کد تابع سفارشی دارید) به عنوان یک افزونه ذخیره کنید.برای این:  

از زبانه File گزینه Save As را انتخاب کنید , در پنجره محاوره ای باز شده فرمت فایل را Excel Add-Ins (.xalm) انتخاب کنید.

 

  • پس از آن ، بر روی افزونه مورد نظر خود دوبار کلیک کرده و آن را نصب کنید.

خودشه.

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

راه های مختلف ایجاد یک تابع سفارشی VBA [سطح پیشرفته]

تا این مرحله ، شما می توانید یک تابع سفارشی در VBA ایجاد کنید.

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

  • بدون هیچ مولفه ای
  • فقط با یک مولفه
  • با مولفه های اجباری و اختیاری
  • با مولفه های متعدد
  • استفاده از آرایه به عنوان مولفه

بیایید ادامه بدیم...

*جهت دریافت کتاب های آموزش ماکرونویسی با اکسل روی تصویر کلیک کنید*

 

کتاب آموزش ماکرو نویسی با اکسل

  انتشار : ۷ آبان ۱۳۹۸               تعداد بازدید : 2231

برچسب های مهم

دیدگاه های کاربران (0)

حقوق مادی و معنوی این سایت متعلق به exceldepo می باشد.

فید خبر خوان    نقشه سایت    تماس با ما