عملگرهای VBA و توابع داخلی. توابع سفارشی در VBA برای Excel توابع استاندارد vba

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

توابع داخلی ریاضی؛

توابع ریاضی در VBA نشان داده نمی شوند.

توابع قالب بندی داده ها؛

توابع تبدیل را تایپ کنید

توابع داخلی ریاضی

ارزش برگشتی

قدر مطلق عدد

arctan(x) - مماس قوس مقدار پارامتر، بر حسب رادیان

sin(x) - سینوس زاویه را از مقدار پارامتر، بر حسب رادیان برمی‌گرداند

cos(x) - کسینوس زاویه مشخص شده بر حسب رادیان

tg(x) - مماس زاویه را از مقدار پارامتر مشخص شده بر حسب رادیان برمی گرداند

e x - عدد e را به توان مشخص شده برمی‌گرداند، جایی که e پایه لگاریتم طبیعی است.

ln(x) - لگاریتم طبیعی مقدار یک عبارت عددی را برمی گرداند

- جذر یک عبارت عددی را برمی گرداند

عدد تصادفی از بازه )

فرمت های اعداد نامگذاری شده

نام قالب	شرح
	عدد بدون جداکننده هزاران
	دو رقم را در سمت راست نقطه اعشار نشان می دهد
	یک رقم را در سمت چپ و دو رقم را در سمت راست نقطه اعشار نشان می دهد
	یک رقم را در سمت چپ و دو رقم را در سمت راست نقطه اعشار نمایش می دهد و جداکننده هزاران را خروجی می دهد
	یک عدد را به صورت درصد نمایش می دهد و دو رقم را در سمت راست نقطه اعشار نشان می دهد
	از قالب اعشاری شناور استفاده می کند
	اگر عدد 0 باشد خیر و در غیر این صورت بله نمایش داده می شود
	اگر عدد 0 باشد False و در غیر این صورت True را نشان می دهد
	اگر عدد 0 باشد خاموش و در غیر این صورت روشن نمایش داده می شود

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

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

تعریف تابع

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

به عنوان مثال، می توانید دو عدد را به یک تابع ارسال کنید و سپس می توانید انتظار داشته باشید که تابع ضرب خود را به تماس گیرنده شما برگرداند.

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

قبل از استفاده از یک تابع، باید آن تابع خاص را تعریف کنیم. متداول ترین روش برای تعریف یک تابع در VBA استفاده از کلمه کلیدی Function به دنبال یک نام تابع منحصر به فرد است و ممکن است حاوی لیست پارامترها و عبارتی با کلمه کلیدی End Function باشد که پایان تابع را نشان می دهد. در زیر سینتکس اولیه آمده است.

نحو

یک دکمه اضافه کنید و تابع زیر را اضافه کنید.

تابع Functionname(parameter-list) عبارت 1 دستور 2 عبارت 3 ....... عبارت n تابع پایان

مثال

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

تابع find_area (طول به عنوان دو برابر، عرض اختیاری به عنوان متغیر) اگر IsMissing(Width) سپس find_area = طول * طول دیگر find_area = طول * عرض انتهای اگر تابع پایان

فراخوانی تابع

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

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

عملکرد نام ([فهرست_ استدلال ها])
[دستورالعمل ها]
[نام = اصطلاح]
[دستورالعمل ها]
[نام = اصطلاح]
عملکرد پایانی

یک مقدار همیشه حداقل یک بار و معمولاً زمانی که اجرای تابع به پایان می رسد به نام تابع اختصاص داده می شود. برای ایجاد یک تابع سفارشی، با ایجاد یک ماژول VBA شروع کنید (می توانید از یک ماژول موجود نیز استفاده کنید). کلمه کلیدی Function و به دنبال آن نام تابع و لیست آرگومان های آن (در صورت وجود) را در پرانتز وارد کنید. همچنین می توانید نوع داده مقدار بازگردانده شده توسط تابع را با استفاده از کلمه کلیدی As اعلام کنید (این اختیاری است اما توصیه می شود). کد VBA را که اقدامات لازم را انجام می دهد، قرار دهید و مطمئن شوید که مقدار مورد نیاز اختصاص داده شده است متغیر رویه، مطابق با نام تابع، حداقل یک بار در بدنه تابع. تابع با عبارت End Function به پایان می رسد.

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

