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

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

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

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

 

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

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

 مراكز پخش

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

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

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

 

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

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

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


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

 

 

 

 

آخرين مطالب

در این بخش در نظر داریم مثالی کاملا کاربردی ارایه دهیم. این مثال میتواند با کمی تغییر کاربردهای گوناگون پیدا کند. حتی می توانید بخشهایی از آنرا در برنامه های خود استفاده کنید.

در این بخش در نظر داریم تا اطلاعاتی را از چندین فایل اکسل در یک فایل تجمیع کنیم. فرض کنید که اطلاعاتی که در ورک بوکها هستند ازیک الگو پیروی می کنند به عنوان مثال  در همه ورک بوکها اطلاعات در شیت1 و در ناحیه A1:A10 باشد. یک ورک جدید باز میکنیم و کدهای زیر را در یک ماژول می نویسیم.

در این برنامه از ماکرو IntegrateData استفاده می کنیم. این ماکرو نیز در داخل خود از ماکروهای PicFolder و getdata‌ استفاده می کند. تابع PicFolder نام فولدری را که فایلها در آن هست از کاربر میگیرد. تابع getdata‌ هم اطلاعات را از فایل جدا و در  فایل مقصد کپی میکند.

Dim AllAnswers
Dim PathName
Sub IntegrateData()
AllAnswers = ThisWorkbook.Name
 PathName = PicFolder()


ادامه مطلب

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

از اینجا بردارید

 

بسیار سودمند و خلاصه و کاربردی

دستور GoToکنترل برنامه را به خط دیگری می برد. استفاده از این دستور خیلی ساده است و   بدین شکل بکار می‌رود:

GoTo  linename

 linename  نام یک خط و عبارت  است و  در نظر داریم کنترل برنامه را به آنجا بفرستیم. برای اینکه یک خط را نامگذاری کنیم و در اصطلاح به آن Label  بدهیم در ابتدای خط نام آن را با یک عبارت متنی می آوریم و در انتهای آن علامت دو نقطه یعنی  :  قرار می دهیم.

 


ادامه مطلب

   اکسل بطور پیش فرض در ضبط ماکرو از آدرس دهی مطلق استفاده می نماید. البته در برخی موارد نیاز است که از آدرس دهی نسبی استفاده شود. در آدرس دهی مطلق آدرس سلولها همانطور که هستند در کدهای ماکرو آورده می شود. به عنوان مثال اگر ما کار ضبط ماکرو را از سلول c1 شروع کنیم و بعد به سلول C3برویم دقیقا آدرسها به همین صورت کد می‌شوند. 


ادامه مطلب

     ساختار With…End With برای اجرای دستوارت و تنظیمات متعدد مربوط به یک شیء (یک چیز)مورد استفاده قرار می گیرد. 

به عنوان مثال کدهای زیر را در نظر بگیرید: 

SubChangeFont()
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 12
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.ColorIndex = 5
End Sub
 


ادامه مطلب

 زمانیکه یک تابع (یا رویه) در یک  Workbook تعریف و ایجاد می شود این تابع تنها در همان Workbook قابل استفاده خواهد بود. تابع را در Workbook های دیگر نمی توان بکار برد و  با پیغام #Name. روبرو می‌شویم.

 چگونه می‌توان توابع نوشته شده در یک  فایل را همیشه و همه جا بکار برد؟

 


ادامه مطلب

مثال:یک تاریخ مشخص مانند تاریخ 18/1/1392 را که با ممیز"/" از هم جدا شده اند را در نظر می گیریم. ماکروی زیر سال، ماه و روز را جدا نموده و در سه کادر پیغام نمایش میدهد. محل "/" مشخص نیست و ممکن است تاریخ به شکلهای گوناگونی مانند 18/01/1392 یا 18/1/1392 یا 18/01/92نوشته شود.


ادامه مطلب

برای اینکه همزمان چند workbook ‌ یا شیت را ببینید

برای هر شیت یا workbook گامهای1 و 2 را انجام دهید


ادامه مطلب

حلقه بی نهایت: در ساختار حلقه ای ممکن است به دلیل برقراری شرط (و یا برقرار نبودن شرط)  دستورات حلقه همواره انجام شوند و بدین ترتیب دستورات حلقه بی نهایت بار تکرار خواهد شد. در اینصورت هیچگاه از آن حلقه خارج نمی شویم و برنامه معلق(Hang) خواهد شد.

