Macro
This query is : Resolved
29 April 2013
i have seen in a excel file where a message is displayed above formula bar
Security Warning : Macros have been disabled Options
when we have to click on options to active macros
how can i bring the above message in normal excel file ?
because "spellnumber" formula does work in the above file where the security warning message is shown
please guide
Security Warning : Macros have been disabled Options
when we have to click on options to active macros
how can i bring the above message in normal excel file ?
because "spellnumber" formula does work in the above file where the security warning message is shown
please guide
09 May 2013
IS THERE ANY EXPERT SO SOLVE MY QUERY ?
03 August 2024
To make the security warning message about macros appear in Excel and to use features like custom functions (e.g., `SpellNumber`), you need to enable macros in Excel. Here’s how you can achieve that and ensure the security warning appears when opening a file:
### **1. **Understanding the Security Warning**
**Security Warning: Macros have been disabled Options**
- This message appears when you open an Excel file containing macros, and your macro settings are set to disable all macros by default. Excel displays this warning to alert you that macros are disabled, which is a security feature to prevent potentially harmful code from running.
### **2. **Steps to Display the Security Warning**
To ensure that the security warning appears when opening a file with macros, follow these steps:
**A. **Enable Macros in Excel**
1. **Open Excel:**
- Launch Excel and go to the **File** tab.
2. **Access Options:**
- Click on **Options** at the bottom of the navigation pane to open the Excel Options dialog box.
3. **Trust Center Settings:**
- Select **Trust Center** from the left sidebar.
- Click on **Trust Center Settings**.
4. **Macro Settings:**
- In the Trust Center, select **Macro Settings**.
- Choose **Disable all macros with notification**. This option will disable macros but show the security warning message allowing you to enable macros if you trust the source.
- Click **OK** to apply the settings and close the Trust Center.
5. **Save Changes:**
- Click **OK** again to close the Excel Options dialog box.
**B. **Save and Open a Workbook with Macros**
1. **Create or Open a Workbook with Macros:**
- Either create a new workbook with a macro or open an existing workbook that contains macros.
2. **Ensure the Macro is Present:**
- For testing, you can create a simple macro in the workbook. Press `ALT + F11` to open the VBA editor, then insert a new module and add a simple macro like:
```vba
Sub TestMacro()
MsgBox "Macro is running!"
End Sub
```
- Save the workbook as a macro-enabled file (`.xlsm`).
3. **Close and Reopen the Workbook:**
- Close the workbook and reopen it. The security warning should appear above the formula bar, prompting you to enable macros.
### **3. **Utilizing Custom Functions like `SpellNumber`**
To use custom functions like `SpellNumber` (which is not a built-in function in Excel but can be added through macros), you need to:
1. **Ensure the Macro is Included:**
- If `SpellNumber` is a custom function defined in a VBA module, make sure it is included in the workbook.
2. **Enable Macros:**
- When you see the security warning, click **Enable Content** to allow macros to run.
3. **Add `SpellNumber` Function:**
- If you don’t have the `SpellNumber` function, you can add it by inserting a VBA module and pasting the function code. For example:
```vba
Function SpellNumber(ByVal MyNumber)
Dim Units As String
Dim SubUnits As String
Dim DecimalPlace As Integer
Dim Count As Integer
Dim DecimalCount As Integer
Dim TempStr As String
Dim Unit As String
Dim SubUnit As String
Dim TempNum As String
Dim DecimalPart As String
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to STRING and TRIM white space
MyNumber = Trim(CStr(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
TempStr = Left(MyNumber, DecimalPlace - 1)
DecimalPart = Mid(MyNumber, DecimalPlace + 1)
Else
TempStr = MyNumber
End If
Count = 1
Do While TempStr ""
TempNum = GetHundreds(Right(TempStr, 3))
If TempNum "" Then
Unit = TempNum & Place(Count) & Unit
End If
Count = Count + 1
TempStr = Left(TempStr, Len(TempStr) - 3)
Loop
Unit = Application.Trim(Unit)
If DecimalPart "" Then
SubUnits = GetTens(DecimalPart)
SpellNumber = Unit & " and " & SubUnits & " Cents"
Else
SpellNumber = Unit
End If
End Function
Function GetTens(TensText)
Dim Result As String
Result = "" ' Nullify result
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetUnits(Right(TensText, 1)) ' Retrieve ones place
End If
GetTens = Result
End Function
Function GetUnits(UnitsText)
Select Case Val(UnitsText)
Case 1: GetUnits = "One"
Case 2: GetUnits = "Two"
Case 3: GetUnits = "Three"
Case 4: GetUnits = "Four"
Case 5: GetUnits = "Five"
Case 6: GetUnits = "Six"
Case 7: GetUnits = "Seven"
Case 8: GetUnits = "Eight"
Case 9: GetUnits = "Nine"
Case Else: GetUnits = ""
End Select
End Function
Function GetHundreds(HundredsText)
Dim Result As String
If Len(HundredsText) = 3 Then
If Mid(HundredsText, 1, 1) "0" Then
Result = GetUnits(Mid(HundredsText, 1, 1)) & " Hundred "
End If
HundredsText = Mid(HundredsText, 2)
End If
If Len(HundredsText) = 2 Then
Result = Result & GetTens(HundredsText)
Else
Result = Result & GetUnits(HundredsText)
End If
GetHundreds = Result
End Function
```
### **Summary**
1. **Enable Macros:** To see the security warning and enable macros, set Excel’s macro settings to “Disable all macros with notification” in the Trust Center.
2. **Generate Excels Files with Macros:** Save your workbook with macros enabled (as `.xlsm`).
3. **Macro Documentation:** Ensure macros are enabled when opening the workbook and that you can use custom functions like `SpellNumber`.
This setup ensures you see the security warning when opening files with macros and can work with custom functions effectively.
### **1. **Understanding the Security Warning**
**Security Warning: Macros have been disabled Options**
- This message appears when you open an Excel file containing macros, and your macro settings are set to disable all macros by default. Excel displays this warning to alert you that macros are disabled, which is a security feature to prevent potentially harmful code from running.
### **2. **Steps to Display the Security Warning**
To ensure that the security warning appears when opening a file with macros, follow these steps:
**A. **Enable Macros in Excel**
1. **Open Excel:**
- Launch Excel and go to the **File** tab.
2. **Access Options:**
- Click on **Options** at the bottom of the navigation pane to open the Excel Options dialog box.
3. **Trust Center Settings:**
- Select **Trust Center** from the left sidebar.
- Click on **Trust Center Settings**.
4. **Macro Settings:**
- In the Trust Center, select **Macro Settings**.
- Choose **Disable all macros with notification**. This option will disable macros but show the security warning message allowing you to enable macros if you trust the source.
- Click **OK** to apply the settings and close the Trust Center.
5. **Save Changes:**
- Click **OK** again to close the Excel Options dialog box.
**B. **Save and Open a Workbook with Macros**
1. **Create or Open a Workbook with Macros:**
- Either create a new workbook with a macro or open an existing workbook that contains macros.
2. **Ensure the Macro is Present:**
- For testing, you can create a simple macro in the workbook. Press `ALT + F11` to open the VBA editor, then insert a new module and add a simple macro like:
```vba
Sub TestMacro()
MsgBox "Macro is running!"
End Sub
```
- Save the workbook as a macro-enabled file (`.xlsm`).
3. **Close and Reopen the Workbook:**
- Close the workbook and reopen it. The security warning should appear above the formula bar, prompting you to enable macros.
### **3. **Utilizing Custom Functions like `SpellNumber`**
To use custom functions like `SpellNumber` (which is not a built-in function in Excel but can be added through macros), you need to:
1. **Ensure the Macro is Included:**
- If `SpellNumber` is a custom function defined in a VBA module, make sure it is included in the workbook.
2. **Enable Macros:**
- When you see the security warning, click **Enable Content** to allow macros to run.
3. **Add `SpellNumber` Function:**
- If you don’t have the `SpellNumber` function, you can add it by inserting a VBA module and pasting the function code. For example:
```vba
Function SpellNumber(ByVal MyNumber)
Dim Units As String
Dim SubUnits As String
Dim DecimalPlace As Integer
Dim Count As Integer
Dim DecimalCount As Integer
Dim TempStr As String
Dim Unit As String
Dim SubUnit As String
Dim TempNum As String
Dim DecimalPart As String
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to STRING and TRIM white space
MyNumber = Trim(CStr(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
TempStr = Left(MyNumber, DecimalPlace - 1)
DecimalPart = Mid(MyNumber, DecimalPlace + 1)
Else
TempStr = MyNumber
End If
Count = 1
Do While TempStr ""
TempNum = GetHundreds(Right(TempStr, 3))
If TempNum "" Then
Unit = TempNum & Place(Count) & Unit
End If
Count = Count + 1
TempStr = Left(TempStr, Len(TempStr) - 3)
Loop
Unit = Application.Trim(Unit)
If DecimalPart "" Then
SubUnits = GetTens(DecimalPart)
SpellNumber = Unit & " and " & SubUnits & " Cents"
Else
SpellNumber = Unit
End If
End Function
Function GetTens(TensText)
Dim Result As String
Result = "" ' Nullify result
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetUnits(Right(TensText, 1)) ' Retrieve ones place
End If
GetTens = Result
End Function
Function GetUnits(UnitsText)
Select Case Val(UnitsText)
Case 1: GetUnits = "One"
Case 2: GetUnits = "Two"
Case 3: GetUnits = "Three"
Case 4: GetUnits = "Four"
Case 5: GetUnits = "Five"
Case 6: GetUnits = "Six"
Case 7: GetUnits = "Seven"
Case 8: GetUnits = "Eight"
Case 9: GetUnits = "Nine"
Case Else: GetUnits = ""
End Select
End Function
Function GetHundreds(HundredsText)
Dim Result As String
If Len(HundredsText) = 3 Then
If Mid(HundredsText, 1, 1) "0" Then
Result = GetUnits(Mid(HundredsText, 1, 1)) & " Hundred "
End If
HundredsText = Mid(HundredsText, 2)
End If
If Len(HundredsText) = 2 Then
Result = Result & GetTens(HundredsText)
Else
Result = Result & GetUnits(HundredsText)
End If
GetHundreds = Result
End Function
```
### **Summary**
1. **Enable Macros:** To see the security warning and enable macros, set Excel’s macro settings to “Disable all macros with notification” in the Trust Center.
2. **Generate Excels Files with Macros:** Save your workbook with macros enabled (as `.xlsm`).
3. **Macro Documentation:** Ensure macros are enabled when opening the workbook and that you can use custom functions like `SpellNumber`.
This setup ensures you see the security warning when opening files with macros and can work with custom functions effectively.
You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now
Click here to login now
Similar Resolved Queries
Unanswered Queries
- It return filing query
- Penalty on Senior citizens
- 54 Capital Gain Exemption
- Rule 37 of CGST/SGST Rules: ITC Reversal on Non-Payment Within 180 Days
- IT Return - other income
- Capital gains-3.7.25
- Appropriate IT return form
- Rent receivable from the government
- Rent is due tds implementation
- Whether Deductions for Interest paid on Home loans under New Tax regime for financial year 2024-25 f
- F & O TURNOVER CALCULATION FOR TAX AUDIT
- Re-imbursement of Paid Marine Insurance on behalf of client
- Exchange rate for export of goods
- Notice for non filing Form GSTR-4 (Annual)
- INELIGIBLE ITC
- For Reversal ITC
- Invoice related questiond
- Case laws required
- Caselaws for non consideration of exemption u/s 11
- E Bank realisation certificate
Trending Online Classes
CAclubindia
