Catégorie : Excel VBA Course

Objective

Convert a given value in inches to centimeters (or other units), based on user input.

VBA Code for Conversion (inches to centimeters)

1. Open the VBA editor:
   • Open your Excel file.
   • Press Alt + F11 to open the VBA editor.
   • In the menu, go to Insert and then Module to insert a new module.
2. Add the Code:

Here is an example of VBA code to convert units (inches to centimeters).

Sub ConvertUnits()
    ' Declare variables
    Dim value As Double
    Dim result As Double
    Dim choice As String   
    ' Ask the user to enter the value to be converted
    value = InputBox("Enter the value to convert in inches:")   
    ' Check if the entered value is a valid number
    If IsNumeric(value) Then
        ' Ask the user to choose the target unit for conversion
        choice = InputBox("Enter the target unit for conversion: (cm for Centimeters, m for Meters, km for Kilometers)")
        ' Perform the conversion based on the choice
        Select Case LCase(choice)
            Case "cm"
                ' Convert inches to centimeters (1 inch = 2.54 cm)
                result = value * 2.54
                MsgBox value & " inches is equal to " & result & " centimeters."
            Case "m"
                ' Convert inches to meters (1 inch = 0.0254 m)
                result = value * 0.0254
                MsgBox value & " inches is equal to " & result & " meters."
            Case "km"
                ' Convert inches to kilometers (1 inch = 0.0000254 km)
                result = value * 0.0000254
                MsgBox value & " inches is equal to " & result & " kilometers."
            Case Else
                ' If the user enters an invalid unit
                MsgBox "Invalid unit, please choose between 'cm', 'm', or 'km'."
        End Select
    Else
        ' If the user did not enter a valid number
        MsgBox "Please enter a valid numeric value."
    End If
End Sub

Code Explanation

• Variable Declaration:
  • value: Stores the value entered by the user (in inches).
  • result: Stores the result of the conversion.
  • choice: Stores the target unit that the user wants (centimeters, meters, or kilometers).
• Getting User Input:
  • The InputBox prompts the user to enter a value in inches.
  • Another InputBox prompts the user to choose the target unit for conversion (cm, m, or km).
• Conversion with Select Case:
  • If the user chooses « cm », the conversion is done by multiplying the value in inches by 2.54 (since 1 inch = 2.54 cm).
  • If the user chooses « m », the conversion is done by multiplying the value in inches by 0.0254 (since 1 inch = 0.0254 m).
  • If the user chooses « km », the conversion is done by multiplying the value in inches by 0.0000254 (since 1 inch = 0.0000254 km).
• Displaying the Result:
  • The MsgBox displays the conversion result in a message box.

How to Use This Code:

1. Copy the VBA code into a module as described above.
2. Press F5 to run the script.
3. The program will ask you to enter the value in inches that you want to convert, and then choose the target unit (cm, m, or km).
4. The result will be displayed in a message box.

Example:

If you enter the value 10 for inches and choose « cm » as the target unit, the message displayed will be:

10 inches is equal to 25 centimeters.

Possible Improvements:

• Add additional conversions for other units (e.g., convert to feet, yards, etc.).
• Allow the user to input the value directly into an Excel cell and automate the conversion based on data in the worksheet.

Objective:

We will create a VBA macro that will convert text to either uppercase or lowercase based on the user’s selection.

Explanation of the code:

1. Define the conversion function: We will use Excel’s built-in functions UCase to convert to uppercase and LCase to convert to lowercase.
2. Select the cell to process: The code will work on the selected cell(s).
3. Prompt the user to decide whether to convert to uppercase or lowercase.
4. Apply the conversion to the selected cells.

VBA Code:

