Formatting a bookmark value

SMI// · November 9th, 2017, 04:04 AM

Friends,

I am using a simple format expression but getting an error. I am copying both my code and the error I am getting is against format expression used:

UpdateBookmark "BLGrossMtons", Format(SOD.Range("rngBLGrossMtons").value, "#,###0.000")

I have copied whole code just in case if you want to know what is being done as a whole but the error is appearing against above line of code only. Note that without using the format function, my code works just fine.

Code:

Public WordApp As Object
Public WordDoc As Object
Public strBMName As String
Public objBMRange As Object

Sub ICallBookMark()
Dim SOD  As Worksheet
Set SOD = ActiveWorkbook.Worksheets(1)
SOD.Activate

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show

If intChoice <> 0 Then
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
End If

Set WordApp = CreateObject("word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open(strPath, ReadOnly = False)


'On Error Resume Next


UpdateBookmark "BLGrossMtons", Format(SOD.Range("rngBLGrossMtons").value, "#,###0.000")
UpdateBookmark "BLGrossLtons", SOD.Range("rngBLGrossLtons").value
UpdateBookmark "BLGrossBbls", SOD.Range("rngBLGrossBbls").value
UpdateBookmark "Ship_tanks", SOD.Range("Ship_tanks").value



End Sub


Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
  
        If WordDoc.Bookmarks.Exists(BookmarkToUpdate) Then
            strBMName = BookmarkToUpdate
            Set objBMRange = WordDoc.Bookmarks(strBMName).Range
            objBMRange.Text = TextToUse
            WordDoc.Bookmarks.Add Name:=strBMName, Range:=objBMRange
            Set objBMRange = Nothing
            
            Else
            
            MsgBox "Missing data"
            
        End If
 
 End Sub

I get this error:

Quote:

Method 'Range' of object'_Worksheet' failed