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

از ماکرونویسی نهراسید! بسیار ساده است!

صفحه اصلي | آرشيو مطالب | تماس با من                    ali_fatehi@yahoo.com

آموزش  مشاورهداستان وبلاگ من

 

كتاب ماكرونويسي و برنامه نويسي كار بردي به زبان VBA در Excel

به نگارش اينجانب منتشر شد
مولف: علي فاتحي
ناشر: انتشارات سازمان بورس

 مراكز پخش

«نمايشگاه و فروشگاه انتشارات بورس»
شماره تماس:  88648192
(تهران - ميدان ونك - خ ملاصدرا- شماره 25)
اطلاعات كتاب و سفارش خريد آنلاين از انتشارات بورس

«انتشارات صانعي»

شماره تماس:  5385  6640
(
تهران- خ انقلاب- روبروي  دانشگاه تهران- پلاك 1290)
www.saneibook.com

 

.....در زماني كوتاه و به راحتي مي توانيد در اكسل برنامه و ماكرو بنويسيد؛ حتي اگر شما برنامه نويس نباشيد ماكرونويسي و برنامه نويسي ابزاري مناسب براي كاربراني است كه مي خواهند در Excel بهتر و پوياتر عمل كنند. زبان برنامه نويسي مورد استفاده در Excel بسيار شبيه زبانهاي محاوره اي است و VBA

 (Visual Basic for Applictions )
نام دارد.
......

20 صفحه اول كتاب را از اينجا برداريد.


اين كتاب براي كاربراني نوشته شده كه با برنامه‌نويسي و ماكرونويسي آشنايي نداشته يا آشنايي كمي دارند و مي‌خواهند در نرم‌افزار اكسل كارهاي بهتر و پوياتري انجام دهند و با زباني ساده و به دور از پيچيدگي مفاهيم اوليه و كاربردي را گام به گام ارايه نموده است. مثال‌ها متنوع و كاربردي‌اند. از برنامه‌هاي ساده  و "تابع تبديل عدد به حروف" گرفته تا "پياده‌سازي مفاهيم مهندسي مالي" در كتاب آموزش داده شده‌اند..

 

 

 

 

آخرين مطالب

 

1.          مقدمه

VBA  که مخفف عبارت   Visual Basic For Applications یک زبان برنامه‌نویسی است که توسط شرکت نرم افزاری مایکروسافت طراحی شده است.  VBA در Excel و سایر نرم افزارهای آفیس گنجانده شده است.

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

VBA  با Visual Basic) VB) تفاوت دارد. اگر چه این دو شباهاتهایی هم با هم دارند ولی کاربردهای آنها متفاوت است. مهارت در VB به معنای مهارت در VBA نمی باشد اگر چه کار را برای یادگیری و پیشرفت در VBA هموار می کند. از سوی دیگر هر قدر در نرم افزارهای آفیس مهارت بیشتری داشته باشد در VBA توانایی و مهارت بیشتری خواهید داشت.

موارد استفاده از  VBA

·         خودکارسازی(automating) کارهای دستی و طولانی

·          خودکارسازی(automating) امور تکراری

·          سفارشی کردن و ایجاد رابط با کاربر(با استفاده از دگمه ها و منوها)

·          ایجاد توابع جدید که در اکسل وجود ندارد

·       ارتباط با سایر برنامه های office   مانند Word‌ و Access و Powerpoint

 2.            ایجاد ماکرو

اگر در اکسل(یا هر یک از نرم افزارهای آفیس) بخواهیم کارهای ثابتی را پشت سر هم انجام دهیم از ماکرو استفاده می کنیم. ماکرو این کارهای ثابت و پشت سر هم را ضبط می کند و بعدا میتوان از آن استفاده کرد.

Macro recorderمانند یک ضبط عمل می کند. در واقع از زمانیکه دگمه record ‌ زده میشود شروع به ضبط  ریز به ریز کلیه عملیات نموده و تا زمانیکه دگمه Stopزده شود اینکار را ادامه میدهد.

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

فیلم آموزشی زیر مراحل کار را خیلی ساده و سریع نمایش میدهد.

 

 http://alifatehi.persiangig.com/video/ExcelMacros.wvx 

 