تابع را می توان به یکی از روش های زیر فراخوانی کرد:

آن را از رویه دیگری فراخوانی کنید.
آن را در فرمول کاربرگ قرار دهید.
شامل فرمول قالب بندی شرطی؛
آن را در پنجره اشکال زدایی VBE فراخوانی کنید ( فوری). این روش معمولاً در مرحله آزمایش اعمال می شود (شکل 3).

برنج. 3. فراخوانی یک تابع در پنجره اشکال زدایی

برخلاف رویه ها، توابع در یک کادر محاوره ای ظاهر نمی شوند. ماکرو(منو توسعه دهنده –> کد –> ماکروها; یا Alt+F8).

آرگومان های تابع

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

توابع بدون آرگومان

اکسل چندین توابع داخلی دارد که هیچ آرگومان نمی‌گیرند، مانند RAND، TODAY، TODAY. ایجاد توابع سفارشی مشابه آسان است. مثلا:

تابع کاربر()
" نام کاربری را برمی گرداند
User = Application.UserName
عملکرد پایانی

هنگامی که فرمول =User() را وارد می کنید، سلول نام را برمی گرداند کاربر فعلی(شکل 4). لطفاً توجه داشته باشید که هنگام استفاده از یک تابع بدون آرگومان در فرمول کاربرگ، باید پرانتزهای خالی را وارد کنید.

برنج. 4. Formula =User() نام کاربر فعلی را برمی گرداند

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

Application.Volatile True

متد Volatile شیء Application یک آرگومان دارد (درست یا نادرست). اگر تابع به صورت برجسته شده باشد فرار(قابل تغییر)، هر زمان که سلولی در کاربرگ تغییر کند دوباره محاسبه می شود. هنگامی که از آرگومان False متد Volatile استفاده می کنید، تابع تنها زمانی دوباره محاسبه می شود که یکی از آرگومان های آن در نتیجه محاسبه مجدد تغییر کند.

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

تابع StaticRand()
" عدد تصادفی را برمی‌گرداند که هنگام محاسبه مجدد فرمول‌ها تغییر نمی‌کند
StaticRand = Rnd()
عملکرد پایانی

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

تابع با یک آرگومان

برنج. 5. جدول کمیسیون

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

IF(AND(D1>=0,D1<=9999,99);D1*0,08;ЕСЛИ(И(D1>= 10000; D1<=19999,99);D1*0,105; ЕСЛИ(И(D1>=20000;D1<=39999,99);D1*0,12;ЕСЛИ(D1>=40000;D1*0.14))))

این فرمول به چند دلیل شکست می خورد. اولاً، پیچیده است، تایپ کردن و متعاقباً ویرایش آن آسان نیست. دوم، مقادیر به شدت در فرمول تعریف شده اند و تغییر آن را سخت می کند. استفاده از VLOOKUP بسیار بهتر است (شکل 6).

برنج. 6. استفاده از تابع VLOOKUP برای محاسبه کمیسیون

حتی بهتر است (پس نیازی به استفاده از جدول جستجو ندارید) یک تابع سفارشی ایجاد کنید:

کمیسیون عملکرد (فروش)
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
" محاسبه کمیسیون فروش
Case Sales را انتخاب کنید
مورد 0 تا 9999.99: کمیسیون = فروش * Tier1
مورد 10000 تا 19999.99: کمیسیون = فروش * Tier2
مورد 20000 تا 39999.99: کمیسیون = فروش * Tier3
Case Is >= 40000: کمیسیون = فروش * Tier4
پایان انتخاب کنید
عملکرد پایانی

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

کمیسیون (B2)

تابع DoubleCell()
DoubleCell = Range("Al") * 2
عملکرد پایانی

