CODEWITHSUNDEEP
vbaexcel
AMorton1989
AMorton1989

Reputation: 313

VBA - Automation Error - Excel 2013

I have the following (previously working) code which converts formulas to their constant values. I attached it to a form button on the spreadsheet. It was working fine, and then decided to break. I click on the button or run the Macro and a box comes up with "Automation error". Excel help describes it as a "Automation error (Error 440)".
I have followed the advice of forums, inserting "On Error Resume Next" statement in various places. I have tried recreating the Macro. I have tried some different versions of the Macro. But have not managed to fix it yet. How do I get rid of this error? Is it something similar to a try catch statement in Java and Python?

Sub RangeOfFormulasToConstants()
'
' Changes a selection of Formulas into their values thus removing the formula.
'
' Keyboard Shortcut: Ctrl+q

Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Useful box"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each Rng In WorkRng
    Rng.Value = Rng.Text
Next
Application.ScreenUpdating = True

End Sub

I have other Macros which are currently working with the spreadsheet and form buttons. Detailed below. 

Sub CopyFormulasNonCalculate()

'
' PasteMacro Macro
'
' Keyboard Shortcut: Ctrl+m
'
    On Error Resume Next
    Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
    On Error GoTo 0
    If Not Ret Is Nothing Then
        Selection.Copy

        Range("C2:F2").Copy Destination:=Ret



            Application.CutCopyMode = False
    End If
End Sub

Sub CopyFormulasCalculate()
Dim Ret As Range
Dim RangeToCopy As Range

Set RangeToCopy = Range("H3:AF3")
'
' PasteMacro Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Set Ret = Application.InputBox(Prompt:="Please select a range where you want to paste", Type:=8)
    If Not Ret Is Nothing Then
        RangeToCopy.Copy Destination:=Ret
        Ret.Resize(1, RangeToCopy.Columns.Count).Calculate

            Application.CutCopyMode = False
    End If
End Sub

Upvotes: 2

Views: 1236

Answers (1)

user4039065
user4039065

Reputation:

You should not be using Rng.Value = Rng.Text without knowing what you are transcribing back to the cell. This attempts to take the displayed value of the cell subject to formatting and return it to the cell as a value. Essentially, you are getting what the cell looks like which is not necessarily the value.

Use Rng = Rng.Value or Rng = Rng.Value2 instead. This takes the underlying value that the formula has returned and sets the cell's value to it.

Sub RangeOfFormulasToConstants()
    ' Changes a selection of Formulas into their values thus removing the formula.
    ' Keyboard Shortcut: Ctrl+q

    Dim WorkRng As Range, xTitleId As String

    Application.ScreenUpdating = False
    xTitleId = "Useful box"

    On Error GoTo bm_Exit
    Set WorkRng = Application.InputBox("Range: ", xTitleId, Selection.Address, Type:=8)
    WorkRng = WorkRng.Value  'do it all at once - no need to loop through

bm_Exit:
    Application.ScreenUpdating = True
End Sub

Rather than stumbling through errors for every inappropriate code line, this traps the error and shuffles code execution off to the bm_Exit bookmark where the environment is restored before exiting.

Upvotes: 1

Related Questions