چگونه مي‌توان در اكسل تابعي جديد تعريف كرد؟

محيط ويژوال بيسك

گام دوم ايجاد يك ماژول

شما بايد دستورات تابع خود را در يك Module (ماژول) بنويسيد ،  از منوي Insert گزينه Module را بزنيد . و اگر به project explorer نگاه كنيد متوجه خواهيد شد كه يك ماژول جديد ايجاد شده است.

گام سوم ايجاد يك تابع در ماژول

يك تابع در ويژوال بيسيك قواعد استانداردي دارد كه شما بايد از اين قواعد اطاعت كنيد .

اولين قانون آن اين است كه يك تابع با دستورات استانداردي شروع و به پايان مي‌رسد.

قانون دوم اين است كه هر تابع يك نوع دارد و وروديهاي يك تابع در داخل پرانتز مشخص مي‌شوند.

قانون سوم ، نوع داده وروديها (و خود تابع) بايد مشخص شود.

اين دستورات عبارتند از :

Private Function Test(Num As Integer) as Double

End Function

نام تابع ما test است و عبارت داخل پرانتز مي‌گويد كه اين تابع يك ورودي دارد كه نام آن ورودي Num است و integer  بيانگر آن است كه اين ورودي عددي صحيح است . (-32,768  تا 32,767 )

خروجي تابع از نوع double است و البته گذاشتن آن در همه موارد الزامي نيست ، گرچه بهتر است كه مشخص شود. (براي اطلاع بيشتر به كتابهاي برنامه نويسي مراجعه كنيد.)

عبارت Private Function نشانگر شروع تابع و End Function براي پايان تابع است.

گام چهارم – نوشتن تابع

فرض كنيد مي‌خواهيم تابعي بنويسيم كه يك عدد را بگيرد و آنرا در 10 ضرب كند!

اول بايد تصميم بگيريم كه اسم اين تابع را چه بگذاريم ، در حقيقت اين اسم همان كلمه‌اي است كه در اكسل براي استفاده از اين تابع استفاده خواهيم كرد.

خوب اسم آنرا Test مي‌گذاريم و مي‌دانيم كه اين تابع بايد يك ورودي داشته باشد و خوب چون به تازگي با نوع عدد Integer  آشنا شديم (عدد صحيح) نوع اين ورودي را هم Integer مي‌گذاريم.

بايد نامي براي اين ورودي در نظر بگيريم ، اين نام نبايد يك نام آشنا ! براي VB باشد و بهتر است نامي با مسما در نظر بگيريم ، اينجا اسم اين ورودي را Num مي‌گذاريم.

پس در ماژول خود خواهيم نوشت :

Private Function Test(Num As Integer)

    Test = Num * 10

End Function

حال از ويژوال بيسيك خارج مي‌شويم ( Alt + Q) و به اكسل بر مي‌گرديم .

گام پنجم - استفاده از تابع

مثل توابع استاندارد اكسل مي‌توان از اين تابع هم استفاده كرد مثلا بنويسيد :

= test(8)

= test(A1)

مثال 1) تابع بدست آوردن شماره رنگ يك سلول و رنگ قلم آن سلول

مي‌خواهيم تابعي بنويسيم كه شماره رنگ يك سلول (fill color) يا شماره رنگ قلم (font color) را مشخص كنيم.

(مي‌دانيم كه در اكسل از 56 رنگ مي‌توان استفاده كرد كه هر رنگ يك كد دارد مثلا كد رنگ قرمز 3 و آبي 5 است.)

·          نام تابع : CellColor

·          ورودي : تابع دو ورودي دارد ، ورودي اول آدرس سلول است و ورودي دوم مشخص مي‌كند كه ما مي‌خواهيم رنگ زمينه سلول را داشته باشيم يا رنگ قلم آنرا .
اگر ورودي دوم عبارت fill بود رنگ زمينه مد نظر است و اگر font بود رنگ قلم.

·          نام ورودي اول MyRange و از نوع Range  است

·          نام ورودي دوم Mode و از نوع String است

·          اگر ورودي دوم داده نشده بود و يا مقاديري غير fill و font بود ، خروجي تابع يك خطا به شكل #Mistake  باشد .

براي نوشتن اين تابع از دستور شرطي IF به صورت زير استفاده مي‌كنيم :

