VBA - Automation Error - Excel 2013

Question

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

Please consider restarting your machine. Repetitious crashes can destabilize a VBA environment, particularly so when On Error Resume Next is involved. — user4039065
– user4039065, Commented Jun 5, 2015 at 11:12

user4039065 · Accepted Answer · 2015-06-05 11:11:05Z

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.

I rebooted, amended the code and it worked fine. Thanks a bunch.

Collectives™ on Stack Overflow

VBA - Automation Error - Excel 2013

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related