رهایی از حلقه بی نهایت: امکان و خطر حلقه بی نهایت  برای حلقه های Do…Loop بیش از حلقه For‌ وجود دارد. اگر برنامه شما در هنگام اجرا یا تست Hang‌ کرد با زدن دگمه Ctrl+Alt+Break اجرای برنامه قطع خواهد شد.

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

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


ادامه مطلب

   در اینجا دو تابع برای پیدا کردن آخرین سلول غیر خالی در یک سطر یا ستون برایتان آورده ایم.

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

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


ادامه مطلب

 

اگر به گزینه Tools--->Option در داخل محیط VBE بروید پنجره محاوره‌ایOptions   را مشاهده می نمایید.

 این پنجره دارای زبانه های Editor‌ و Editor Format‌ و General وDocking ‌ است که هر زبانه نیز بخشهای مربوط به خود را دارد. کارهایی مانند نوع فونت، رنگ فونت و اندازه فونت محیط و سایر تنظیمات ظاهری را می توانید در همین گزینه ها به راحتی انجام دهید. برخی از گزینه ها خیلی مهم نیستند و برخی گزینه ها نیز در جای مناسب خودشان توضیح داده خواهند شد. در اینجا به مهم ترین و کاربردی ترین موارد اشاره می کنیم:


ادامه مطلب

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

برای اینکه بتوانید این ماکرو را اجرا کنید مراحل زیر را دنبال کنید


ادامه مطلب

در برنامه صفحه گسترده  اکسل تبدیل تاریخهای میلادی و شمسی و قمری بسیار مورد استفاده قرار می گیرد و با توجه به نیاز بسیاری از دوستان و بازدیدکنندگان وبلاگ،‌ توابعی بدین منظور تهیه شده و  در قالب یکadd ins قرار داده شده اند.همچنین تابعی نیز برای تبدیل یک عدد به حروف آماده شده است. می توانید این add-ins را از اینجا بردارید

 

(بر روی لینک زیر کلیک راست و سپس Save Target As)

 ALFA_Utility 2017

 

نوشته شده توسط علی فاتحی

ایمیل : ali_fatehi@yahoo.com

درصورت رضایت از نحوه عملکرد برنامه و به منظور حمایت از برنامه نویسی برای توسعه برنامه های کاربردی در صورت تمایل و به دلخواه حداکثر مبلغ 100/000 ریال به حساب برنامه نویس واریز فرمایید

شماره حساب سپهری بانک صادرات : 325505610006 به نام علی فاتحی

شماره کارت نزد بانک صادرات: 6037694030696441 به نام علی فاتحی

شماره کارت نزد بانک آینده :   6362141074301756 به نام علی فاتحی

 


ادامه مطلب

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

(اینکار به راحتی از طریق office button>Prepare>Encrypt document و سپس دادن پسورد امکانپذیر است.)

 میتوان از worksheet ها و  سلولهای خاص و نمودارها، اشیای گرافیکی، نمودارها، سناریو ها، پنجره ها را نیز محافظت کرد.

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

 


ادامه مطلب

1.   شروع برنامه نویسی

 

روشهای مختلفی برای شروع و ایجاد یک برنامه با VBA وجود دارد. یک روش ساده ایجاد یک command button بر روی صفحه گسترده محیط اکسل و شروع برنامه نویسی با کلیک بر روی  آن است. روش دیگر نوشتن توابع در داخل  ویرایشگر VB  یا همان     VBE  می باشد. کار را با روش اول شروع می کنیم

1.1      روش اول : ایجاد یک برنامه با استفاده از command button

 

برای قرار دادن  command button بر روی محیط اکسل مسیر زیر را طی می کنیم:

View منوی  àToolbaràControl ToolBox

(البته اگر قبلا نوار ابزار Visual Basic Editor نیز فعال شده باشد میتوانید Control ToolBox را از آن انتخاب کنید.)

بعد از طی مسیر فوق Control ToolBox ظاهر میشود. command button را انتخاب و آنرا بر روی صفحه گسترده محیط اکسل اکسل قرار میدهیم. یک دگمه در محیط اکسل ظاهر میشود.

با کلیک کردن بر روی command button  بلافاصله  Visual Basic Editor یا  همان  VBE  ظاهر می شود. بلافاصله میتوانید دستورات خود را همانند شکل تایپ کنید. (شکل بعدی)

