• Created by Unknown User (jonakaa), last modified on 22.08.2019

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 42 Next »








Page content

Beginner

Exercise 1

In the first exercise, we will focus on variables and some calculations using arithmetic operators.


Body mass index (BMI) is a value derived from the mass (weight) and height of a person. It is a convenient rule of thumb used to broadly categorize a person as underweight, normal weight, overweight, or obese.

The BMI is defined as


Your task is to create a sheet where you can calculate your BMI by entering your name, height and weight. The calculated BMI should be displayed by the click of a button. A draft to a finished solution is shown below.

Commonly accepted BMI ranges are:


BMI [kg/m2]

Underweight< 18.5
Normal weight18.5 - 25
Overweight25 - 30
Obese> 30

There are as always a number of ways to solve the task, so don't be alarmed if your answer differentiates from our solution.


This solution is currently available as a go-through video: BMI VBA (no audio) and as the finished solution: BMI exercise.xlsm

Further documentation on functions used:

  • To make the answer more informative to the user, we may in addition to the BMI value add the corresponding BMI range (normal weight, underweight etc.). How can we do this?
  • If we were to store statistics of every calculated BMI in every range, how may we do this?
  • How could we do the same task without the use of VBA, but only using Excel commands? Is that a better approach to this exact problem, and if so, why?

Exercise 2

In this exercise it is recommended to use the Record macro-feature available in VBA, although you don't necessarily have to.

Make a macro that deletes all but the first sheet. The macro should not take into consideration if any sheets are written on. Some pictures showing before and after running the desired macro are presented below.

  1. First of all, make sure you have some extra sheets opened, as the task is to delete all but the first sheet.
  2. Although we don't have to, this is a great example to of when to use the record macro feature in VBA. If you don't know how to record a macro, check out the tutorial on recording a macro found at Beginner tutorials on VBA in Excel before proceeding. The macro we want to record is the simple action; deleting a sheet.
    Also, keep in mind that the recording will act as the first code in our own macro, so may already now give the macro our desired name, in this case "DeletingSheets".
  3. Now, enter the Visual Basic editor and open the module where our procedure is located.

  4. At the moment, "Sheet4" is always selected and then deleted. This is great, but we would not like to always delete "Sheet4" only, but rather a number of sheets. To break it up even further, let's first try to delete the last sheet available. To do this, we will use the command "Sheets.count", which returns the number of sheets in our workbook.

    Sub DeletingSheets()

    Sheets(Sheets.Count).Select		' When selecting a sheet, we may either use the name of the sheet, or the number where it's positioned.
									' By using "Sheets.Count", we will select sheet number "Sheets.Count", which will always be the last sheet.
    ActiveWindow.SelectedSheets.Delete

End Sub

  5. When running the code above, a warning will be displayed:

    To keep this warning from displaying every time we want to delete a sheet, we may disable all warnings during our procedure.

    Sub DeletingSheets()
    
    Application.DisplayAlerts = False	' Turning all warning displays off
    Sheets(Sheets.Count).Select     ' When selecting a sheet, we may either use the name of the sheet, or the number where it's positioned.
                                    ' By using "Sheets.Count", we will select sheet number "Sheets.Count", which will always be the last sheet.
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True	' It is recommended to always turn the warning displays on when we no longer need them disabled, 
										' as they are very helpful.

End Sub


  • No labels