changing decimal format

vipulgos

vipulgos

Active Member
Joined
Aug 17, 2002
Messages
335
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
The problem is that the NumberFormat method is for use with a Range, not a value, and a Function cannot manipulate a worksheet range to change the NumberFormat.

I don't have a solution right now, but I'll post back if I can think of something.
 
Upvote 0
I want to use this code as addin. So, that it can be used like this:
=comma(a1)
somthing very little wrong is there..
 
Upvote 0
any body please comment on first part as :

Function comma(MyNumber)
Application.ScreenUpdating = False
If MyNumber = 0 Then Exit Function
Dim result As String

and last part as:
Comma = result.

I think the mistake is in these part only.
 
Upvote 0
Hi Krishnakumar,
Being an Indian you must have function like this:
=comma(A1)
should write the comma as expected in Lacks, Crores, Thousands, etc. I've gone throgh your link, but it seems limited use. If this data can be converted and saved as add-in, it will serve the purpose/...
 
Upvote 0
I've made some changes, but result is in TRUE and FALSE only

Code: 
Option Explicit

Function comma(MyNumber)
Application.ScreenUpdating = False
If MyNumber > 9999999999999# Then
MyNumber = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""##"",""##"",""##"",""###"
ElseIf MyNumber > 999999999999999# Then '99,99,99,999
MyNumber = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""##"",""##"",""###"
ElseIf MyNumber > 9999999999999# Then '99,99,99,999
MyNumber = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""##"",""###"
ElseIf MyNumber > 99999999999# Then '99,99,99,999
MyNumber = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""##"",""###"
ElseIf MyNumber > 999999999# Then '99,99,99,999
MyNumber = MyNumber.NumberFormat = "##"",""##"",""##"",""##"",""###"
ElseIf MyNumber > 9999999# Then
MyNumber = MyNumber.NumberFormat = "##"",""##"",""##"",""###"
ElseIf MyNumber > 99999# Then
MyNumber = MyNumber.NumberFormat = "##"",""##"",""###"
ElseIf MyNumber > 999# Then
MyNumber = MyNumber.NumberFormat = "##"",""###"
End If
Application.ScreenUpdating = True
comma = MyNumber
End Function
 
Upvote 0
Hi Vipul,

Why should you need a function? I think parry's contribution is enough. Still, if you want a formula based solution then use Yogi's.

Kris
 
Upvote 0
Hi Vipul:

Sorry, I did not have a chance to debug your code, however, based on my formula based solution, I have written the following UDF ...
Rich (BB code): 
Public Function yIndNumF(amount)
'***********************************************************************
'* This Function displays numbers in Indian System of Representation
'* in Thousands, Laks, Crores, Arabs, ...
'* upto 15 digits and two decimal places (these can be easily changed)
'* Please pass on comments or enhancements to the writer
'* Written Nov-22-2004 by: Yogi Anand -- ANAND Enterprises, Rochester Hills MI 48309
'* www.anandent.com  www.oakland.edu~anand
'************************************************************************
'
    yIndNumF = WorksheetFunction.Replace(WorksheetFunction.Text _
       (amount, "##"",""##"",""##"",""##"",""##"",""##"",""000.##"), 1, 6 - Int _
       ((Len(Int(amount)) > 2) * -1 + (Len(Int(amount)) - 4) / 2), "")
'
End Function
Following is an illustration of the use of the UDF ...
y041121h1.xls
ABCD
2
30.450.45
41.451.45
511.4511.45
6111.45111.45
71111.451,111.45
81111111111111.4511,11,11,11,11,111.45
911111111111111.401,11,11,11,11,11,111.4
10
Sheet14


Would this work for you?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

kelly mort
  • Solved
VBA AdvancedFiltering faling to work after changing the format of cells.
Replies
3
Views
459
kelly mort
kelly mort
J
  • Question
Cannot change number of decimal places
Replies
1
Views
600
jmacleary
jmacleary
C
  • Question
Change format of result cell from Vlookup
Replies
2
Views
2K
EveMLehar
E
C
  • Question
VBA to switch Currency Formats (USD, GBP, EUR, etc)
Replies
5
Views
17K
Rhodie72
Rhodie72
S
  • Question
VBA Validated userforms
Replies
2
Views
2K
Sovereignty9
S

Forum statistics

Threads
1,214,970
Messages
6,122,514
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top