Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
I've created a function that returns an array of numbers which I'd like to
be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
I think you would need to format the range in the correct format. A
worksheet function cannot make any formatting changes. -- Vasant "Asif" wrote in message ... I've created a function that returns an array of numbers which I'd like to be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
Well Vasant-ji, thanks for your reply. But I think a function can directly
return numbers in "##,##0" format when it returns a single value. Function NumberFormatting(theNumber) theNumber=10000.01 NumberFormatting=Format(theNumber,"##,##0") End Function I'd like to do something similar with my function that returns an array. Thanks ~Asif Vasant Nanavati wrote in message ... I think you would need to format the range in the correct format. A worksheet function cannot make any formatting changes. -- Vasant "Asif" wrote in message ... I've created a function that returns an array of numbers which I'd like to be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
A simple sample:
Function testIt() Dim arr, i As Long, Elem as variant arr = Array(1000.01, 10000.01, 100000.01) i = 1 For Each Elem In arr arr(i) = Format(Elem, "##,##0") i = i + 1 Next testIt = arr End Function Array enter =testIt() into a range of cells of the appropriate size and shape to accommodate the output. Alan Beban Asif wrote: Well Vasant-ji, thanks for your reply. But I think a function can directly return numbers in "##,##0" format when it returns a single value. Function NumberFormatting(theNumber) theNumber=10000.01 NumberFormatting=Format(theNumber,"##,##0") End Function I'd like to do something similar with my function that returns an array. Thanks ~Asif Vasant Nanavati wrote in message ... I think you would need to format the range in the correct format. A worksheet function cannot make any formatting changes. -- Vasant "Asif" wrote in message ... I've created a function that returns an array of numbers which I'd like to be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
Sorry, I misunderstood your question. Of course you can use the format
function to format the number, but what will be returned is a string, not a number. If that is acceptable, Alan's solution will do what you need. -- Vasant "Asif" wrote in message ... Well Vasant-ji, thanks for your reply. But I think a function can directly return numbers in "##,##0" format when it returns a single value. Function NumberFormatting(theNumber) theNumber=10000.01 NumberFormatting=Format(theNumber,"##,##0") End Function I'd like to do something similar with my function that returns an array. Thanks ~Asif Vasant Nanavati wrote in message ... I think you would need to format the range in the correct format. A worksheet function cannot make any formatting changes. -- Vasant "Asif" wrote in message ... I've created a function that returns an array of numbers which I'd like to be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
Vasant Nanavati wrote:
Sorry, I misunderstood your question. Of course you can use the format function to format the number, but what will be returned is a string, not a number. If that is acceptable, Alan's solution will do what you need. And if it's not, then I believe calling it with =--testIt() will. Alan Beban |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
=--testit()
removes the formatting produced by the function. -- Regards, Tom Ogilvy Alan Beban wrote in message ... Vasant Nanavati wrote: Sorry, I misunderstood your question. Of course you can use the format function to format the number, but what will be returned is a string, not a number. If that is acceptable, Alan's solution will do what you need. And if it's not, then I believe calling it with =--testIt() will. Alan Beban |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
Unless you have option Base 1,
arr is zero based. Function testIt() Dim arr, i As Long, Elem as variant arr = Array(1000.01, 10000.01, 100000.01) i = lbound(arr) For Each Elem In arr arr(i) = Format(Elem, "##,##0") i = i + 1 Next testIt = arr End Function Might be a little more robust. -- Regards, Tom Ogilvy Alan Beban wrote in message ... A simple sample: Function testIt() Dim arr, i As Long, Elem as variant arr = Array(1000.01, 10000.01, 100000.01) i = 1 For Each Elem In arr arr(i) = Format(Elem, "##,##0") i = i + 1 Next testIt = arr End Function Array enter =testIt() into a range of cells of the appropriate size and shape to accommodate the output. Alan Beban Asif wrote: Well Vasant-ji, thanks for your reply. But I think a function can directly return numbers in "##,##0" format when it returns a single value. Function NumberFormatting(theNumber) theNumber=10000.01 NumberFormatting=Format(theNumber,"##,##0") End Function I'd like to do something similar with my function that returns an array. Thanks ~Asif Vasant Nanavati wrote in message ... I think you would need to format the range in the correct format. A worksheet function cannot make any formatting changes. -- Vasant "Asif" wrote in message ... I've created a function that returns an array of numbers which I'd like to be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
Indeed it does. The cells I used in testing it happened to be formatted
to produce the right result. Sloppy. Thanks, Alan Beban Tom Ogilvy wrote: =--testit() removes the formatting produced by the function. -- Regards, Tom Ogilvy Alan Beban wrote in message ... Vasant Nanavati wrote: Sorry, I misunderstood your question. Of course you can use the format function to format the number, but what will be returned is a string, not a number. If that is acceptable, Alan's solution will do what you need. And if it's not, then I believe calling it with =--testIt() will. Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preformatted array returned by custom function
Tom Ogilvy wrote:
Unless you have option Base 1, arr is zero based. I did, and the loop assumes a 1-based array. To avoid the assumption, the array needs to be loaded more rigorously. Alan Beban Function testIt() Dim arr, i As Long, Elem as variant arr = Array(1000.01, 10000.01, 100000.01) i = lbound(arr) For Each Elem In arr arr(i) = Format(Elem, "##,##0") i = i + 1 Next testIt = arr End Function Might be a little more robust. -- Regards, Tom Ogilvy Alan Beban wrote in message ... A simple sample: Function testIt() Dim arr, i As Long, Elem as variant arr = Array(1000.01, 10000.01, 100000.01) i = 1 For Each Elem In arr arr(i) = Format(Elem, "##,##0") i = i + 1 Next testIt = arr End Function Array enter =testIt() into a range of cells of the appropriate size and shape to accommodate the output. Alan Beban Asif wrote: Well Vasant-ji, thanks for your reply. But I think a function can directly return numbers in "##,##0" format when it returns a single value. Function NumberFormatting(theNumber) theNumber=10000.01 NumberFormatting=Format(theNumber,"##,##0") End Function I'd like to do something similar with my function that returns an array. Thanks ~Asif Vasant Nanavati wrote in message ... I think you would need to format the range in the correct format. A worksheet function cannot make any formatting changes. -- Vasant "Asif" wrote in message .. . I've created a function that returns an array of numbers which I'd like to be shown in "##,##0" format when the array is printed to the worksheet. Looks like I can't do this. Can I? Thanks ~Asif |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to open worksheet in preformatted style | Setting up and Configuration of Excel | |||
Getting a list of returned values from an array | Excel Worksheet Functions | |||
array results not returned | Excel Worksheet Functions | |||
Excel SQL.Request restricts data returned to array | Excel Worksheet Functions | |||
returning an array from a custom function | Excel Programming |