Rounding problem (controlling word from excel)

[robert burger]

Good morning,

having a problem with rounding errors when using Walkenbach Sub
MakeMemos()
how do i stop word from rounding the number from excel?
for example:
.TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab &
vbTab & vbTab & vbTab & vbTab & vbTab & Format(Fee_deducted, "$##.##0")

number shows as 1865.000 when it should be 1864.96

Thanks,
For your help
RTB

 

[Tom Ogilvy]

I suspect you have Fee_deducted declared as long or integer. So it is being
rounded upon assignment. Declare it as single or double (or currency).

the format function isn't the problem:

fee_deducted = 1864.96
? Format(Fee_deducted, "$##.##0")
$1864.960

 

[robert burger]

Tom,
bear w/ me.
here's part of my code. Somehow i don't think its right. If i declare
it as a currency then why am i giving the format as "$##.##0"

Dim fee_deducted As Currency

' assign current data to variables
fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.##0")

' send commands to word
.TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab &
vbTab & vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0")

 

[Tom Ogilvy]

there is no need to introduce a format statement to assign the value to
fee_deducted. Currency is a number type - it doesn't require a $ or
anything like that

Sub BBB()
Dim fee_deducted As Currency

ActiveCell.Value = 1987.32
' assign current data to variables
fee_deducted = ActiveCell.Value

' send commands to word
Debug.Print "Fee to be Deducted from Account:" & vbTab & vbTab & vbTab &
vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0")
End Sub

produced:
Fee to be Deducted from Account: $1987.320

 

[robert burger]

Tom,
i guess what you mean by a fomat statement is:
' assign current data to variables
fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.##0")

You have me confused. I thought in order to have the number picked from
excel and placed in word i need to assign current data to veriables?

Anyways here is all my code so that you uderstand what i'm doing.

Sub Makefeestatement()
' creates statments in word using automation
Dim WordApp As Object


' start word and create an object
Set WordApp = CreateObject("Word.Application")

' info from worskheet
Set data = Sheets("sheet1").Range("a1")
Message = Sheets("sheet1").Range("Message")

' cycle through all records in sheet1
Records = Application.CountA(Sheets("sheet1").Range("a:a"))
For i = 1 To Records

' update status bar progress message
Application.StatusBar = "processing record " & i

' assign current data to variables
Port_account = data.Offset(i - 1, 0).Value
For_Qtr_End = data.Offset(i - 1, 1).Value
Port_name = data.Offset(i - 1, 2).Value
Report_date = data.Offset(i - 1, 3).Value
Avg_mnth_end_val = Format(data.Offset(i - 1, 4).Value,
"##,000")
Invst_mnge_fee = Format(data.Offset(i - 1, 5).Value, "##,000")
GST = Format(data.Offset(i - 1, 6).Value, "#,000")
fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.000")

' determine the file name
SaveAsName = ThisWorkbook.Path & "\" & Port_account & ".doc"

' send commands to word
With WordApp
.Documents.Add
With .Selection
.typeparagraph
.paragraphformat.Alignment = wdAlignParagraphJustify
.InlineShapes.AddPicture Filename:="C:\Documents and
Settings\rburger\Verity\verity logo\Logos + Branding
Info\fee_logo_testing_color.jpg", _
LinkToFile:=False, SaveWithDocument:=True
.Font.Size = 14
.Font.Bold = True
.paragraphformat.Alignment = wdAlignParagraphJustify
.TypeText Text:=vbTab & "Quarterly Statement of Fees"
.typeparagraph
.typeparagraph
.typeparagraph
.typeparagraph
.Font.Size = 11
.paragraphformat.Alignment = 0
.Font.Bold = False
.paragraphformat.Alignment = wdAlignParagraphRight
.TypeText Text:="Report Date:" & vbTab & vbTab & vbTab &
vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab & vbTab &
Format(Date, "mmmm d, yyyy")
.typeparagraph
.typeparagraph
.TypeText Text:="For the Quarter Ending: " & vbTab &
vbTab & vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab & For_Qtr_End
.typeparagraph
.typeparagraph
.TypeText Text:="Portfolio Name:" & vbTab & vbTab &
vbTab & vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab & vbTab & Port_name
.typeparagraph
.typeparagraph
.TypeText Text:="Portfolio Account:" & vbTab & vbTab &
vbTab & vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab & Port_account
.typeparagraph
.typeparagraph
.TypeText Text:="Average Month End Portfolio Value:" &
vbTab & vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab &
Format(Avg_mnth_end_val, "$#,##.00")
.typeparagraph
.typeparagraph
.TypeText Text:="Investment Management & Custody Fee:" &
vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab &
Format(Invst_mnge_fee, "$#,#.00")
.typeparagraph
.typeparagraph
.TypeText Text:="G.S.T at 7% (Reg:# unknown):" & vbTab &
vbTab & vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab & Format(GST,
"$#,#.00")
.typeparagraph
.typeparagraph
.Font.Underline = wdUnderlineSingle
.TypeText Text:="Fee to be Deducted from Account:" &
vbTab & vbTab & vbTab _
& vbTab & vbTab & vbTab & vbTab &
Format(fee_deducted, "$##.##0")
.typeparagraph
.typeparagraph
.typeparagraph
.TypeText Message
.typeparagraph
.typeparagraph
End With
.ActiveDocument.SaveAs Filename:=SaveAsName
' .ActiveWindow.Close

End With
Next i
' kill the object
WordApp.Quit
Set WordApp = Nothing

' reset status bar
Application.StatusBar = ""
MsgBox Records & " fees were created and saved in " &
ThisWorkbook.Path
End Sub

 

[Tom Ogilvy]

I changed
fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.000")

to

fee_deducted = Format(Data.Offset(i - 1, 7).Value, "$##.000")

and it worked fine for me.

 

[robert burger]

Tom,
thanks again for your help.
i realized my mistake was not matching the assign current data to
variables to the send commands to word ie "$#,#,00" vs "$#,#.00" (the
comma was wrong)


Cheers,
Rob