تشریفات و مقدمات- کمی صبور باشید

اضافه کردن تب  Developer به اکسل

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

 

 

در حالت عادی این تب فعال نیست و شما باید به اکسل بگویید که چگونه آنرا نمایش دهد. اضافه کردن آن بسیار ساده است و تنها کافیست یکبار اینکار انجام شود. برای اینکار مراحل و مسیرهای  را دنبال کنید:

در اکسل 2007

1- مسیر  Office Button ---> Excel OPtion را دنبال کنید

 شاید برای شما این سوال پیش بیاید که تب Office کجاست؟

مایکرو سافت در تمام برنامه های خود  علامت یا شکلک (آیکونی) در گوشه بالای سمت چپ ایجاد کرده  که Office Button  نام دارد. پس از انتخاب آن منوهای آفیس نمایش داده می شود. (یا اینکه  Alt+F را بزنید)

2- تب  excel option ----> Popular را انتخاب کنید

3- قسمت زیر را تیک بزنید

Show Developer tab in Ribbon

4-کلیک OK

 

در اکسل 2003

در excel 2003 از دو راه می توان پنجره ماکرو  را باز نمود:

1- Tools/macro

 2- فعال کردن   Visual Basic Toolbar    و Recor New Macro

به هر ترتیب آیکونهای مربوط به ماکرو از جمله  Record Macro‌ در برنامه اکسل ایجاد می شود که برای ایجاد ماکرو از آن استفاده می کنیم

 مثال1:برای شروع میخواهیم یک ماکرو ساده در اکسل ایجاد کنیم. فرض کنید صفحه ای در اکسل شامل اعداد، روبروی شماست. رئیس شما بالای سر شما ایستاده و مرتبا از شما میخواهد که برخی از سلولها را علامتگذاری کنید. مثلا فونت آنرا Arial  سایز 16 و  Boldو رنگ زمنیه سلول را قرمز کنید. اگر بتوانیم یکبار اینکار را انجام دهیم و مرتبا از آن استفاده کنیم سرعت و دقت کارمان افزایش می یابد. ایجاد و بکارگیری یک ماکرو یک راه حل بسیار سریع وساده برای اینکار است.

Record Macro‌را از تب Developr انتخاب می کنیم.

به محض فشردن آیکون، Recor Macroپنجره ای باز می شود 

 

 این پنجره حاوی اجزای یک ماکرو است. اجزای ماکرو را که شامل موارد زیر است تعیین می کنیم:

1.نام ماکرو : این نامChangeColor را برای این ماکرو انتخاب می کنیم.

2.کلید میانبر(Shortcut key) (اجباری در تعیین کلید میانبر نیست.) که بصورت ترکیبی از کلید Ctrl  و یک حرف می باشد: در اینجاCtrl + T را انتخاب می کنیم.

3.توضیحات ماکرو(Description)

4.محل ذخیره ماکرو(بصورت پیش فرض در یک   درفایل جاری (Thisworkbook) ‌می باشد)

  

حال عملیات مورد نظر را انجام می دهیم. یعنی سلول مورد نظر را Bold و رنگ زمینه آن را قرمز میکنیم.  با زدن دگمه Stop  عملیات ضبط ماکرو خاتمه می یابد.

3.        اجرا ی ماکرو(Playing Macro)

برای اجرای ماکرو روشهای متعددی وجود دارد

فشردن کلیدهای ALT+F8

فشردن آیکون Run Macro

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

 

بطور خلاصه: از پنجره ماکرو گزینه record  را انتخاب می کتیم عملیات مورد نظر را انجام میدهیم ودر نهایت با زدن دکمه  stop آن را می بندیم. با اجرای ماکرو(با دگمه PLAY  یا استفاده از کلید میانبر ) کلیه کارهای مورد نظرمان انجام می شوند.

 مثال 2- میخواهیم ماکرویی ایجاد کنیم که محتویات  سلولی که در آن واقع هستیم را به گوشه سمت چپ و بالای اکسل منتقل کند(A1).

از تب ِDeveloper  دگمه  Macro Record را می‌زنیم. کلید میانبری هم تعیین می کنیم. حال شروع کرده و محتوای سلول را Cut کرده و در سلول A1 آن را  Paste می‌کنیم و در نهایت دکمه  Recording Stop را می زنیم.

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

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

