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

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

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

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

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

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

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

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

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

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