• 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 48 Current »








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 we 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 generally very helpful.

End Sub

  6. By now you may have noticed that it seems a bit strange to first select the sheet and then delete the selected sheet. That is correct, we don't have to select a sheet first to delete it.

    Sub DeletingSheets()
    
Application.DisplayAlerts = False	' Turning all warning displays off
Sheets(Sheets.Count).Delete			' Deleting a sheet without first selecting it. This will also save us some time when running,
									' especially if we had a lot of sheets.
Application.DisplayAlerts = True	' It is recommended to always turn the warning displays on when we no longer need them disabled, 
									' as they are generally very helpful.

End Sub

  7. We now have a working macro that deletes the last sheet in our workbook. The last step is to make it delete several sheets, but not the first. We also want it to be a dynamic procedure, meaning that it works wether we have e.g. 3 or 44 sheets.
    To accomplish these goals, we will use a Do While...loop.

    Sub DeletingSheets()
    
Application.DisplayAlerts = False   ' Turning all warning displays off

Do While Sheets.Count > 1		' Making the While loop keep going until the total number of sheets equals 1.
								' This will solve our task, as we are always deleting the last sheet available inside the loop,
								' thus not ever touching the first sheet.
    Sheets(Sheets.Count).Delete         ' Deleting a sheet without first selecting it. This will also save us some time when running,
                                        ' especially if we had a lot of sheets.
Loop

Application.DisplayAlerts = True    ' It is recommended to always turn the warning displays on when we no longer need them disabled,
                                    ' as they are generally very helpful.

End Sub
  8. The task is now solved, and running the macro will result in all sheets deleted except the first one. Congratulations!


  • No labels