Public Function Colorindex(MyRange As Range, Mode As String)

    Application.Volatile True

    If Mode = "font" Then

     Colorindex = MyRange.Font.Colorindex

    ElseIf Mode = "fill" Then

     Colorindex = MyRange.Interior.Colorindex

    Else

     Colorindex = "#Mistake"

    End If

End Function

عبارت As Range بيان مي‌كند كه ورودي اول يك خانه است.

عبارت String As بيان مي‌كند كه ورودي دوم يك رشته (متن – غير عدد) است .

حال خانه A1 را به رنگ زرد و متن آنرا قرمز مي‌كنيم و تابع را روي آن آزمايش مي‌كنيم.

فرمولهاي بكار رفته در خانه B1 و B2 را به ترتيب در D1 و D2 مشاهده مي‌كنيد.

مثال 2) سلولهايي را كه رنگ آنها . . . را با هم جمع بزنيد.

بهتر است از مثال قبل يك استفاده كاربردي كنيم. مثلا در يك محدوده مي‌خواهيم سلولهاي قرمز رنگ را با هم جمع بزنيم. (اين سلولها مي‌توانند با Conditional Formationg قرمز شده باشند.)

Public Function SumByColor(InRange As Range, WhatColorIndex As Integer) As Double

     Application.Volatile True

For Each C In InRange.Cells

    If C.Interior.Colorindex = WhatColorIndex Then

        SumByColor = SumByColor + C.Value

    End If

Next C

End Function

·          نام تابع : SumByColor و نوع آن را Double در نظر گرفتيم.

·          ورودي‌ها: محدوده كه نام آنرا InRange و شماره رنگ  كه نام آنرا  WhatColorIndex گذاشتيم و از نوع عدد صحيح است.

·          عبارت For Each C InRange دارد مي‌گويد كه C يك سلول از سلولهاي محدود InRange است .

مثال 4- تابعي ايجاد كنيد كه اختلاف بزرگترين و كوچكترين عدد يك محدوده را حساب كند.

البته اينكار با خود اكسل بسيار ساده است و كافي است كه فرمول =max(   ) – Min(   )  را بكار ببريم ، اما هدف اين مثال اينست كه نشان دهد چطور مي‌توان در محيط ويژوال بيسيك از توابع استاندارد اكسل استفاده كرد.

Public Function max_min(InRange As Range) As Double

    Application.Volatile True

MaxNum = Application.WorksheetFunction.Max(InRange)

MinNum = Application.WorksheetFunction.Min(InRange)

max_min = MaxNum - MinNum

End Function

همانطور كه مي‌بينيد با دستور application.worksheetfunction.xxx  كه به جاي xxx نام تابع استاندارد اكسل را مي‌نويسيم.

مثال 4- مي‌خواهيم تابعي بنويسيم كه بتواند مقدار هزار، ميليون و ميليارد را در يك عدد تشخيص دهد و سپس كلمه "تومان" يا "ريال" را هم  به آن عدد اضافه كند . در صورتي كه عدد معتبر نبود پيغام خطاي مناسبي را اعلام كند.

مثلا اگر عدد 15000  وارد شد بنويسيد : 15 هزار ريال (يا 15 هزار تومان)

با اين مثال قصد داريم وروديهاي Optional را مرور كنيم. مثلا اگر تابع Vlookup را در نظر بگيريم ، آخرين ورودي آن انتخابي (optional) است ، بدين معني كه اگر كاربر مقداري را وارد نكند به طور پيش فرض مقدار True را در نظر مي‌گيرد و در ضمن اگر كاربر خواست مي‌تواند ورودي را به تابع بدهد.

حالت ساده اين كار در VB به اين صورت است كه مي‌نويسيم :

Public Function Test(Num as Integer, optional Num as Integer = True)

تفسير  كد بالا به اين صورت است كه تابعي داريم به نام Test با دو ورودي.

ورودي اول آن مقداري است به نام Num و از نوع عدد صحيح است .

ورودي دوم آن مقداري است اختياري ، يعني كاربر مي‌تواند ورودي دوم را ندهد و با تعريف بالا مقدار پيش فرض آن True در نظر گرفته شده كه اگر كاربر مقداري را صريحاً به تابع اعلام نكند ، آنوقت مقدار دوم True در نظر گرفته خواهد شد و كار تابع با اين مقدار شروع مي‌شود.

اگر ورودي دوم داده نشود يا 1 يا True اعلام شود ، كلمه ريال در نظر گرفته خواهد شد.

