changing decimal format

[vipulgos]

I'm trying to make add-in for change the decimal setting in indian currency formate, as the decimal format is strenge in Indian currency. It keeps on changing with length of number. So, i've tried like this but it seems not working. Number like 5555 should be written like 5,555, number lke 555555 should be written as 5,55,555 and so on....


Option Explicit

Function comma(MyNumber)
Application.ScreenUpdating = False
If MyNumber = 0 Then Exit Function
Dim result As String
If MyNumber > 9999999999999# Then
result = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""##"",""##"",""##"",""###"
End If
If MyNumber > 999999999999999# Then '99,99,99,999
result = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""##"",""##"",""###"
End If
If MyNumber > 9999999999999# Then '99,99,99,999
result = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""##"",""###"
End If
If MyNumber > 99999999999# Then '99,99,99,999
result = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""###"
End If
If MyNumber > 999999999 Then '99,99,99,999
result = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""###"
End If
If MyNumber > 9999999 Then
result = MyNumber.NumberFormat = "##"",""##"",""##"",""###"
End If
If MyNumber > 99999 Then
result = MyNumber.NumberFormat = "##"",""##"",""###"
End If
If MyNumber > 999 Then
result = MyNumber.NumberFormat = "##"",""###"
End If
Application.ScreenUpdating = True
comma = result
End Function

 

[Yogi Anand]

Thanks Yogi it works nicely. I've saved the workbook as add-in.
But one problem still there. When I uses this function in any book, it automatically generate link to yIndNumF. I want to remove this problem, as this link will definitly cause problem in saving the book. Why this UDF is not act as morefunction.xla, etc.
There should not be VBA project named as yIndNumF in left pane of VB window. How to make it please guide me.

Hi Vipul:

Couple of things ...

1) you saved the file with the UDF as an add-in ... that's Good!

2) Did you invoke the Add-in file via TOOLS|Add-Ins

3) if you did 2) successfully, your Add-in should be listed with a check mark in the dialogBox that pops up when you do TOOLS|Add-Ins

if your add-in is invoked, you can use the function yIndNumF in any worksheet of any workbook, because the function is being made available by the EXCEL application and not necessarily by any open workbook.

I hope this helps -- and my apologies if I have misunderstood your question.

 

[Yogi Anand]

Hi Vipul:

Here is Worksheet_Change Code ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, [B:B]) Is Nothing Then Exit Sub
If Len(Int(Target)) > 13 Then yF = "##"",""##"",""##"",""##"",""##"",""##"",""000.##": GoTo Finish
If Len(Int(Target)) > 11 Then yF = "##"",""##"",""##"",""##"",""##"",""000.##": GoTo Finish
If Len(Int(Target)) > 9 Then yF = "##"",""##"",""##"",""##"",""000.##": GoTo Finish
If Len(Int(Target)) > 7 Then yF = "##"",""##"",""##"",""000.##": GoTo Finish
If Len(Int(Target)) > 5 Then yF = "##"",""##"",""000.##": GoTo Finish
If Len(Int(Target)) > 3 Then yF = "##"",""000.##": GoTo Finish
Finish:
Target.NumberFormat = yF
End Sub

When a numeric entry is made in a cell of column B, the entry is CustomNumber Formatted according to Indian Style of Numbering.

 

[vipulgos]

Hi Yogi both works fine. But my problem about creating add-in is still there. Yogi this time i'm sending you a personnel message with attachment in word with screen shots. What i'm trying to mention regarding te link and about function that always seen in VB project. I want this function to be act as like morefunction.xla, etc. (There should not be any link to active workbook, while using UDF), i'm making little mistake, but can't find out. I've keep property as Add-in=True
I've also invoke the Add-in file via TOOLS>Add-Ins.
--regards

 

[Yogi Anand]

Hi Vipul:

Please verify that you have installed your Add-In correctly. You should be able to use the function contained in the Add-In without being forced to link to the base file containing the function.