Excel allows manage our accounts very easily . Although sometimes necessary know how to create a macro with VBA in Excel , to facilitate our work, using codes visual Basic for Applications (VBA); for things like number the invoices or receipts, you do not need to be a programmer.
What it is an Excel macro?
Macros are codes that are embedded in our Excel file to execute a set of instructions. The which can process and modify the value and/or appearance of one or more cells, located even on different sheets or even in other files.
For that, we use a scripting language called Microsoft VBA . Which is used to program in Excel not only, but also in other programs the Microsoft Office suite and Windows; thus expanding the possibilities and capabilities of all of them.
You may also be interested in:
What are the most common commands for macros in Excel?
The commands or instructions VBA for Excel macros are many ; there are instructions to move or cell sheet, change its value, the color of the letters or background, to put edges to display alerts, among many other things.
There is a macro instruction for anything you can normally do in Excel using the keyboard and mouse. From selecting a cell to use a macro to save a book without asking or delete a file.
The codes of a macro will always be with a header and an ending » sub Name DeLaMacro ()» and «End Sub» respectively. briefly mention some of the most commonly used.
- Move to an Excel: Shetts ( «NombreDeLaHoja») Select
- Select one or more cells: Range ( «RangoDeCeldas») Select
- Delete the contents of selected cells: Selection.ClearContents
- Put text in bold selection: Selection.FontBold=True
- Underline the text of a selection: Selection.Font.Underline=True
- To put an alert window: MsgBox «Your message in double quotes»
If you want to learn more about all instructions can be executed in an Excel macro, please visit the official guide Microsoft Office where you will learn deeper about it.
How to number invoices or receipts Excel automatically?
Assuming you already have our invoice or receipt; We can incorporate a macro using a button. When it is pressed, instructions are executed to capture the value of the cell and increase it. Although you can automatically run a macro to the Excel open a book.
To insert a button in Excel, first we must have enabled the tab «Developer» ; usually it is hidden, but activate the File menu, then «Excel Options & gt; Customize Ribbon & gt; Main tabs & gt; Developer «.
To place the button, we headed to the aforementioned Developer tab, and go to the «Insert & gt; Form Controls & gt; Button «. The cursor will change to a cross, to draw the area with the mouse button and then the «Assign Macro» window will open.
We will assign the name of the macro and we press «New» to open the «Microsoft Visual Basic for Applications»; the module automatically see where we will place the code to increase the value of the cell showing the invoice number.
If the cell is placed in the position F3, write Range ( «F3») Value=Range ( «F3») Value + 1 ..; so when you press the button, the number will increase cell F6. We can also wipe cells that are above invoice data, such as product descriptions, prices and units.
To clear the cells have to add a couple of lines. For example, if we want to whiten the cells between the C3 and C12 write Range (» c3: C12.»). Select and then Selection.ClearContents
We finished saving the macro button-shaped disk, or «File & gt; Save Book1 «. The VBA project is saved by default in Book1 and macro in Module1; anytime we can edit or modify the macro in Excel , when we need to make adjustments.
With this, we will have running our button to number invoices or receipts Excel automatically. What will facilitate the management of our accounts , without fear of contradiction to the tipiar what the next number should be placed.
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?
You may also be interested in: