Excel Trainer Ahmedabad

 USING MACROS INSIDE MICROSOFT EXCEL

First of all, in order to understand the usage of Macro's inside of Microsoft Excel, you must know that some of these macro's are called UDF's, or User-Defined Functions. Read on, below:

What is a UDF?

A User Defined Function is a function you create using VBA to perform custom calculations that Excel’s built-in functions can't easily do.

---

📌 Why Use Macros for UDFs?

• Extend Functionality: Excel’s built-in functions may not meet specific business or technical needs.

• Reusability: UDFs can be reused across different workbooks and shared with others.

• Automation: You can integrate UDFs into broader macros to perform full automation workflows.

---

🛠️ How to Create a UDF Using Macros (VBA)

1. Open the VBA Editor:

   • Press ALT + F11 in Excel.

2. Insert a Module:

   • Go to Insert > Module.

3. Write Your Function:

   vba
   Function AddNumbers(x As Double, y As Double) As Double
       AddNumbers = x + y
   End Function
   

4. Use It in Excel:

   • Go back to Excel and use =AddNumbers(5, 3) in a cell.

---

📝 Notes About UDFs

• UDFs cannot modify the Excel environment (e.g., they can’t change cell formatting or move cells). They're limited to returning a result.

• Execution speed: UDFs can be slower than built-in functions if written inefficiently or if used repeatedly over large ranges.

• Security warning: Excel disables macros by default for security; users must enable them to run UDFs.

---

📍 Example UDF: Convert Celsius to Fahrenheit

vba
Function CtoF(celsius As Double) As Double
    CtoF = (celsius * 9 / 5) + 32
End Function

Use as: =CtoF(25), which returns 77.

---

🧩 UDF vs. Macro

Feature	UDF	Macro
Returns a value	Yes	No (usually performs actions)
Used in worksheet cells	Yes	No
Modifies worksheet	No	Yes
Triggered automatically	Yes (when cell recalculates)	No (runs on command or event)

---

🛑 Limitations

• UDFs can't access things like:

• Message boxes or input boxes (MsgBox, InputBox)

• Formatting or inserting rows/columns

• Worksheets not currently open or recalculating