The worksheet function Convert() offers a wide range of possibilities for converting physical units. It supports units with prefixes such as “k” for kilo (factor 1,000), for example in “km”.
The four numeric values shown in Figure 8.37 (representing distance, energy, temperature, and pressure) are each converted into different units. At the same time, they are formatted for clearer display along with their converted results.
Sub Conversions()
ThisWorkbook.Worksheets("Sheet2").Activate
' Distance
Range("A2").Value = WorksheetFunction.Convert(Range("A1").Value, "km", "mi")
Range("A1").NumberFormat = "0,000 ""km"""
Range("A2").NumberFormat = "0,000 ""mi"""
' Energy
Range("A5").Value = WorksheetFunction.Convert(Range("A4").Value, "J", "cal")
Range("A4").NumberFormat = "0,00 ""J"""
Range("A5").NumberFormat = "0,000 ""cal"""
' Temperature
Range("A8").Value = WorksheetFunction.Convert(Range("A7").Value, "C", "F")
Range("A7").NumberFormat = "0,0 ""°C"""
Range("A8").NumberFormat = "0,0 ""°F"""
' Pressure
Range("A11").Value = WorksheetFunction.Convert(Range("A10").Value, "hPa", "mmHg")
Range("A10").NumberFormat = "0,000 ""hPa"""
Range("A11").NumberFormat = "0,000 ""mmHg"""
End Sub
Explanation:
- In the first case, a distance value is converted from kilometers to miles. The parameters « km » and « mi » specify the units. The prefix k indicates kilometers, as shown in Figure.
- In the second case, an energy value is converted from joules to calories. The parameters « J » and « cal » specify the units, as shown in Figure .
- In the third case, a temperature is converted from degrees Celsius (C) to degrees Fahrenheit (F), as shown in Figure.
- The last conversion calculates pressure in millimeters of mercury (mmHg) from hectopascals (hPa), as shown in Figure 8.41. The prefix h stands for hecto, meaning one hundred.
All cells have been formatted appropriately. Remember: text within a number format string must be enclosed in double quotation marks (« »).