I have generated VBA code to place a worksheet range in a comment. To format
the text for the comment I rely on application.worksheetfunction.len or max
referring to the named range(slow). I am trying to move the data into a array
and do the process differently with the same result. So far only one problem.
If the contents in one cell of named range ends on a even dollar then the
array only recognizes the dollar and not the 0s in the length of number.
?? Is there a way for the array to grab (new VBA term) the data from the
named range as it's formatted for display or rounded for display?

--
Jim at Eagle

On Apr 15, 12:56*pm, Jim at Eagle
<JimatEa...@discussions.microsoft.com> wrote:
> I have generated VBA code to place a worksheet range in a comment. To format
> the text for the comment I rely on application.worksheetfunction.len or max
> referring to the named range(slow). I am trying to move the data into a array
> and do the process differently with the same result. So far only one problem.
> If the contents in one cell of named range ends on a even dollar then the
> array only recognizes the dollar and not the 0s in the length of number.
> ?? Is there a way for the array to grab (new VBA term) the data from the
> named range as it's formatted for display or rounded for display?
>
> *--
> Jim at Eagle

Try posting your code if this doesn't help. I'm not sure how you're
putting it back into the comment from the array, but try using the
Format function to force the value into the format you're wanting.
For example:

Dim value as Double
value = 123

Range("A2").AddComment Format(value, "0.00")

Hope that helps,
Cory

Arrays hold data. They do not hold any formatting. If you want to output the
value of an array in a formatted manner then you can use the Format function
something like this...

Sub test()
Dim ary(3) As Double
Dim lng As Long

ary(0) = 1.23
ary(1) = 2
ary(2) = 4.567

For lng = 0 To 2
MsgBox Format(ary(lng), "#,##0.00")
Next lng
End Sub
--
HTH...

Jim Thomlinson


"Jim at Eagle" wrote:

> I have generated VBA code to place a worksheet range in a comment. To format
> the text for the comment I rely on application.worksheetfunction.len or max
> referring to the named range(slow). I am trying to move the data into a array
> and do the process differently with the same result. So far only one problem.
> If the contents in one cell of named range ends on a even dollar then the
> array only recognizes the dollar and not the 0s in the length of number.
> ?? Is there a way for the array to grab (new VBA term) the data from the
> named range as it's formatted for display or rounded for display?
>
> --
> Jim at Eagle

Hello,

I suggest to use something like:

Function grab(r As Range, _
Optional sdel As String = " ") As String
Dim v, s As String

For Each v In r
grab = grab & s & v.Text
s = sdel
Next v

End Function

Sub testme()
Range("D1").ClearComments
Range("D1").AddComment grab(Range("myrange"))
End Sub

Regards,
Bernd