برنامه نویسی در اکسل — آموزش گام به گام برنامه نویسی در اکسل

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

آشنایی با VBA، زبان برنامه نویسی در اکسل

زبان Visual Basic for Applications یا به اختصار VBA، زبانی مبتنی بر رویدادهاست که مایکروسافت برای توسعه‌ی اپلیکیشن‌های آفیس از آن استفاده کرده است. در واقع VBA مبتنی بر Visual Basic 6.0 BASIC طراحی شده که زبانی ساده برای مبتدیان است. در این زبان از دستوراتی که درک کردن آن ساده است، استفاده می‌شود و به همین جهت یادگیری آن در قیاس با بسیاری از زبان‌های برنامه‌نویسی دیگر، نسبتاً ساده است. در اکسل رابط کاربری بخش برنامه نویسی به زبان VBA حتی از درگ و دراپ یا گرفتن و رها کردن پشتیبانی می‌کند و می‌توانید کنترل‌ها را با این روش جابجا کنید. دقت کنید که در اکسل قابلیت ضبط کردن و اجرا کردن ماکرو وجود دارد. منظور از ماکرو، فرآیندی است که مراحل آن ضبط شده است و برای انجام امور تکراری به کار می‌رود. اما در کنار قابلیت کار با Macro در اکسل، می‌توانید برنامه‌ای بنویسید که امور تکراری را انجام دهد. در این صورت سرعت و دقت کار بیشتر می‌شود. علاوه بر این نوشتن توابع دلخواه و سفارشی و حتی تغییر دادن رابط کاربری اپلیکیشن میزبان توسط VBA امکان‌پذیر است. برای آشنایی بیشتر با برنامه نویسی در اکسل از سطح مقدماتی تا پیشرفته، بهتر است از دوره‌های آموزشی ویدیویی استفاده کنید. فرادرس مرجع خوبی برای آموزش برنامه نویسی در اکسل است:

• آموزش برنامه نویسی VBA وی بی ای در اکسل – مقدماتی — کلیک کنید

با یک مثال ساده، توانمندی VBA در گسترش قابلیت‌های اکسل روشن می‌شود: فرض کنید که می‌خواهید تمام سلول‌هایی که پس‌زمینه رنگی دارند را در اکسل انتخاب کنید. برای این منظور گزینه و تابعی در اکسل پیش‌بینی نشده است لذا اگر جدول بزرگی داشته باشید، می‌بایست با دقت روی تک‌تک سلول‌ها کلیک کنید. این کار هم وقت‌گیر است و هم احتمال بروز خطای انسانی و انتخاب نشدن یک سلول رنگی، وجود دارد. با کمک ویژوآل بیسیک و امکانات برنامه نویسی در اکسل، می‌توانید یک تابع ساده بسازید که به سرعت تمام سلول‌های رنگی را از میان هزاران سلول جدول، انتخاب می‌کند و خطایی نیز وجود ندارد!

مقدمات برنامه نویسی در اکسل

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

متغیر

در VBA برای کار با سلول‌ها، از متغیر استفاده می‌شود. به عنوان مثال زمانی که می‌خواهید عدد موجود در سلولی را در ۲ ضرب کنید، می‌بایست محتویات سلول را در متغیری ذخیره کنید و سپس محاسبات ریاضی را روی آن متغیر انجام دهید. در تعریف متغیر دقت کنید که نام متغیر نباید کلمات خاص و رزرو شده در زبان برنامه‌نویسی موردنظر باشد. به عنوان مثال if کلمه‌ای است که در همه‌ی زبان‌های برنامه‌نویسی امروزی، برای چک کردن برقراری شرط استفاده می‌شود و در نتیجه نمی‌توانید متغیری به اسم if‌ داشته باشید. نکته‌ی دیگر این است که در VBA نام متغیر نمی‌تواند اسپیس یا فاصله داشته باشد. می‌توانید به جای فاصله بین کلمات نام متغیر، از آندرلاین یا ـ استفاده و یا حروف ابتدای کلمات را بزرگ تایپ کنید. به عنوان مثال به جای first name از FirstName یا first_name استفاده کنید تا اروری ظاهر نشود.

اعمال ریاضی

در زبان VBA اعمال ریاضی پایه یعنی جمع و تفریق و ضریب و تقسیم، با علامت + و – و * و / صورت می‌گیرد.

گزاره‌های شرطی

در برنامه نویسی در اکسل، ممکن است مقایسه کردن مقدار متغیرها و بررسی شرایط چندین متغیر، ضروری باشد. در این صورت می‌توانید از گزاره‌های منطقی شامل if و OR و NOT و AND و همین‌طور TRUE و FALSE استفاده کنید، درست مثل اغلب زبان‌های برنامه‌نویسی. اگر تاکنون برنامه‌نویسی نکرده‌اید یا اطلاعات بسیار کمی دارید، بهتر است مبانی برنامه‌نویسی را یاد بگیرید و با الگوریتم یا فلوچارت برنامه و گزاره‌های شرطی و حلقه‌ها و متغیرها آشنا شوید. می‌توانید از دوره‌های آموزشی فرادرس در عرصه‌ی برنامه‌نویسی استفاده کنید:

• آموزش اصول و مبانی برنامه نویسی – (رایگان) — دریافت کنید

آموزش فعال کردن تب برنامه نویسی یا Developer در اکسل

در نوار ابزار بالای پنجره اکسل به صورت پیش‌فرض تب برنامه نویسی در اکسل فعال نیست چرا که این تب مورد استفاده عموم کاربران Excel‌ نیست. برای فعالسازی تب Developer می‌بایست ابتدا نرم‌افزار اکسل را اجرا کنید و سپس در صفحه‌ی ایجاد فایل یا باز کردن فایل، روی گزینه‌ی Options در ستون کناری کلیک کنید. اگر قبلاً فایلی را باز کرده‌اید هم ابتدا روی منوی File کلیک کنید تا صفحه‌ی باز کردن فایل ظاهر شود و روی Options کلیک کنید. در پنجره‌ی باز شده که Excel Options نام دارد، روی تب Customize Ribbon در ستون کناری کلیک کنید. سپس در سمت راست تیک Developer را در لیست سمت راست بزنید و روی OK کلیک کنید.

اکنون تب برنامه نویسی در بالای صفحه اضافه شده است و می‌توانید برنامه نویسی را آغاز کنید.

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

برای نوشتن برنامه به زبان VBA با یک مثال ساده شروع می‌کنیم که نوشتن برنامه‌ای برای نمایش کادر ورود نام کاربر است و زمانی که کاربر نامش را تایپ کردن، یک عبارت خوشامدگویی نظیر سلام کاربر ظاهر می‌شود. همه‌ی برنامه‌های VBA با عبارت Sub شروع می‌شوند و در پایان برنامه نیز End Sub نوشته می‌شود. منظور از ساب، ساب روتین است. بین این دو عبارت، کدها نوشته می‌شود. در خط نخست می‌توانید نام برنامه را پس از Sub تایپ کنید.

Sub name()
.
.
.
.
End Sub

اما روال کار: ابتدا یک فایل جدید اکسل بسازید و آن را با فرمت xlsm که در واقع فایلی با ماکرو فعال است، ذخیره کنید. سپس روی تب Developer در بالای صفحه کلیک کنید. در این تب روی گزینه‌ی Insert کلیک کنید تا لیستی از کنترل‌های فرم ظاهر شود. اولین مورد لیست، دکمه یا Button‌ است. روی آن کلیک کنید.

روی نقطه‌ای از صفحه اکسل کلیک کرده و بدون رها کردن کلید چپ موس، آن را حرکت دهید و سپس رها کنید تا دکمه با ابعاد دلخواه رسم شود. به این ترتیب کادری مشابه تصویر زیر ظاهر می‌شود که برای انتخاب نام ماکرو و محل کارکرد آن کاربرد دارد. نام دلخواهی مرتبط با کارکرد دکمه در کادر Macro name تایپ کنید و سپس روی دکمه‌ی New کلیک کنید.

با کلیک روی New، پنجره‌ی برنامه نویسی ویژوآل بیسیک ظاهر می‌شود. همان طور که در تصویر زیر مشاهده می‌کنید، نام دکمه پس از Sub در خط اول دیده می‌شود و در پایان برنامه نیز End Sub نوشته شده است.

حال نوبت به نوشتن خطوط برنامه بین خط شروع و پایان می‌رسد. سه خط کد زیر را تایپ کنید:

Dim name As String
name = InputBox("Enter your name")
MsgBox "Hello " + name

