Why do I keep getting a "object 'Range' failed" error my code runs but not when I attach this code to a button?

Question

I'm working on a calculator that is working in psi and bar. On my sheet I have an area where the user can input their values and right next to those inputs it gives a secondary input in the opposite unit. Originally I was solving the secondary input using a formula in my sheet but when I added my macro button that let the user flip the initial input values with the secondary inputs, my formula disappeared. I tried creating a private sub to accomplish the same thing but I kept getting a "Ranged Failed" error.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim unit As Range
    Dim ip As Range
    Dim ad As Range
    Dim op As Range
    Dim unit2 As Range
    Dim ip2 As Range
    Dim ad2 As Range
    Dim op2 As Range
    Dim barcon As Integer

        barcon = 14.5038
        Set unit = ActiveSheet.Range("B3")
        Set ip = ActiveSheet.Range("B4")
        Set ad = ActiveSheet.Range("B5")
        Set op = ActiveSheet.Range("B6")
        Set unit2 = ActiveSheet.Range("D3")
        Set ip2 = ActiveSheet.Range("D4")
        Set ad2 = ActiveSheet.Range("D5")
        Set op2 = ActiveSheet.Range("D6")

        If unit = "PSI" Then
            unit2 = "Bar"
            ip2 = ip / barcon
            ad2 = ad / barcon
            op2 = op / barcon
        Else
            unit2 = "PSI"
            ip2 = ip * barcon
            ad2 = ip * barcon
            op2 = op * barcon
        End If

End Sub

I have tried to use public sub but I kept getting the same error. I did noticed if I attached this to a button, the code worked fine. Is this because I am using the same variables between the two macros? Here is the macro I'm using to fil my inputs.

Sub Convert()
    Dim ip As Range, ad As Range, op As Range
    Dim ip2 As Range, ad2 As Range, op2 As Range
    Dim tran1 As Integer, tran2 As Integer, tran3 As Integer
    Dim unit As Range, unit2 As Range

        Set unit = ActiveSheet.Range("B3")
        Set ip = ActiveSheet.Range("B4")
        Set ad = ActiveSheet.Range("B5")
        Set op = ActiveSheet.Range("B6")
        Set unit2 = ActiveSheet.Range("D3")
        Set ip2 = ActiveSheet.Range("D4")
        Set ad2 = ActiveSheet.Range("D5")
        Set op2 = ActiveSheet.Range("D6")

        tran1 = ip
        tran2 = ad
        tran3 = op

            If unit = "PSI" Then
            Application.EnableEvents = False
                unit = "Bar"
                unit2 = "PSI"
                ip = ip2
                ad = ad2
                op = op2
                ip2 = tran1
                ad2 = tran2
                op2 = tran3
            Else
            Application.EnableEvents = False
                unit = "PSI"
                unit2 = "Bar"
                ip = ip2
                ad = ad2
                op = op2
                ip2 = tran1
                ad2 = tran2
                op2 = tran3
            End If
            Application.EnableEvents = True

        ip.NumberFormat = "0"
        ad.NumberFormat = "0"
        op.NumberFormat = "0"
        ip2.NumberFormat = "0"
        ad2.NumberFormat = "0"
        op2.NumberFormat = "0"

End Sub

this won't fix your problem but just so you know if you declare a variable as `integer` and then assign a decimal to it, it will round your number. so right now, barcon is basically 15. If the decimal actually matters, you should declare the variable as a `double` instead — Hayden Moss, Jul 31 '20 at 18:28
You are getting that error because you are trying to change a range in `Worksheet_Change` without switching off the events. You may want to read up on https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640 — Siddharth Rout, Jul 31 '20 at 18:31

Warcupine · Accepted Answer · 2020-07-31T18:39:21.673

First, variables are scoped to the Sub so you can name them the same.

Your issue is that you are in a worksheet_change event, so when the code modifies the worksheet it refires the code, looping until it freaks out and crashes. Use Application.EnableEvents = False

I added in error handling so it turns events back on in case of a crash, you may want to make it more robust by handling specific errors (like entering a non number).

Don't use integer use long to store numbers, though in this case you actually want double to handle the decimals.

You should probably also use target.offset and range.intersect but I'm not sure the full scope of what you have planned so I left them out.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo safeexit
    Dim unit As Range
    Dim ip As Range
    Dim ad As Range
    Dim op As Range
    Dim unit2 As Range
    Dim ip2 As Range
    Dim ad2 As Range
    Dim op2 As Range
    Dim barcon As Double

        barcon = 14.5038
        Set unit = ActiveSheet.Range("B3")
        Set ip = ActiveSheet.Range("B4")
        Set ad = ActiveSheet.Range("B5")
        Set op = ActiveSheet.Range("B6")
        Set unit2 = ActiveSheet.Range("D3")
        Set ip2 = ActiveSheet.Range("D4")
        Set ad2 = ActiveSheet.Range("D5")
        Set op2 = ActiveSheet.Range("D6")

        If unit = "PSI" Then
            unit2 = "Bar"
            ip2 = ip / barcon
            ad2 = ad / barcon
            op2 = op / barcon
        Else
            unit2 = "PSI"
            ip2 = ip * barcon
            ad2 = ip * barcon
            op2 = op * barcon
        End If
safexit:
    Application.EnableEvents = True
End Sub

Why do I keep getting a "object 'Range' failed" error my code runs but not when I attach this code to a button?

1 Answers1