Sub ConvertText()
    ' Declare a variable to store the user's choice
    Dim choice As String
    ' Ask the user whether they want to convert to uppercase or lowercase
    choice = InputBox("Enter 'M' to convert to uppercase or 'm' to convert to lowercase.", "Choose Conversion")
    ' Check if the user has selected a cell
    If TypeName(Selection) = "Range" Then
        ' Check if the selected cell is not empty
        If Not IsEmpty(Selection.Value) Then
            ' If the user chose 'M', convert to uppercase
            If choice = "M" Then
                Selection.Value = UCase(Selection.Value)
            ' If the user chose 'm', convert to lowercase
            ElseIf choice = "m" Then
                Selection.Value = LCase(Selection.Value)
            ' If the user enters an invalid choice, display an error message
            Else
                MsgBox "Invalid option. Please enter 'M' for uppercase or 'm' for lowercase.", vbExclamation
            End If
        Else
            MsgBox "The selected cell is empty.", vbExclamation
        End If
    Else
        MsgBox "Please select a cell containing text.", vbExclamation
    End If
End Sub

Explanation of the Code:

Asking for the conversion option:

choice = InputBox("Enter 'M' to convert to uppercase or 'm' to convert to lowercase.", "Choose Conversion")

• • This line displays an input box where the user can enter either « M » for uppercase or « m » for lowercase.

Check if the selection is valid:

If TypeName(Selection) = "Range" Then

• • This checks if the user has selected a valid range of cells. If no cell is selected, an error message will be shown.

Check if the cell is not empty:

If Not IsEmpty(Selection.Value) Then

• • This condition ensures that the selected cell is not empty before attempting to convert the text.

Convert to uppercase:

If choice = "M" Then
    Selection.Value = UCase(Selection.Value)

• • If the user chose « M », the UCase function is used to convert the text to uppercase.

Convert to lowercase:

ElseIf choice = "m" Then
    Selection.Value = LCase(Selection.Value)

• • If the user chose « m », the LCase function is used to convert the text to lowercase.

Error messages:

MsgBox "Invalid option. Please enter 'M' for uppercase or 'm' for lowercase.", vbExclamation

1. • If the user enters anything other than « M » or « m », an error message is displayed.

How to Use:

1. Insert the code into the VBA editor:
   • Press Alt + F11 to open the VBA editor.
   • In the menu, click Insert > Module.
   • Paste the code into the module window.
2. Run the macro:
   • Return to Excel and press Alt + F8, then select ConvertText and click « Run ».
   • A prompt will appear asking whether you want to convert to uppercase or lowercase.

Possible Improvements:

• Add a check to convert only text cells (ignoring empty or numeric cells).
• Extend the functionality to support other types of transformations, like title case (capitalizing each word).

This code simplifies the process of converting text in Excel, providing users with an easy way to choose between uppercase and lowercase conversions.

Objective:

The goal is to convert a text string that represents a number into an actual numeric value in an Excel cell. Sometimes numbers are stored as text, which can cause problems when performing calculations. We will solve this issue using VBA.

Example VBA Code:

Sub ConvertTextToNumber()
    ' Declare a variable to store the cell reference
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text that can be converted to a number
        If IsNumeric(cell.Value) And IsEmpty(cell.Value) = False Then
            ' Convert the text representing a number into an actual number
            cell.Value = CDbl(cell.Value)
        End If
    Next cell
    ' Display a message when the conversion is complete
    MsgBox "Conversion complete!", vbInformation
End Sub

Code Explanation:

1. Declaring the cell variable:

Dim cell As Range

This line declares a variable cell to represent each cell in the selected range.

For Each loop:

For Each cell In Selection

This line starts a loop that will go through each cell in the active selection (the range of cells you have selected in Excel).

Checking the cell’s content:

If IsNumeric(cell.Value) And IsEmpty(cell.Value) = False Then
Here, we check two conditions:

1. • IsNumeric(cell.Value): This function checks if the content of the cell is a number (even if it is in text form).
   • IsEmpty(cell.Value) = False: This check ensures that the cell is not empty.

If both conditions are true, it means the cell contains a text string that represents a number.

Converting text to number:

cell.Value = CDbl(cell.Value)

CDbl is a function that converts the text to a numeric value (a double precision floating-point number). It is used here to convert the text representation of a number into an actual number.

