Where can I start VBA Excel
Easily create and run Excel macros
To display existing macros, open the dialog window with View / Macros / Show macros:
3) Inclusion of a macro with absolute references
You will now create your first macro. By default, macros are included with absolute references; more on this shortly.
Open the "Record Macros" dialog box and enter a name for the macro, for example "TestAbsolut".
If you would like to start the macro later using a certain key combination, choose an appropriate combination. However, always make sure that the key combination in question is not already used elsewhere!
Then you have to specify whether the macro should be carried out in the current workbook or in your personal workbook. The advantage of "This workbook" is that, for example, colleagues or other users can also execute your configured macros. Saving it in your personal workbook only makes sense if you are dealing with special key combinations or abbreviations or with certain formatting etc. that you only want to work with yourself.
Finally, you have the option of entering a description for the macro. In our example we use "copy absolute".
After clicking on "OK", all the steps you have taken in the open workbook from this point on will be recorded.
As soon as the recording of the macro has started, the macro symbol changes in the status bar at the bottom left ...
... to a stop symbol (dark square).
Have you carried out all the steps that are relevant for the current macro (in our example, copying the contents of cell A1 into cell A3), ...
... then you end the recording of the macro, for example by clicking on the stop symbol in the status bar at the bottom left or in the dialog window on "Stop recording".
4) Inclusion of a macro with relative references
Next, let's look at a macro with relative references. By default, as you heard earlier, macros are included with absolute references, but you also have the option to include macros with relative references. You can see which option is currently active by the bottom icon in the "Macros" menu.
To record an Excel macro with relative references, first click on the command "Use relative references" (see above) and then open the "Record macro" dialog box again so that you can create a new macro with a different name (in our example " TestRelativ ") can create.
After you have carried out the copying process from cell A1 to cell A3 exactly as before, you end the recording of the macro as described above.
So what's the difference between the two macros you just configured?
The "TestAbsolut" macro always copies the content of an active (selected) cell into cell A3. For example, if you write something in cell D4, then open the "Show macros" dialog box ...
... and execute the "TestAbsolut" macro, ...
... after clicking on "Execute" the content of the (active) cell D4 is copied into cell A3.
If, on the other hand, you write something in cell D4, open the "Show macros" dialog box (see above) and run the "TestRelativ" macro, ...
... after clicking on "Execute" the content of the (active) cell D4 is copied into cell D6, ...
... because cell D6 is in the same relation to cell D4 as cell A3 was previously to cell A1 - that is exactly two rows below.
5) How can macros that have already been created be edited afterwards?
There are several ways to edit existing macros.
In the "Show macros" dialog box, you can subsequently change the description of the Excel macro concerned by clicking on "Options", as well as adding a shortcut or changing or deleting the existing one.
Clicking on "Edit" will take you to the code of the macro that you recorded.
6) Further possibilities for the execution of recorded macros
One of several ways to run existing Excel macros is via the "Developer" menu, which does not appear in the menu bar by default. To activate this, first go to "File" and "Options" ...
... to "Customize Ribbon".
On the right side you will find the item "Developer Tools" next to the one not activated box. You put the tick in the empty square and then click on OK.
Then the "Developer Tools" tab will appear in your menu ribbon.
Clicking on the "Insert" icon opens a dialog window with the help of which you have the option of inserting both so-called form controls and ActiveX controls into your workbook.
How you use the various elements, e.g. to insert a button and assign a macro to it, how to edit the button and the attached macro and what other options are available to you with the help of the developer tools when working with Excel macros will be explained to you Explained step by step in the video.
In it you will find out, among other things. also how you can add more symbols to the quick access to assign specific macros to these symbols, or how you can adapt and adapt the tabs for using your macros.
7) Editing the macro code
As already described above, a distinction is made between absolute and relative macros when recording macros. You can tell the difference if you take a closer look at the codes of the two macros you have configured.
In the macro above ("TestAbsolut") you will find the absolute references with the selected cells A1 and A3. In both cases, the range object is used.
In the lower macro ("TestRelativ"), the ActiveCell, ie the "active" cell selected at the moment the macro is executed, is used in order to then copy the content of this cell into another cell with the corresponding relation. In our example (see above) this was copying the contents of cell D4 into cell D6.
The advantage of working with relative references is that you can use such a macro in other places, in other worksheets, and so on.
In the code for the macro with the relative reference, you can see the offset command "Offset (2, 0)" and the range ("A1"), which means that - relatively speaking - the ActiveCell looks up 2 lines down and 0 columns to the right or left to run the macro.
For example, if you change the offset information in (3, 2), ...
... the content of the active cell is copied into a cell 3 rows below and two columns to the right - as seen from the starting cell (ActiveCell).
But of course you also have the option to let the whole thing run in the other direction. You then enter the corresponding minus values for rows upwards and for columns to the left.
Example: The content of the ActiveCell E6 is to be copied 3 rows up and 4 columns to the left into cell A2.
You can also enlarge the area into which the content of the active cell is to be copied by increasing the range. In our example we want to cover an area of two cells.
You can also find this example explained in the video.
- Is it possible to be too distant?
- How did Pink Floyd affect you
- What is the psychology behind non-binary
- The merger with Disney Pixar was a success
- ASP NET is compared with Java Serverlet
- You can see satellites during the day
- Why should a teacher have patience
- Why are Mindvalley courses so expensive
- Where do portfolio managers invest their money
- What is life like after graduating from IRMA
- Who should I never seek advice from?
- Which company manufacturers I C engines
- Where is the oldest theater in Russia
- What can cause secondary amenorrhea
- Have you ever sneaked into a movie?
- How many courses are there in pipelines
- Was an invasion of Iran really necessary during WWII
- How do divisions affect the flow of water
- Learn English How to learn English
- Revised GST invoice
- What is an unauthorized immigrant
- Spike sucralose insulin
- What is Bobby Blitz's vocal range
- How does the epidermis protect your skin
- You can mix olive oil and onions
- What are examples of Indian hard work
- Who sang the song Estranged in 1991
- Revlon Uniq One makes hair white
- What is the meaning of Miller indices
- Can search SharePoint in documents
- What do you study in M Com
- What is PSI in Schroedinger's wave equation
- When was the International Monetary Fund founded?
- Overactive thyroid gland can cause infertility