Microsoft excel macros excel solver ieng 490
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

Microsoft Excel Macros & Excel Solver ( IENG 490 ) PowerPoint PPT Presentation


  • 147 Views
  • Uploaded on
  • Presentation posted in: General

Microsoft Excel Macros & Excel Solver ( IENG 490 ). Spring :(201 3 -201 4 ) Research Assistants: Arman Nedjati & Mahmoud Golabi. Excel Solver. Example1. Example2. Example2. Contents of Macros. Introduction to Macro Introduction to Visual Basic for Application Recording a Macro

Download Presentation

Microsoft Excel Macros & Excel Solver ( IENG 490 )

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Microsoft excel macros excel solver ieng 490

Microsoft Excel Macros&Excel Solver(IENG490)

Spring:(2013-2014)Research Assistants:ArmanNedjati & Mahmoud Golabi


Excel solver

Excel Solver


Example1

Example1


Example2

Example2


Example21

Example2


Contents of macros

Contents of Macros

  • Introduction to Macro

  • Introduction to Visual Basic for Application

  • Recording a Macro

  • Looking at the code of Recorded Macro


Introduction to visual basic for application

Introduction to Visual Basic for Application

  • Microsoft Excel in itself a tool which helps users to work with worksheets & workbooks with ease.

  • This tool is one of the ultimate timesaver, but there are numerous events when you have to do repetitive tasks which are of a very little value but consume your precious time; yet engage you in a less productive activity, which Excel may do automatically.

  • Microsoft Excel macro is the feature which eliminates or at-least minimize these events which take your precious time in repetitive tasks, and you will be able to capitalize this time in more productive & strategic activities.


Introduction to visual basic for application cont

Introduction to Visual Basic for Application - cont…

  • Apart from automating repetitive tasks Microsoft Excel Macros may also be used to create user interfaces

  • User interfaces are screens where user can give inputs with ease also it enables input validations


Introduction to visual basic for application cont1

Introduction to Visual Basic for Application - cont…

  • What is Visual Basic?Visual Basic is a language which is inherited from a very popular language BASIC. BASIC stands for Beginners All-purpose Symbolic Instruction Code. Visual Basic is called visual as you can do most of the program by click and go i.e. visually. It’s an event driven & object based language.

  • What is Event Driven Language?When we say event driven it means that you can write triggers which will initiate on specific events for example when user opens any document or saves any document

  • What is Object Based Language?Object based language is language in which we can use objects & their properties i.e. we can use the Worksheet object and its property SheetName when some one clicks on a button


Introduction to visual basic for application cont2

Introduction to Visual Basic for Application - cont…

  • Visual Basic for ApplicationVisual Basic for Application is called VBA as it uses Visual Basic language and is capable of using application specific objects i.e. if we talk about VBA for Excel it can use Cell Object, Range Object, Worksheet Object, Workbook Object etc.


Recording your first macro

Recording your first Macro

  • Microsoft Excel provides you a very helpful option which enables you to record a macro and also modify and run afterwards

  • Microsoft Excel don’t provide you the macro recording option by default for enabling macro recording at first you need to enable the Developer Tab, next slide defines step by step procedure to enable Developer Tab


Enabling the developer tab

Enabling the Developer Tab

  • Click on File

  • Click on Excel Options

  • On Customize Ribbon checkDeveloper tab

2

3

1


Enabling the developer tab1

Enabling the Developer Tab


Recording your first macro1

Recording your first Macro

  • When you record macro Microsoft Excel provides you with 2 options either you can record a macro using Absolute References or Relative References

  • If you go for Absolute References Excel’s behavior will be:

    • You record a macro in Cell A1 type “Excel is Great” format Bold Cell B1

    • Whenever you play / run this macro Excel will always type “Excel is Great” in Cell A1 and format Bold Cell B1 no matter wherever your cursor is


Recording your first macro2

Recording your first Macro

  • You can refer to following tab that you can click on Use Relative References which will enable you to record a macro in relative cells, as discussed whenever a relative reference macro is executed it will run in-place


Recording your first macro3

Recording your first Macro

  • Click Developer Tab

  • Click on Use Relative Reference

  • Click on Record Macro

1

3

2


Recording your first macro4

Recording your first Macro

  • As soon as you press record macro button the following screen will appear

  • In the Macro name boxyou have to type the nameof macro

  • In Shortcut key you can typeshort cut key which invokethe Macro

  • Store macro (will discussedlater)

  • You can also provide description of the macro


Recording your first macro5

Recording your first Macro

  • As soon as you press OK in the previous dialog box two things happens first record macro button changesto Stop Recording

  • Secondly Excel starts lookingfor your action and will recordthe same

  • Please follow steps on the following slide to complete recording


Recording your first macro6

Recording your first Macro

  • Click on Cell A1 type “Excel is Great”

  • Select cells from A1:C1 click on Home Tab and click on Merge & Center

  • Press Bold & Italics

  • Now click on Developer Tab and click on Stop recording


Playing your macro

Playing your Macro

  • You can execute the macro you just recorded by either clicking on the Macro button on the Developer Tabor by pressing Alt F8, you can see the list of Recorded macros in the dialog box you can press Run buttonto execute the macro as soonas you press run you can seethat your recorded macro willexecute


Behind the scenes

Behind the scenes

  • To see what Excel did behind the scenes you can either click on Macros button on Developer Taband select Edit or you can press Alt F11


What it looks like

What it looks like

  • This is what Excel did behind the scenes, it written the code for you. You can change the code if you want. You can either closethe window or pressAlt F11 again to goback to your Excelnormal screen.


Example11

Example1


Example12

Example1


Example13

Example1

Working with Excel while recording the macro


Example14

Example1


Example15

Example1


Example16

Example1


Looking inside the vb code of our macro

Looking inside the VB code of our Macro


Using form controls

Using Form Controls


Using form controls1

Using Form Controls

Using the Form Controls select a GUI element and by mouse-click place it on the frame


Using form controls2

Using Form Controls


Using form controls3

Using Form Controls

Select

The Cell and click on button


Example2 develop a macro to calculate the average of five cells to the left of a selected cell

Example2: Develop a macro to calculate the average of five cells to the left of a selected cell.

Initialize a spreadsheet and fill five vertical cells with numbers. Select the cell to the right


Example22

Example2

Start macro-recording and modify

macro properties


Example23

Example2

Input formula in cell C2 and then stop macro-recording.


Example24

Example2

Test the macro by filling other five cells with numbers, then select the cell to the right

and then click shortcut-key. (we had chosen [Ctrl-a] )


Example25

Example2

Click [Alt-F11] to access VBA module


Example3

Example3

Writing a subroutine to solve a set of linear equations while allowing the user to input cell-range.

Step 1. Click [Alt-F11] to open the Visual Basic Editor.

Step 2. Select [Insert][Module].


Example31

Example3

Step 3. Type in the following code:


Example32

Example3

Step 4. Go back to the Excel workbook by clicking [Alt-F11] once more. Then click [Alt-F8] to change the macro options and select shortcut key (plus add description if desired).


Example33

Example3

Step 5. Test the macro. First prepare the spreadsheet with a matrix which will be treated as matrix A and column of cells to be treated as vector b. Then run the macro.


Conclusion

Conclusion

  • Macros are used to automate tasks or you can use macros to develop user interfaces

  • Excel Macro use a language called VBA (Visual Basic for Applications), which is both Event Driven and Object Based

  • The easiest way to look at macro is by using the Excel’s record Macro Feature

  • You can even edit Excel’s recorded macro by yourself

  • Soon we will publish a presentation which will guide you on how to write your own macro


Finish

FINISH


  • Login