End of the loop: The loop continues with the next cell in the selection until all cells have been processed.

Completion message:

MsgBox "Conversion complete!", vbInformation

After the process is finished, a message box appears to inform the user that the conversion is complete.

How to Use the Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, click on Insert > Module to insert a new module.
3. Copy and paste the code above into the module.
4. Return to Excel, select the cells containing the text values that represent numbers.
5. Press Alt + F8, select ConvertTextToNumber from the list of macros, and click « Run ».

Possible Improvements:

• Error Handling: If a cell contains non-convertible text (like « Hello » or any other word), you can add error handling to prevent the code from crashing. For example:

On Error Resume Next
cell.Value = CDbl(cell.Value)
If Err.Number <> 0 Then
    MsgBox "Conversion error in cell " & cell.Address
End If
On Error GoTo 0

• Conditional Conversion: You could extend the logic to convert only specific types of text (e.g., numbers with certain formatting) or to skip cells containing dates or formulas.

 Objective:

The purpose of this code is to convert numbers (either integers or decimals) to text while preserving their format. You can use this code to manipulate data in an Excel worksheet via VBA.

VBA Code to Convert Numbers to Text

1. Open the VBA editor in Excel:
   • Press Alt + F11 to open the VBA editor.
   • In the editor, click Insert > Module to insert a new module.
2. Copy and paste the following code into the module:

Sub ConvertNumbersToText()
    Dim rng As Range
    Dim cell As Range
    Dim number As Double
    Dim text As String   
    ' Select the range of cells to convert
    Set rng = Selection   
    ' Check if a range is selected
    If rng Is Nothing Then
        MsgBox "Please select cells with numbers to convert", vbExclamation
        Exit Sub
    End If   
    ' Loop through each cell in the selected range
    For Each cell In rng
        ' Check if the cell contains a number
        If IsNumeric(cell.Value) Then
            ' Get the value of the cell
            number = cell.Value           
            ' Convert the number to text
            text = CStr(number) ' CStr function converts the number to text           
            ' If the number is an integer, you can choose to format the text without decimals
            If Int(number) = number Then
                ' Convert to text without decimals
                text = CStr(Int(number))
            Else
                ' If the number has decimals, you can choose a specific format
                text = Format(number, "0.00") ' Example: format with two decimals
            End If           
            ' Replace the numeric value with its text equivalent
            cell.Value = text
        Else
            ' If the cell does not contain a number, leave it unchanged
            cell.Value = "Non-numeric"
        End If
    Next cell
End Sub

Detailed Explanation of the Code:

1. Variable Declarations:
   • rng: Represents the range of cells that the user selects.
   • cell: Represents each individual cell in the selected range.
   • number: Holds the numeric value of each cell.
   • text: Holds the text version of the number.
2. Selecting the Range:
   • The code starts by capturing the range of cells you have selected in Excel (Set rng = Selection).
3. Checking the Data:
   • If no range is selected, an error message pops up (MsgBox).
   • For each cell in the selected range, it checks if the cell contains a number using IsNumeric(cell.Value).
4. Converting the Number to Text:
   • If the number is an integer (Int(number) = number), it converts it to text without decimals. If it’s a decimal number, it is converted with a specific format (e.g., 2 decimal places).
5. Replacing Values in Cells:
   • The numeric value in each cell is replaced with its text equivalent (cell.Value = text).

Example Usage:

• Select the cells containing numbers.
• Run the macro by pressing F5 in the VBA editor or by assigning a button to the macro in Excel.

Formatting Options:

• The formatting is flexible in this solution. For example:
  • You can use Format(number, « 0.00 ») to display two decimal places.
  • If you want more decimals, adjust the format, like « 0.0000 » for four decimal places.
  • If you don’t want any decimals, use « 0 ».

Conclusion:

This VBA code provides a flexible way to convert numbers to text while allowing you to customize the formatting of the conversion. You can easily adapt this code for different scenarios based on the type of data you’re working with in Excel.