Excel - Auto Enable Macro

Asked By atiq8961 on 04-Mar-10 01:19 AM
I download SpellIndian function by Yogi Anand from anandent.com, for my
invoice template, I insert code in to VBE standard module. Now I want to
automate the code i.e. when I open worksheet it is auto enable and when I
close worksheet it is automtic disable.
I have one worksheet for my invoice template. I do not understand how can I
automate my macro.
I am not a programmer, so please someone explaine me in details.
My code is below.
Function SpellIndian(ByVal MyNumber)

'**** Yogi Anand -- ANAND Enterprises -- Rochester Hills MI 48309 --
248-375-5710 www.anandent.com
'**** Last updated 03-Oct-2003
'**** SpellIndian (modified on 20-Sep-2003 to 1) show Rupees to
precede, and to show "" for 0 paise)
'**** ySpellRupees (on 20-Nov-2002)
'**** Excel UDF to spell Indian Currency -- Rupees and Paise into text
'**** Indian currency starts off with 1000s, and after that only with
'**** 1000 (Thousand) -- 1,00,000 (Lac or Lakh) -- 1,00,00,000 (Crore)
-- 1,00,00,00,000 (Arab)
'**** (this UDF is based on SpellNumber by Microsoft)
'****************' Main Function *'****************

Dim Rupees, Paise, Temp
Dim DecimalPlace,  Count
Redim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lac "
Place(4) = " Crore "
Place(5) = " Arab " ' String representation of amount
MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
' Convert Paise and set MyNumber to Rupee amount
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Count = 1 And Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
If Count > 1 And Len(MyNumber) > 2 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
MyNumber = ""
End If
End If
Count = Count + 1
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
'Yogi Anand on 20-Sep-2003
'modified the following two lines to display "Rupees" to precede
' rem'd the first line and added the second line
'Rupees = Rupees & " Rupees"
Rupees = "Rupees " & Rupees

End Select
Select Case Paise
Case ""
'Yogi Anand on 20-Sep-2003
'modified the following two lines to display nothing for no paise
' rem'd the first line and added the second line

'Paise = " and No Paise"
'Yogi Anand on 03-Oct-2003
'modified the following line to display " Only" for no paise
' rem'd the first line and added the second line
'Paise = ""
Paise = " Only"
Case "One"
Paise = " and One Paisa"
Case Else
Paise = " and " & Paise & " Paise"

End Select
SpellIndian = Rupees & Paise
End Function
' Converts a number from 100-999 into text *
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place

Paul Robinson replied to atiq8961 on 04-Mar-10 04:33 AM
You have four functions here


In VBA you can have functions and subs. It is subs that "do" things
automatically, like write onto worksheets or create charts or switch
between worksheets. Functions are there to perform calculations.
Functions do not change what you see on the screen or "do" things
automatically. You cannot automate functions - they must be used by a
sub and you automate the sub.
Sub Test()
Dim x as Double, y as String
x =3D  worksheets(1).Cells(1, 1).Value
y =3D SpellIndian(x)
worksheets(1).Cells(1, 2).Value =3D y
end Sub

This sub will read the value in A1 and call it x. It then calculates
SpellIndian(x) and calls it y. It then puts the value y into cell A2.
SpellIndian itself cannot put a value in A2 - a function cannot "do"
anything to the worksheet. You cannot put the line

worksheets(1).Cells(1, 2).Value =3D y

inside the SpellIndian function.

The functions ARE available to you as functions in a worksheet when
you open the workbook. In a worksheet cell type

=3D Spellindian(

and you will be prompted for a number, which can be a value in another
cell. e.g. Put 1 in A1 and in A2 type

=3D Spellindian( A1)


309 --
nto text
ly with
atiq8961 replied to Paul Robinson on 04-Mar-10 08:18 AM
"Paul Robinson" wrote:
Paul Robinson replied to atiq8961 on 04-Mar-10 09:43 AM

That is simply a macro security issue.
In Excel 2003 go to Tools, Macro, Security..In advanced tab check on
Open Excel again and you should not get the warning message.
In Excel 2007
1. click the round Office button then the button excel Options at the
bottom. Click on "Show developer tab in the ribbon"
2. Within Developer Tab click on Macro Security.
3. Within Macro Settings check on "Trust access to the VBA project
object model"
4. You could also check on "Trust all macros.." but it might be safer
to go to Trusted Locations and tell excel that your PC is trusted.

atiq8961 replied to Paul Robinson on 05-Mar-10 03:58 AM
Dear Paul sir,
I am using excel2003, as per your instructions, Tools>Macro>Security>In
advance tab, and I select "Trust Access to Visual Basic Project" and saved my
file and closed excel. When I reopen my file agian security warning appears
and asked for enable/disable macro.
My security level set on medium.
Thanks for co-operation.
Gord Dibben replied to atiq8961 on 05-Mar-10 04:18 PM
If just for your own computer, create a digital certificate using the Office
SelfCert tool.

Sign the workbook/project using that DC

See more on this in 2003 help on "digital certificates"

Gord Dibben  MS Excel MVP
atiq8961 replied to Gord Dibben on 06-Mar-10 02:40 AM
Dear Gord Dibben Sir / Paul Robinsons Sir,
Thanks for your help,
Have a nice and happy weekend.
Gord Dibben replied to atiq8961 on 06-Mar-10 04:31 PM
Thanks for the feedback on whatever it was we did.