اگر ورودي دوم False يا 0  داده شود ، كلمه تومان در نظر گرفته خواهد شد.

حال به نوشتن تابع مذكور مي‌پردازيم :

Public Function PersianCurrency(MyNumber As String, Optional Mode As Boolean = True)

    Application.Volatile True

If Val(MyNumber) >= 0 Then

    Temp$ = MyNumber

    Cur$ = ""

End If

If Val(MyNumber) >= 1000 Then

    Temp$ = Mid(Trim(MyNumber), 1, Len(MyNumber) - 3)

    Cur$ = "هزار"

End If

If Val(MyNumber) >= 1000000 Then

    Temp$ = Mid(Trim(MyNumber), 1, Len(MyNumber) - 6)

        Cur$ = "ميليون"

End If

If Val(MyNumber) >= 1000000000 Then

    Temp$ = Mid(Trim(MyNumber), 1, Len(MyNumber) - 9)

        Cur$ = "ميليارد"

End If

If Mode = True Then C$ = "ريال" Else C$ = "تومان"

PersianCurrency = Temp$ & Cur$ & " " & C$

If Val(MyNumber) = 0 Then

    PersianCurrency = "مقدار يافت نشد"

End If

End Function

تست و خروجي اين تابع را در مثال زير مشاهد مي‌كنيد .

تذكر : مقدار True همان 1 است و مقدار False همان 0 است . (يعني از هر كدام از اين مقادير مي‌توان استفاده كرد.)

 اين تابع جنبه تمريني دارد و همه مقادير اعداد را پوشش نميدهد ،  فقط ارقامي كه با صفر همراه هستند را مي شناسد.

استفاده از توابع در تمامي فايلهاي اكسل

اما اين سوال پيش مي‌آيد كه چطور مي‌شود اين توابع را در همه جا – يعني همه فايلهاي اكسل – استفاده كنيم . وقتي شما يك تابع جديد را تعريف مي‌كنيد اين تابع فقط در همان فايل (البته همه sheet هاي آن) قابل استفاده است و اگر بخواهيد اين تابع را در فايل ديگري فراخواني كنيد، با پيغام #Name مواجه خواهيد شد. يك راه اين است كه اين فايل را بعنوان يك فايل Template ذخيره و بعد استفاده كنيم. اما راه اصولي اضافه كردن تابع در اكسل استفاده از Add-In ها است.   Add-In چيست؟ Add-In  توابعي هستند كه توسط كاربران نوشته مي‌شود و سپس روي اكسل اضافه مي‌گردند. هر بار كه اكسل اجرا شود، تمامي اين توابع نيز فراخواني خواهند شد و در واقع Add-In ها امكان بسط نرم افزار را بدون محدوديت به كاربران مي‌دهد.   ايجاد يك Add-In قدم اول ايجاد يك Add-In است كه كار بسيار ساده‌اي است. فايلي كه توابع در آن نوشته شده است را باز كنيد. تمام Sheetهاي اضافه آن را حذف كنيد. (فقط يك sheet خالي داشته باشيد.) از منوي File گزينه Save as را انتخاب كنيد. يك نام براي فايل انتخاب كنيد. در جلوي گزينه Save As type گزينه  MicroSoft Office Excel Add-In را انتخاب كنيد. دقت داشته باشيد كه فايل را در كجا Save مي‌كنيد.       نصب يك Add-In بعد از ساختن يك Add-In شما مي‌توانيد آنرا در هر كامپيوتري با هر نسخه‌اي از Office نصب كنيد.( مثلا Office2000) با نصب اين فايل تمامي‌توابعي كه نوشته‌ايد به اكسل اضافه خواهد شد و تا وقتي كه شما office را Uninstall يا Remove نكنيد، اين توابع همراه اكسل خواهند بود. براي نصب يك Add-In مراحل زير را دنبال كنيد. از منوي Tools گزينه Add-Ins… را انتخاب كنيد. با زدن گزينه Brower فايل Add-In را انتخاب كنيد. تا وقتي كه CheckBox در حالت فعال قرار دارد ، اين فايل با اكسل فراخواني مي‌شود و مي‌توان با تمامي توابع اين فايل كار كرد و بازدن كليد Alt+F11 مي‌توانيد وارد محيط ويژوال بيسيك شويد و فايل خود را با پسوند xla ببينيد.           از اينكه نظرات و تجربيات و ... را از طرف شما دريافت كنم خوشحال خواهم شد.   فرشيد ميداني