Saved 5/12/16 12:46 PM to Microsoft Excel

Programming of macroes on VBA in Excel  See details »

Video subtitles:
  • 00:00: Hello my they call Nikolai Pavlov I'm a trainer and an expert on Microsoft Office products and by excel in particular a also the author of the site planet x elle.ru in this video tutorial I I would like to tell on the main principles creation and use of macros and custom functions on visual basic microsoft excel let's consider the situation with macros on a simple example suppose you have to do in exele monotonous repetitive
  • 00:30: chain of routine it can be like something complex for example combing reports or in the game after 1s or some other of corporate programs in excel or something simple but common for example one of typical situations the question is asked often in my training as rapidly transform value formulas i I think to many of you is known opportunity using special rate for this there is
  • 01:00: cells are highlighted copied and special insert are inserted only value instead the function formula not less if you often have do this Procedure for example several Once a day that's possible it makes sense to write for this simple macro to occupy it there will be 45 rows very quickly and run with a single button or even by a combination of keys the macro is essentially very simple thing this program written in special language who understands excel and office language given case called visual
  • 01:32: basic for applications usually reduced to jubilee of heaven and if you know the basic the syntax of this language then you can always write a macro which will perform for you some chain action that you do not want to perform manually basic functional to work with macros is on the developer and presents itself here this here is the group code visual basic button opens the editor macros in visual basic
  • 02:03: macro button we will need later in order to run recorded macros edit them hit them hang on key combination and so on button macro recording includes a macro recorder this is a special program that will sit in memory computer track all your actions mouse and keyboard and fix them in the form commands on the visual BASIC such a kind programmer for dictation or the transcript of your action on the visual
  • 02:33: BASIC consider It's a little later if you can not see the tab the developer means by you do not have it included to display need will click right mouse button in any place tapes choose command setup tapes and in the appeared window tick opposite the tab developer and it will appear now let's let's try to write down simple macro direct programming then there is an input code in editor and visual basic press to the visual basic button on the
  • 03:04: developer tab and over the x or this here is the editor window microsoft visual basic for applications as is easily seen in top left corner our own the only book the sheet in it actually here he is there is also here we can insert the so-called new the module is actually module is special container in which stored macros if so it is easier then can represent
  • 03:34: a thread invisible to itself sheet which will be added to our a book on which the text will be stored macros to the visual BASIC insert a new clean module and enter there code for the new macro use the menu insert graves what appears further imagine simple text editor where with the keyboard you need to enter the code macros any macro always begins with operator sap from English words dog procedure in the end
  • 04:05: further will automatically be added and sap final statement which finishes macro code after sap through a space you must enter a name macro the name can be any You can name macros and Russian loading the only condition start with a letter and without spaces also name should contain special characters like percent exclamatory signs and so on let's call our macro replace it because We have replaced
  • 04:36: formula assignment after pressing enter as you see excel automatically writes down the ending turns on the backlight the syntax of different flowers makes capital letters for convenience of reading so further that is tries hard be useful now introduce basic macro code repeat it one more time our macro should go over all cells allocated area and for each replace cells formula by value technically I see
  • 05:06: afraid of this code will look in the following way literally three lines if you own though would be basic English then I think more or less it should be clear for which sell in selection for each cell
  • 05:37: allocated area do that formula me and hole meaning cell formula on pour and excel it trailing statement cycle that translates it to the next check and sorts out all ethics allocated area until they will end for for example for to understand that the macro has finished work and everything is fine let's add to end before m sap more one operator who will output to screen small window messages to the visual
  • 06:07: BASIC such a team called masich box excellent macro ready keep typed macro special in a separate way does not need technical macro stored as we spoke inside module a can stored inside the book so if you want to save macro is enough will be easy
  • 06:38: save the whole book the only nuance who appeared since 2007 version excel and is that Now for files with macros are separate special type files and they excel and sex so when we we will save the book will need must choose file type as a book excel support macros and unusual excel book also possible used and old format book excel 2003
  • 07:08: pharmatex forest because what can he do contain and not contain a macro in any case supported the select format of the book with macro support you need to save and now let's back to the main window and let's try both our macro works several numbers and suppose
  • 07:40: sum these numbers with each other at simple formulas and then I copy the formula to all trumpet now I will highlight several cells, suppose I I want to turn formulas of meaning are not in all cells a only in certain and let's run our macro run it can be done with Macro buttons on tab developer in the appeared window
  • 08:11: it is necessary to highlight our macro here in principle will lists all macros that you available in this now is our macro substitute and click on the button perform hooray macro triggered drove the window messagebox Click ok in this window to close it and check selected cells as you can see remained only values ​​in the remaining still formula macro work if you plan to launch macro is frequent enjoy and much
  • 08:41: Once a day that's possible it makes sense here in Macro window appoint him key combination for this can take advantage button options like you can see here enter any control + letter By the way, not the numbers do not punctuation marks namely the letters with take into account that in In this case excel distinguishes between small and big letters that is uppercase and lowercase and distinguishes Russian English font so control q & control and brief for example
  • 09:12: this land to be two different max others way use of macros is spelling custom functions so called define functions or for df we reduce back to the editor visual basic for these function or other You can insert a separate module as we are already done through the menu insert but it can be a ZIL do right here in the same modules number of macros within each module is not limited
  • 09:42: technical function differs from simple theme macro which uses the operator does not sap and funcion and returns resultant meaning they fulfill some actions on cells suppose we want create a simple function which calculates the amount vat 17 percent of of his argument technical on visual basic it will look like in the following way I put the function operator garden place next
  • 10:13: function name suppose the VAT same as any functions in brackets should specify which the arguments will be be used suppose we have this there will be an amount after pressing enter as in previous case excel automatically writes down the ending now between fancings and and function need to enter operators for calculating VAT received in as an argument in amount technically case there of course simple formula of VAT equals
  • 10:43: 0.18 times more pay attention to that in this case is used . between whole and fractional part of the visual BASIC There is no choice here always . separator between whole and fractional part as opposed to exel where this the parameter sets c original settings in either menu file parameter I save the file close visual editor basic returning to the target custom
  • 11:15: function is different from macros so that not starts familiar to us already through the window macros as you can see there is no added vdf functions it is not added here it is added as a new function in function wizard so that its I'll click to use to the familiar button fx and in which window function wizards choose a new category that here as you can see appeared certain user define franchises here is our vat function
  • 11:46: I choose her clothes and everything goes on in the usual way as and for any other standard functions need to specify arguments in this case this is a cell sum let's take number from the next column, click ok. we get 18 percent of this amount naturally also can this business to stretch down all will work you can use macros in two cases, either for automation routine
  • 12:16: repetitive action in this we write code macro using operator sap or for addition to excel missing us function vat sum in words or something more interesting of course lies in how to write Here such code such Here you can use key operators words of the function here the syntax and so further but this topic certainly not for short webinar a for a large training or great beautiful thick books in anyways hope
  • 12:47: you become clearer what are macros for what to use them and you stopped Be afraid if you were afraid when you know everything just