با دقت در این شکل ملاحظه میشود که:

دستور اول در نظر دارد مقدار(Value) سلولهای A1 تا  A10را با عبارت "Visual Basic for Application" پرکند.

دستور بعدی نیز حاصلجمع سلولهای  A11  و B11 را در سلول C11 قرار میدهد.

دستور آخر نیز مقدار سلول D11 را نیز با روش دیگری برابر با حاصلجمع A11  و B11 قرار میدهد. با اندکی دقت متوجه می شویم که این دستور با دستور قبلی متفاوت است.

 

 خروجی و رابط برنامه

 

 

 

 

برای اجرای این برنامه به محیط اکسل برمی گردیم. (میتوان از F11  +   ALT نیز استفاده کرد).

با فشردن آیکن Exit Design Mode   از    Control Toolbox از حالت طراحی خارج می شویم. پس از آن با کلیک بر روی  command button برنامه اجرا می شود که خروجی آن در شکل نشان داده شده است.

 

1.2      ایجاد یک برنامه ساده  در  VBE

برای نوشتن یک برنامه در محیط برنامه نویسی اکسل مراحل زیرانجام می شود:

1- ورود به محیط برنامه نویسیی یا همان Visual Basic Editor

 برای اینکار چند روش وجود دارد

·        استفاده از کلیدهای میانبر:  F11  + ALT

·        استفاده از منوی اکسل:  Tools-> Macro-> Visual Basic Editor

·        استفاده از ToolBar یا همان نوار ابزار Visual Basic و فشردن آیکون Visual Basic Editor

به هر حال با یکی از روشهای فوق وارد محیط برنامه نویسی می شویم

2- ورود به  منوی  Insert  و انتخاب گزینه Module  

3- نوشتن فرمان (کلمه کلیدی)  Sub ‌ و سپس نام برنامه

4- فشردن دگمه  Enter   

5- بلافاصله بطور اتوماتیک فرمان End Sub  در یک خط جدید اضافه می شود.

6- کدهای برنامه را خط به خط  بین فرمانهای Sub  و End Sub می نویسیم.

 

 به عنوان مثال:

 

Sub MyProgram()

Range("A1:A10").Value = "Visual Basic For Applications"

Range("A11").Value=10

Range("B11").Value = 20

Range("C11").Value = "=A11+B11"

End Sub

 

ملاحظه می شود که در سطر C11 ارزشهای موجود درA11   و B11 جمع زده می شود.

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

 

 

7-اجرای برنامه : برای اجرای برنامه چندین روش وجود دارد

·        فشردن کلید F5

·        فشردن آیکن Rub Sub/User Form از   Toolbar

·        بازگشت به محیط   Excel و استفاده از F11  + ALT

 

نکته: در صورتی که از علامت '  در ابتدای یک خط برنامه نویسی در محیط VB استفاده شود آن خط سبز شده و عملیاتی بر روی آن خط اجرا نخواهد شد. این خط اصطلاحا Comment (توضیحات)‌نامیده می شود. Comment حالت اجرایی ندارد و از آن برای ارایه توضیحات استفاده میشود. Comment ها به خوانایی برنامه بسیار کمک میکنند.

1.3        Select  , Value  , Rangeدستورات

 

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

 

با انجام دستور  Select سلهای مورد اشاره انتخاب می شود. می توان بعد از  Rrange از این  دستور استفاده نمود.

دستور زیر را وارد کنید:

Range("F1:F10").Select

 

و دکمه  RUN  را بزنیم، ملاحظه می شود که سطرهای مورد ازF1 تا  F10 اشاره های انتخاب میشوند.

می توان به جای : از علامت ,  استفاده نمود که در این حالت سلولهای F1 و  F10می انتخاب می شوند.

 

VALUE=

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

 

مواردی که در بالا اشاره گردید خلاصه ای از سه دستور  ,RANGE VALUE,  SELECTبود که اشاره ای مختصر به آن گردید. در اینجا مقدمه ای بر برنامه نویسی ارایه گردید و در جلسات بعدی با برنامه نویسی بیشتر آشنا خواهیم شد. 

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

 


ادامه مطلب

 

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 

 


ادامه مطلب
.: Weblog Themes By Iran Skin :.

درباره وبلاگ

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