Excel allows us to easily manage our accounts . Although sometimes it is necessary to know how to create a macro with VBA in Excel, to make our work easier, using Visual Basic for Applications (VBA) codes; For things like numbering invoices or receipts, you don’t need to be a programmer.

What is an Excel Macro?

Macros are codes that are inserted into our Excel file , to execute a set of instructions. Which can process and modify the value and / or appearance of one or more cells, even located on different sheets or even in other files.

For that, we must use a programming language called Microsoft VBA . Which is used to program not only in Excel, but also in other programs in the Microsoft Office package and also in Windows; thus expanding the possibilities and functionalities of all of them.

pantalla excel para grabar una macro

What are the most common commands for macros in Excel?

The VBA commands or instructions for Excel macros are many ; there are instructions to move between sheets or cells, change their value, the color of the letters or background, to put borders, to show 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 using a macro to save a book without asking or deleting a file.

The codes of a macro will always be between a heading and an ending “Sub Nombre DeLaMacro ()” and “End Sub” respectively. We will briefly mention some of the most used.

  • Move to an Excel sheet: Shetts (“SheetName”). Select
  • Select one or more cells: Range (“CellRange”). Select
  • Clear the content of a cell selection: Selection.ClearContents
  • Bold text from a 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 know more about all the instructions that you can execute in an Excel macro, you can visit the official Microsoft Office guide where you will learn in greater depth about the subject.

How to number invoices or receipts in Excel automatically?

Assuming we already have our invoice or receipt; we can incorporate a macro using a button. When it is pressed, the instructions to capture the value of the cell and increment it will be executed . Although a macro can also be run automatically when opening an Excel workbook.

macro de excel para numerar una factura o recibo

To insert a button in Excel, we must first have the “Developer” tab enabled ; It is usually hidden, but we activate it in the File menu, and then “Excel Options> Customize Ribbon> Main tabs> Developer”.

To place the button, we go to the aforementioned Developer tab, and go to the option “Insert> Form controls> Button” . The cursor will change to a crosshair, to draw the button area with the mouse, and then the “Assign macro” window will open.

We will assign the name of the macro and click on “New” to open the “Microsoft Visual Basic for Applications”; We will automatically see the module where we will place the code, to increase the value of the cell that shows the invoice number.

If the cell is located at position F3, we will write Range (“F3”). Value = Range (“F3”). Value + 1 ; so when we press the button, cell F6 will increase in number. We can also clean the cells that have data from the previous invoice, such as product descriptions, prices and units.

To delete the cells you will have to add a couple more lines. For example, if we want to clear the cells between C3 and C12 we write Range (“C3: C12”). Select and then Selection.ClearContents .

We finish by saving the macro in the button shaped like a floppy disk, or in the menu option “File> Save Book1” . The VBA project will be saved by default in Book1 and the macro in Module1; at any time we can edit or modify the macro in Excel, when we need to make an adjustment.

With this, we will have our button working to number invoices or receipts in Excel automatically. Which will facilitate the management of our accounts , without fear of making mistakes when typing what is the next number to place.

Helpful?, please rate!
[Total: 0 Average: 0]