اما توضیح هر یک از خطوط برنامه: در خط اول Dim name as String برای تعریف کردن یک متغیر به اسم name به کار می‌رود. این متغیر از نوع استرینگ یا به زبان فارسی، رشته و متن است و قابلیت ذخیره کردن حروف و اعداد در آن وجود دارد. لذا as String برای تعریف کردن نوع متغیر در خط اول تایپ شده است. خط بعدی عبارتی را در متغیر name ذخیره می‌کند که از کادر ورود متن با عنوان Enter your name دریافت می‌شود. تابع InputBox یکی از توابع اکسل است که برای نمایش کادر ورود متن به کار می‌رود. خط سوم با دستور MsgBox شروع می‌شود که تابعی برای نمایش پیغام است. با استفاده از این تابع، کلمه‌ی Hello و پس از آن محتویات متغیر name چاپ می‌شود. با این توضیحات روشن است که ۳ خط برنامه‌ی فوق، نام کاربر را از کادر ورود متن دریافت کرده و پیام سلام کاربر را نمایش می‌دهد. به این ترتیب در پنجره برنامه نویسی اکسل، کدها به صورت زیر خواهد بود:

اکنون می‌توانید پنجره‌ی ویژوآل بیسیک را ببندید. سپس روی دکمه‌‌ای که در صفحه قرار داده‌اید راست‌کلیک کنید و گزینه‌ی Edit text را برای ویرایش کردن عنوان دکمه انتخاب کنید. نام دلخواهی نظیر Say Hello برای دکمه تایپ کنید.

اما روش اجرا کردن برنامه و کار با دکمه‌ای موردبحث: زمانی که روی دکمه‌ی Say Hello کلیک می‌کنیم، برنامه اجرا می‌شود و اولین اتفاق این است که کادر وارد کردن متن با توضیح Enter your name ظاهر می‌شود:

پس از اینکه کاربر نامش را تایپ کردن و Enter را فشار داد یا روی دکمه‌ی OK کلیک کرد، خطوط بعدی برنامه اجرا می‌شود و یک پیغام ساده ظاهر می‌شود، مثل تصویر زیر:

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

• آموزش مبانی برنامه نویسی – الگوریتم و فلوچارت با رویکرد حل مسأله — کلیک کنید

برنامه نویسی در اکسل – قفل کردن سلول‌های اکسل

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

برنامه‌ی موردبحث کمی پیچیده و طولانی است لذا توضیحی مختصر در مورد آن می‌دهیم.

Sub LockCell ()
Dim colorIndex As Integer
Dim Range As Range
colorIndex = FF0000
For Each Range In ActiveSheet.UsedRange.Cells
Dim color As Long
color = Range.Interior.colorIndex
If (color = colorIndex) Then
Range.Locked = True
Else
Range.Locked = False
End If
ActiveSheet.Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlNoRestrictions End Sub

اما توضیحاتی در مورد خطوط برنامه‌ی فوق:

• در خط اول نام تابع LockCell تعریف شده است.
• در خط دوم متغیری به اسم colorIndex از نوع Integer یا عدد صحیح تعریف شده که در حقیقت کد و شماره رنگ در آن ذخیره می‌شود.
• در خط سوم متغیری به اسم Range از نوع Range یا محدوده سلول‌های جدول تعریف شده است.
• در خط چهارم رنگ موردنظر برای قفل کردن سلول‌ها تایپ شده که برای رنگ قرمز خالص، از کد هگز FF0000 استفاده می‌شود.
• در خط بعدی یک حلقه For آغاز شده که سلول‌های محدوده جدول در صفحه فعال را یکی یکی بررسی می‌کنید.
• خط بعدی متغیری به اسم color از نوع Long را تعریف می‌کند.
• در خط بعدی مقدار رنگ سلول در متغیر color ذخیره می‌شود.
• در خط بعد یک گزاره شرطی با if تعریف شده که یکسان بودن رنگ سلول با رنگ موردنظر بررسی می‌شود و اگر شرط صحیح بود، طبق دستور خط بعدی، سلول قفل می‌شود و اگر این طور نبود، سلول قفل نمی‌شود.
• در پایان امکان مرتب‌سازی و فیلتر کردن و امکان استفاده از جدول پاشته‌ای فعال شده است.

برای اجرا کردن این برنامه می‌توانید یک دکمه بسازید تا با کلیک روی آن، سلول‌های قرمز قفل شوند. روش دیگر این است که هر بار پنجره‌ی ویژوآل بیسیک را باز کنید و با کلیک روی دکمه‌ی Run در بالای صفحه یا با زدن کلید F5، برنامه را اجرا کنید که طبعاً وقت‌گیر است و بهتر است از روش اول که فراخوانی تابع قفل کردن با کلیک روی دکمه است، استفاده نمایید. پس از پیست کردن این خطوط کد در پنجره‌ی ویژوآل بیسیک، فایل جدید اکسل را با پسوند xlsm ذخیره کنید.

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

• آموزش برنامه نویسی VBA در اکسل Excel – تکمیلی — کلیک کنید