اگرچه این تابع کار می کند، اما در برخی موارد نتایج نادرستی ایجاد می کند. دلیل آن این است که موتور محاسبه اکسل محدوده هایی را که به عنوان آرگومان ارسال نمی شوند در نظر نمی گیرد. در نتیجه، گاهی اوقات تمام مقادیر مرتبط قبل از اینکه تابع مقداری را برگرداند، ارزیابی نمی شود. همچنین باید یک تابع DoubleCell بنویسید که مقدار سلول A1 را به عنوان آرگومان می گیرد.

تابع DoubleCell (سلول)
دو سلول = سلول * 2
عملکرد پایانی

تابع با دو آرگومان

تصور کنید که مدیر مورد بحث در بالا سیاست جدیدی را اجرا می کند که برای کاهش گردش کار کارکنان طراحی شده است: مبلغ کل کمیسیون های قابل پرداخت به ازای هر سالی که کارمند برای شرکت کار کرده است 1٪ افزایش می یابد. اجازه دهید تابع سفارشی کمیسیون را طوری تغییر دهیم که دو آرگومان بگیرد. آرگومان جدید نشان دهنده تعداد سال هایی است که کارمند برای شرکت کار کرده است. بیایید این را صدا کنیم ویژگی جدیدکمیسیون 2:

عملکرد کمیسیون2 (فروش، سال) به عنوان مجرد
" محاسبه کمیسیون فروش بر اساس
"طول خدمات
کمیسیون 2 = کمیسیون (فروش) + _
(کمیسیون (فروش) * سال / 100)
عملکرد پایانی

تابع با آرگومان آرایه

توابع می توانند یک یا چند آرایه را به عنوان آرگومان بگیرند، آن آرایه(ها) را پردازش کرده و یک مقدار واحد را برگردانند. تابع زیر یک آرایه را به عنوان آرگومان می گیرد و مجموع عناصر آن را برمی گرداند.

تابع SumArray(List) به عنوان Double
کم نور مورد به عنوان نوع
SumArray = 0
برای هر مورد در لیست
اگر WorksheetFunction.IsNumber(Item) سپس _
SumArray = SumArray + Item
مورد بعدی
عملکرد پایانی

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

تابع با آرگومان های اختیاری

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

ترک کرد( متن، تعداد_شخصیت)

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

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

تابع User2 (حروف بزرگ اختیاری به عنوان متغیر)
اگر IsMissing (حروف بزرگ) سپس بزرگ = نادرست
User2 = Application.UserName
اگر حروف بزرگ باشد، User2 = UCase (User2)
عملکرد پایانی

اگر آرگومان False یا حذف شده باشد، نام کاربری بدون هیچ تغییری برگردانده می شود. اگر آرگومان تابع True باشد، نام کاربری به صورت کاراکتر برگردانده می شود. حروف بزرگ(با استفاده از تابع VBA Ucase). به اولین دستور تابع توجه کنید - حاوی تابع IsMissing VBA است که وجود یک آرگومان را تعیین می کند. اگر هیچ آرگومانی وجود نداشته باشد، عبارت، متغیر بزرگ را روی False (پیش‌فرض) تنظیم می‌کند.

تابع VBA یک آرایه را برمی گرداند

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

تابع MonthNames یک مثال ساده از استفاده از تابع Array در یک تابع تعریف شده توسط کاربر است.