پشت پرده ماکرو های اکسل-پیش به سوی برنامه‌نویسی– شیطنت و ناخنک به ماکرو

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

مسیر  Developer->Code->Macros را انتخاب کنید یا  اینکه بطور مستقیم هم می توانید ALT+F8  را بزنید.

بلافاصله پنجره محاوره ای Macro ‌ باز می شود.

ماکروی ChangeColor را از این پنجره انتخاب می کنیم. و  سپس دگمه  Edi t  را می‌زنیم.

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

کدهای تولید شده توسط VBA نمایش داده می‌شود.

     برای دیدن کدهای ماکرو ایجاد شده در پنجره Macro  دکمه  EDIT را می زنیم.

اگر کدهای نوشته شده در این برنامه را ببینید، ‌به قدرت جادویی Macro Recorder پی می برید. حتما از خودتان خواهید پرسید: "یعنی من این کدها رو  ایجاد کردم؟ من که چیزی ازVBA  و برنامه‌نویسی نمی‌دونم !!"

ما بدون اینکه از برنامه‌نویسی چیزی بدانیم کدهایی جادویی را می‌بینیم که توسط Macro Recorder تولید شده‌اند.

 

Sub ChangeColor()

'

' ChangeColor Macro

این اولین ماکرو من است'

'

' Keyboard Shortcut: Ctrl+t

'

    With Selection.Font

        .Name = "Arial"

        .Size = 16

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 192

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    Selection.Font.Bold = True

End Sub

 

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

هر چند که هنور ما اطلاعاتی در رابطه با کدهای VBA و برنامه نویسی نداریم ولی با کمی دقت ملاحظه می شود به نظر میرسد بخشی از کدها هم قابل درک و شناسایی باشند. مثلا کد زیر را در نظر بگیرید:

.Size = 16

 به نظر می‌رسد این همان سایز فونت است.  بیایید ناخنکی به این کدها بزنیم. البته یک مقدار احتیاط کنید.  عدد 16 را به 40 تغییر بدهید و دوباره ماکرو را اجرا کنید.(اگر عجله دارید می توانید با فشردن کلید Alt+F11 اینکار را بکنید).

می‌بینید که همان کار قبلی انجام می‌شود ولی سایز فونت سلول به 40 تغییر می‌کند.

می‌توانید بخشهای دیگر را هم تغییر دهید. و نتبجه را امتحان کنید.

 

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

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

مثال 3: در این صفحه ملاحظه می شود که یک برنامه VBAچگونه نوشته می شود و می توان در آن تغییراتی ایجاد نمود.

فرض کنید که صفحه اکسلی با 50000 (پنجاه هزار) سطر اطلاعات داریم. میخواهیم سطرهای 10000(ده هزار)  تا 20000(بیست هزار)را حذف کنیم.

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

کدهای زیر دیده می شود.

SubMacro1( )

'

' Macro1Macro

' Macro recorded 2010/01/13 byali.fatehi

'

    Rows("3:5").Select

    Selection.Delete Shift:=xlUp

End Sub

به نظر میرسد کد     Rows("3:5").Select سطرهای 3 تا 5 را انتخاب می کند.اگر این کد را به صورت زیر تغییر دهیم مساله حل می شود.

    Rows("10000:20000").Select

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

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

مثال 4: انتقال اطلاعات از اینترنت به اکسل:

از منوی دیتا آدرس زیر را انتخاب می کنیم:

Data/import external data/new web query

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

برای این عملیات ماکرویی ایجاد می کنیم. و همه روزه با اجرای این ماکرو اطلاعات مورد نظر را از اینترنت دریافت می کنیم.

 

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

 

 

 

تمرین:

ستونهای A ‌ تاE ‌ از یک شیت اکسل حاوی اطلاعات است. ماکرویی ایجاد کنید که برای هر ستون یک شیت ایجاد کرده و اطلاعات آن ستون را به شیت جدید کیی کند.

 

 

 

.: Weblog Themes By Iran Skin :.

درباره وبلاگ

آرشيو مطالب
امکانات وب