Problems with MS Word DocVariables defined in VBA

Question

Background: a proprietary piece of veterinary software generates a document pre-populated with merge fields containing data for a particular patient. The field I am interested in is weight but its a string (Top_Stat) that looks like this "24.5 kg".

I have created a script to read that field and convert it into an integer. However I now want to use this integer to male medication dose calculations based on the animal weight. As create document variables for this but the variable (name and value) gets stored in the document. I want at least the value to be removed but can't seem to get the result with the following script.

Sub GetWeight()
 ActiveDocument.Variables("WeightInKg").Delete
 WeightInt = ActiveDocument.MailMerge.DataSource.DataFields("Top_Stat").Value

 WeightInt = Replace(WeightInt, " kg", "") 'This removes the superfluous text
 WeightInt = Val(WeightInt) 'This converts the weight into a number (integer)
 
 ActiveDocument.Variables.Add Name:="WeightInKg", Value:=WeightInt 'Add the Word variable
 ActiveDocument.Fields.Update
 
End Sub

What am I missing? Apologies, I am new to VBA.

Answer 1

Your code needs some error checking. This first time it is run the document variable "WeightInKg" does not exist and when you go to delete it, the routine errors out.

Document variables, not to be confused with VBA Subroutine variables are not Word document fields so unless you have another reason for updating all fields, that code line is unnecessary.

Finally, you should always declare your VBA Subroutine variables.

I have modified your code but could not fully test it because I don't have your mail merge data source ... but give it a try and see if it now works for you.

Sub AutoOpen()
    Call GetWeight
End Sub


Sub GetWeight()
    Dim WeightIn As Long
    On Error Resume Next
    ActiveDocument.Variables("WeightInKg").Delete
    On Error GoTo ErrHandler
    WeightInt = ActiveDocument.MailMerge.DataSource.DataFields("Top_Stat").Value
    
    WeightInt = Replace(WeightInt, " kg", "") 'This removes the superfluous text
    WeightInt = Val(WeightInt) 'This converts the weight into a number (integer)
    
    ActiveDocument.Variables.Add Name:="WeightInKg", Value:=WeightInt 'Add the Word variable
'    ActiveDocument.Fields.Update
ErrHandler:
    If Err.Number > 0 Then
        MsgBox Err.Number & vbCr & Err.Description, vbCritical
        Err.Clear
    End If
End Sub

This is the screenshot of the Word document I am trying to populate. Screenshot