تابع MonthNames()
MonthNames = آرایه (" ژانویه " , " فوریه " , " March " , _
"آوریل"، "مه"، "ژوئن"، "ژوئیه"، "مرداد"، _
" سپتامبر اکتبر نوامبر دسامبر "
عملکرد پایانی

تابع MonthNames آرایه افقی از نام ماه ها را برمی گرداند. در کاربرگ، 12 سلول را انتخاب کنید، فرمول =MonthNames() را وارد کنید و کلیک کنید . اگر می خواهید یک آرایه عمودی از نام های ماه ایجاد کنید، محدوده عمودی را انتخاب کنید، فرمول =TRANSPOSE(MonthNames()) را وارد کنید و فشار دهید. .

تابعی که مقدار خطا را برمی گرداند

xlErrDivO (برای خطای #DIV/0!)؛
xlErrNA (برای خطای #N/A)؛
xlErrName (برای خطای #NAME؟)؛
xlErrNull (برای خطای #NULL!)؛
xlErrNum (برای خطای #NUM!)؛
xlErrRef (برای خطای #REF!)؛
xlErrValue (برای خطای #VALUE!).

در زیر تابع RemoveVowels تبدیل شده است (به مثال در ابتدا مراجعه کنید). هنگامی که آرگومان آرگومان متنی نباشد از ساختار If-Then برای انجام یک عمل جایگزین استفاده می شود. این تابع تابع Excel ISTEXT را فراخوانی می کند که تعیین می کند آیا آرگومان حاوی متن است یا خیر. اگر سلول حاوی متن باشد، تابع یک نتیجه عادی را برمی‌گرداند. اگر سلول حاوی غیر متن باشد (یا خالی باشد)، آنگاه تابع خطای #VALUE را برمی‌گرداند!

تابع RemoveVowels3(txt) As Variant
" تمام حروف صدادار را از آرگومان Txt حذف می کند
اگر آرگومان رشته ای نباشد، یک خطای #VALUE! برمی گرداند
تاریکی من تا زمانی
RemoveVowels3 = " "
اگر Application.WorksheetFunction.IsText(txt) سپس
برای i = 1 To Len (txt)
If Not UCase (Mid(txt, i, 1)) " " را پسندید سپس
RemoveVowels3 = RemoveVowels3 & Mid(txt, i, 1)
پایان اگر
بعدی منم
دیگر
RemoveVowels3 = CVERr(xlErrValue)
پایان اگر
عملکرد پایانی

توجه داشته باشید که نوع داده برای مقدار بازگشتی تابع تغییر کرده است. از آنجایی که تابع می تواند چیزی غیر از رشته را برگرداند، نوع داده به Variant تغییر کرده است.

تابع با تعداد نامحدود آرگومان

امکان ایجاد توابع سفارشی با تعداد نامحدود آرگومان وجود دارد. از یک آرایه به عنوان آخرین (یا تنها) آرگومان استفاده کنید و قبل از آن کلمه کلیدی ParamArray را قرار دهید (ParamArray فقط به آخرین آرگومان در لیست آرگومان یک رویه اشاره دارد. این همیشه یک نوع داده Variant است و همیشه یک آرگومان اختیاری است). تابع زیر مجموع همه آرگومان ها را برمی گرداند که می تواند یک مقدار (سلول) یا یک محدوده باشد.

تابع SimpleSum(ParamArray arglist() As Variant) As Double
سلول کم نور به عنوان محدوده
Dim arg As Variant
برای هر arg در arglist
برای هر سلول در arg
SimpleSum = SimpleSum + سلول
سلول بعدی
ارگ بعدی
عملکرد پایانی

اشکال زدایی تابع

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

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

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

در این حالت مقادیر دو متغیر Ch و i در پنجره اشکال زدایی نمایش داده می شود ( فوری) هر زمان که برنامه با دستور Debug.Print مواجه شد. مکان نما را در هر نقطه از رویه Test() قرار دهید و F5 را فشار دهید. روی انجیر شکل 7 نتیجه موردی را نشان می دهد که تابع آرگومان TusconArizona را می گیرد.

با استفاده از روش MacroOptions

می‌توانید از روش MacroOptions شی Application استفاده کنید، که به شما امکان می‌دهد توابعی را که به عنوان بخشی از توابع داخلی اکسل توسعه داده‌اید، اضافه کنید. این روش اجازه می دهد:

اضافه کردن یک توضیح تابع (شروع با Excel 2010؛
دسته تابع را مشخص کنید.
توضیحی از آرگومان های تابع اضافه کنید.

تابع توصیف ()
Dim FuncName به عنوان رشته
کم نور FuncDesc به عنوان رشته
کم نور FuncCat به مدت طولانی
کم رنگ Arg1Desc به عنوان رشته، Arg2Desc به عنوان رشته
FuncName = "رسم"
FuncDesc = "محتوای سلولی با محدوده تصادفی"
FuncCat = 5 اینچ مراجع و آرایه ها
Arg1Desc = "محدوده ای که حاوی مقادیر است"
Arg2Desc = " (اختیاری) اگر نادرست یا وجود نداشت، _
تابع Rnd دوباره محاسبه نمی شود. "
Arg2Desc = Arg2Desc و "اگر درست است، تابع Rnd دوباره محاسبه می شود"
Arg2Desc = Arg2Desc & "در هر تغییری در برگه."
Application.MacroOptions_
ماکرو:=FuncName، _
توضیحات:=FuncDesc, _
رده:=FuncCat، _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
پایان فرعی

روی انجیر 8 نشان دادن کادرهای محاوره ای Function Wizardو آرگومان های تابعپس از اجرای روال DescribeFunction()

برنج. 8. نمایش جعبه های محاوره ای Function Wizardو آرگومان های تابعبرای عملکرد سفارشی

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

اگر با استفاده از روش MacroOptions یک دسته تابع را مشخص نکنید، تابع کاربرگ سفارشی در دسته ظاهر می شود. تعریف شده توسط کاربرکادر محاوره ای Function Wizard. جدول (شکل 9) اعداد دسته را فهرست می کند که می توانند به عنوان مقادیر آرگومان Category متد MacroOptions استفاده شوند. لطفاً توجه داشته باشید که برخی از این دسته‌ها (10 تا 13) معمولاً در گفتگو نمایش داده نمی‌شوند Function Wizard. اگر یکی از توابع تعریف شده توسط کاربر به چنین دسته ای اختصاص داده شود، در کادر محاوره ای ظاهر می شود.

استفاده از افزونه ها برای ذخیره توابع سفارشی

به صورت اختیاری، می توانید توابع سفارشی پرکاربرد را در یک فایل افزودنی ذخیره کنید. مزیت اصلی این روش این است که توابع را می توان در فرمول ها بدون مشخص کننده نام فایل استفاده کرد. فرض کنید یک تابع ZapSpaces سفارشی دارید. در فایل Myfuncs.xlsm ذخیره می شود. برای اعمال آن در فرمولی در کتاب کار دیگری (غیر از Myfuncs.xlsm)، باید فرمول زیر را وارد کنید: =Myfuncs.xlsm!ZapSpaces(A1:C12).

اگر یک افزونه بر اساس فایل Myfuncs.xlsm ایجاد کنید و این افزونه در جلسه فعلی بارگذاری شود کار اکسل، سپس پیوند فایل را می توان با وارد کردن فرمول زیر نادیده گرفت: =ZapSpaces(A1:C12). ایجاد افزونه ها به طور جداگانه بررسی خواهد شد.

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

استفاده از توابع Windows API

VBA می‌تواند روش‌هایی را از فایل‌های دیگری که ربطی به اکسل یا VBA ندارند، مانند فایل‌های Dynamic Link Library (DLL) که توسط ویندوز و سایر برنامه‌ها استفاده می‌شوند، قرض بگیرد. در نتیجه، VBA توانایی انجام عملیاتی را دارد که بدون روش های قرضی، خارج از توانایی های زبان است.

Windows API (Application Programming Interface - Application Programming Interface) مجموعه ای از توابع در دسترس برنامه نویسان در محیط ویندوز. هنگامی که یک تابع ویندوز را از VBA فرا می‌خوانید، به API ویندوز دسترسی دارید. بسیاری از منابع ویندوزی که توسط برنامه نویسان ویندوز استفاده می شود را می توان از آنها دریافت کرد فایل های DLL، که برنامه ها و توابعی را که در حین اجرای برنامه متصل می شوند و نه در زمان کامپایل ذخیره می کنند.

قبل از اینکه بتوان از یک تابع API ویندوز استفاده کرد، باید آن را در بالای یک واحد برنامه اعلام کرد. اگر ماژول برنامه یک ماژول استاندارد VBA نباشد (یعنی ماژول برای UserForm, ورقیا این کتاب، سپس تابع API باید به عنوان Private اعلام شود.

اعلان یک تابع API دارای پیچیدگی هایی است - تابع باید تا حد امکان دقیق اعلام شود. بیانیه اعلان موارد زیر را به VBA می گوید:

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

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

یک مثال تابع API را در نظر بگیرید که نام را نمایش می دهد پوشه های ویندوز(با استفاده از استاندارد بیانیه های VBAاین کار گاهی غیرممکن است). ابتدا اجازه دهید یک تابع API را اعلام کنیم:

اعلان تابع PtrSafe GetWindowsDirectoryA Lib "kernel32" _
(ByVal lpBuffer As String، ByVal nSize As Long) به مدت طولانی

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

مثال زیر نتیجه را در یک جعبه پیام نمایش می دهد:

زیر ShowWindowsDir()
کم نور WinPath به عنوان رشته * 255
کم نور WinDir به عنوان رشته
WinPath = Space (255)
WinDir = چپ (WinPath، GetWindowsDirectoryA _
(WinPath، Len (WinPath)))
MsgBox WinDir، vbInformation، "Windows Directory"
پایان فرعی

در طی مراحل ShowWindowsDir، یک جعبه پیام نمایش داده می شود که محل پوشه ویندوز را نشان می دهد.

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

تابع WindowsDir() به عنوان رشته
"نام پوشه ویندوز
کم نور WinPath به عنوان رشته * 255
WinPath = Space (255)
WindowsDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath، Len (WinPath)))
عملکرد پایانی

پس از اعلام این تابع، می توانید آن را از رویه دیگری فراخوانی کنید: MsgBox WindowsDir(). همچنین می توانید از این تابع در فرمول کاربرگ استفاده کنید: =WindowsDir().

توجه! هنگام استفاده از توابع Windows API در VBA از خراب شدن سیستم تعجب نکنید. قبل از تست کار خود را ذخیره کنید.

تعیین وضعیت یک کلید

فرض کنید یک ماکرو VBA نوشته اید که از دکمه نوار ابزار اجرا می شود. اگر کاربر پس از کلیک روی دکمه کلید را نگه داشته باشد، می‌خواهید این ماکرو متفاوت اجرا شود . برای یادگیری در مورد ضربه زدن به کلید ، می توانید از GetKeyState API استفاده کنید. تابع GetKeyState گزارش می دهد که آیا یک کلید خاص فشار داده شده است یا خیر. این تابع یک آرگومان دارد، nVirtKey، که نشان دهنده کد کلید مورد نظر شما است.

در زیر برنامه ای است که تشخیص می دهد یک کلید در هنگام اجرای رویه رویداد Button_Click فشار داده شده است. . توجه داشته باشید که برای تعیین وضعیت یک کلید ثابت استفاده می شود (پذیرش مقدار هگز) که سپس به عنوان آرگومان برای تابع GetKeyState اعمال می شود. اگر GetKeyState مقدار کمتر از 0 را برگرداند، به این معنی است که کلید فشرده؛ در غیر این صورت کلید فشرده نشده است بررسی مشابهی را می توان برای کلیدهای Ctrl و Alt انجام داد (شکل 10).

برنج. 10. بررسی فشار دادن کلیدهای Shift، Ctrl و Alt

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

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

AT فایل متنیشامل اعلان ها و ثابت های Windows API است. می توانید این فایل را در آن باز کنید ویرایشگر متنو اعلان های مناسب را در ماژول VBA کپی کنید.

بر اساس کتاب. - م: دیالکتیک، 2013. - S. 287-323.

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

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

نحو:

نام تابع ()
" بیانیه های VBA
عملکرد پایانی

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

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

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

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

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

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

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

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

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

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

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

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

اشتراک گذاری

توییت

پسندیدن