Formatting currency in vba

[veeves]

I am building a spreadsheet in Access vba and storing numbers (currency) values in cells. When I open the spreadsheet after creation I see negative amounts are red. How can I make them display black?

 

[Phox]

Range("Your Range").Style = "Currency"

 

[phxsportz]

If the cells are in a range, just do the following..

RANGE(YourRangeHere).NumberFormat = "$#,##0.00_);($#,##0.00)"

 

[mikerickson]

Select the cells in question and open the Format Cells dialog box (how to do this depends on which version of Excel.) The Number tab will have lots of options including a black negative currency option.

 

[Norie]

How exactly are you 'building' the spreadsheet?

 

[veeves]

I am building my spreadsheet like this:

Dim xapp As Excel.Application
Dim wbook As Excel.Workbook
Dim WS As Excel.Worksheet
Dim intX As Integer
Dim strResults As String
 
'Start a new instance of Excel
Set xapp = New Excel.Application
 
'Create a new workbook
Set wbook = xapp.Workbooks.Add
Set WS = wbook.Worksheets.Add
WS.Name = "Direct"
 
Dim db As DAO.Database
Set db = CurrentDb()
Dim recSet As DAO.Recordset
Set recSet = db.OpenRecordset("Q_Direct_Job_Orders", dbOpenDynaset)
 
If recSet.EOF = True Then
        recSet.Close
        Exit Function
End If
 
Do While Not recSet.EOF
 
     wks.Range("J" & intEnd) = 
            Format(CDbl(recSet"Amount")), "$#,##0.00_);($#,##0.00)")
Loop
 
 recSet.Close
 
xapp.Visible = True
xapp.Quit
'Cleaning up
Set WS = Nothing
Set wbook = Nothing
Set xapp = Nothing

=======================

Adding Range("Your Range").Style = "Currency" works.

RANGE(YourRangeHere).NumberFormat = "$#,##0.00_);($#,##0.00)"

works also.

Is there a way the format function would do this all in one statement like:

wks.Range("J" & intEnd) = 
            Format(CDbl(recSet"Amount")), "$#,##0.00_);($#,##0.00)")

 

[Phox]

I don't think so. We're dealing with two issues. One is what the actual value will be, and the other is how Excel will display that value. When you set the value using Format(CDbl(recSet"Amount")), "$#,##0.00_);($#,##0.00)"), all the formatting is done in VBA prior to sending the value to Excel. Once Excel gets the value, it then makes it own display format of the value based on the properties of the Range.

 

[veeves]

Okay. Thanks a bunch for all the responses.

 

[Norie]

Why not format the field itself?

You could do that in the table/query design

 

[Raza5]

If the cells are in a range, just do the following..

RANGE(YourRangeHere).NumberFormat = "$#,##0.00_);($#,##0.00)"

Hi,

How can I use a VBA code to format a cell to thai currency and uae dirham. I used the custom format option for both but when I paste it to vba it puts a question mark instead?

This is what I get for

Thai : [$?-41E]#,##0.00
UAE dirham : [$?.?.?-3801] #,##0.00_-