Versions Compared

Old Version 100

changes.mady.by.user Unknown User (jonakaa)

Saved on

compared with

New Version 101

changes.mady.by.user Unknown User (jonakaa)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Info

The tutorials on VBA will include both videos and text. If you have feedback on either, please leave a post on our Forum or send us a mail.



Panel
borderColor#dfe1e5
bgColor#eff9ff
borderWidth2
titlePage content

Table of Contents


Introduction to VBA in Excel

Hello World

Our first encouter with VBA will be to display the familiar "Hello World" message using a button.

Expand
titleHello World tutorial, text
  1. Choose the "Developer" tab and open the VBA editor. If you don't locate the "Developer" tab, follow this guide. The following window will then open.
  2. To write our first macro, choose "Insert" -> "Module".

  3. First, we will make a Sub procedure that we may later add to our button. All Sub procedures have to begin with Sub and end with End Sub. A Sub can not return a value, and may be compared with a program in other programming languages, where as a function is an alternative. We then call the MsgBox function to create a new window displaying our message.


  4. Lets now return to our Excel sheet to add the button. The insert button feature is shown in the image below.

    Info

    Tips: To align the size of the button with existing cells, hold down ALT while resizing. This applies to all sizings!


  5. A new window will open. Choose the "Hello World" macro we just created and click "OK".


  6. We may then rename our button by right-clicking on it and choosing "Edit text".



  7. Finally, we can test our macro by clicking on the button. A window displaying the message "Hello World!" should then open.
    If it does, congratulations! You have just made your first macro using VBA.


Expand
titleHello World tutorial, video

Hello World video tutorial (no audio)


Recording a macro

Even though a lot of your work in VBA both can and will be made using only programming, it is highly recommended to get to know the Record Macro feature as well. When using Record Macro, VBA will track and translate every action you perform to code. This can help you in several ways:

  • It is always easier to start a task with some existing code instead of a blank sheet.
  • Use the fact that VBA translates all your actions into code to your advantage. Instead of searching online for e.g. how to mark a range of cells, try recording a macro doing it and then look at the code created.

Our first task will be to duplicate a sheet using the Record Macro feature.

Expand
titleDuplicate sheet tutorial, text
  1. Make a simple sheet to duplicate.
  2. Locate the Developer tab, and click on Record Macro.
  3. A new window should then open. You always want to name your macros something meaningful, making it easy to understand what macro does by just the name. This applies especially when the number of macros increase. Click then OK.
  4. VBA is now recording our actions.
  5. To duplicate a sheet in Excel, right-click on the sheet name, and click on Move or Copy...
  6. In the window appearing, mark off for (move to end) on the location, and tick off for Create a copy. Click then OK.
  7. A duplicate has now been made.
  8. Time to stop the recording. This is done by clicking on Stop Recording, located the same place as the Record Macro was.
  9. To look at the code created by VBA, open the Visual Basic Editor.
  10. Open Module1, and the code is displayed.
  11. If we want to run the code again, we can click somewhere inside the subroutine DuplicateSheet() and then click on the play icon.
  12. Our Sheet1 is then duplicated again.
  13. If we wanted, we could also have made e.i. a button and attached our macro to it, just as in the Hello World tutorial.


Expand
titleDuplicate sheet tutorial, video

DuplicateSheet video (no audio)


Making a function

In the previous tutorials, we have made procedures using the Sub statement. We will now look at how we can make a procedure using the Function statement. First, let's have a quick look at an embedded function. SUM(number1;number2...) (SUMMER in Norwegian) is a function that sums up all its parameters. This function is reachable for execution from a cell in our sheet (picture).

We will now make our very own custom function that can be adressed the same way as SUM, but the calculations made are specified by us.

Expand
titleMaking a Function, text
  1. Open the VBA editor as before and insert a new module. If you don't rememeber how, check out the Hello World tutorial. Your window should then look like the one below.

  2. Make a Function. Since this is an example, we will just do a simple calculation involving the input parameter.

    Code Block
    languagevb
    Function myFunction(num As Integer)

' Remember that the variable named the same as the function is returned
myFunction = num / 2 + 7

End Function

  3. Our function is now done and we can go back to the excel sheet to try it out. We have already inserted some numbers to do the calculations on. As with any other function, start with "=" + "function name". Excel will then autocomplete the function name, and all we have to do is choose the cell (or just write a number) to do the calculation on.
  4. You have now made your first custom function in Excel, congratulations!



Useful applications of VBA in Excel


Duplicating charts between sheets or workbooks

When duplicating (copying) charts between different sheets or workbooks, a common issue is that the new chart still refers to the old table, also if we don't want it to. However, this is easily fixed using a rather simple code. A video illustrating our problem is shown below. Our aim is to duplicate the chart (as well as the theme and settings of the chart) showing the temperatures and make it show the rainfall instead.


View file
nameDuplicate chart problem.mp4
height150


BibTeX Display Table