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:

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:

• Format
• MsgBox
• Range
  
• Variables in VBA
  

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 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!