Also why is there a "$" character in constant "sCurrencyFormat$"?

The Option Explicit statement enforces variable declaration and VBA 'best
practice' is to turn this on in the 'General' tab of the VBE Preferences
dialog.

The NumberFormat definitions are passed as String values. The $ character is
the Type symbol for 'String'. I have Lou Gehrig's and so I like to save typing
when possible; -using the VB type symbols helps with that!

The alternate syntax is below the line (as a comment)...

*****
Option Explicit

Const sCurrencyFormat$ = "$#,##0.00_);($#,##0.00)"
' Const sCurrencyFormat As String = "$#,##0.00_);($#,##0.00)"

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells

With crng
'If cell number format is Currency AND cell not empty
If .NumberFormat = sCurrencyFormat And Len(crng) 0 Then _
crng.Value = WorksheetFunction.Round(crng * (1 + rng), 2)
End With 'crng

Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub
*****

Thanks.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion