Excel VBA Run Time Error '424' object required

Question

I am totally new in VBA and coding in general, am trying to get data from cells from the same workbook (get framework path ...) and then to start application (QTP) and run tests.

I am getting this error when trying to get values entered in excel cells:

Run Time Error '424' object required

I believe I am missing some basic rules but I appreciate your help. Please see below the part of code in question:

Option Explicit

Private Sub RunTest_Click()

    Dim envFrmwrkPath As Range
    Dim ApplicationName As Range
    Dim TestIterationName As Range
    'Dim wb As Workbook
    'Dim Batch1 As Worksheets
    Dim objEnvVarXML, objfso, app As Object
    Dim i, Msgarea

    Set envFrmwrkPath = ActiveSheet.Range("D6").Value ' error displayed here
    Set ApplicationName = ActiveSheet.Range("D4").Value
    Set TestIterationName = ActiveSheet.Range("D8").Value

C. White · Accepted Answer · 2016-05-17 00:12:00Z

29

The first code line, Option Explicit means (in simple terms) that all of your variables have to be explicitly declared by Dim statements. They can be any type, including object, integer, string, or even a variant.

This line: Dim envFrmwrkPath As Range is declaring the variable envFrmwrkPath of type Range. This means that you can only set it to a range.

This line: Set envFrmwrkPath = ActiveSheet.Range("D6").Value is attempting to set the Range type variable to a specific Value that is in cell D6. This could be a integer or a string for example (depends on what you have in that cell) but it's not a range.

I'm assuming you want the value stored in a variable. Try something like this:

Dim MyVariableName As Integer
MyVariableName = ActiveSheet.Range("D6").Value

This assumes you have a number (like 5) in cell D6. Now your variable will have the value.

For simplicity sake of learning, you can remove or comment out the Option Explicit line and VBA will try to determine the type of variables at run time.

Try this to get through this part of your code

Dim envFrmwrkPath As String
Dim ApplicationName As String
Dim TestIterationName As String

edited May 17, 2016 at 0:12

C. White

8001 gold badge7 silver badges20 bronze badges

answered Jan 26, 2014 at 2:02

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3232996 Over a year ago

Really thank you for your quick reply, i posted my question after trying all ways i found: Used/Unused Option Explicit ...tried other expressions; now by using your suggestion; am getting: Run Time Error '13' type mismatch ... well, envFrmwrkPath is a path to the QTP framework entered in the excel cell by the user; i want to get this path then get other reusable files/folders... i will add my excel my file if this could help...

Automate This Over a year ago

If the cell contains a "path" it will actually be treated like a String. Adding the file will be helpful. Just edit your original question and include a link to your file.

user3232996 Over a year ago

yes i tried with String and was ok, getting other errors dealing with QTP now. Thank you again for your help

TylerH · Accepted Answer · 2018-06-29 04:01:27Z

3

Simply remove the .value from your code.

Set envFrmwrkPath = ActiveSheet.Range("D6").Value

instead of this, use:

Set envFrmwrkPath = ActiveSheet.Range("D6")

edited Jun 29, 2018 at 4:01

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Oct 17, 2017 at 3:19

Sandeep Bhatt

5752 gold badges8 silver badges18 bronze badges

Comments

JPR · Accepted Answer · 2019-08-30 14:23:33Z

0

You have two options,

-If you want the value:

Dim MyValue as Variant ' or string/date/long/...
MyValue = ThisWorkbook.Sheets(1).Range("A1").Value

-if you want the cell object:

Dim oCell as Range  ' or object (but then you'll miss out on intellisense), and both can also contain more than one cell.
Set oCell = ThisWorkbook.Sheets(1).Range("A1")

answered Aug 30, 2019 at 14:23

JPR

6526 silver badges12 bronze badges

Comments

user3736273 · Accepted Answer · 2014-06-13 03:29:37Z

Private Sub CommandButton1_Click()

    Workbooks("Textfile_Receiving").Sheets("menu").Range("g1").Value = PROV.Text
    Workbooks("Textfile_Receiving").Sheets("menu").Range("g2").Value = MUN.Text
    Workbooks("Textfile_Receiving").Sheets("menu").Range("g3").Value = CAT.Text
    Workbooks("Textfile_Receiving").Sheets("menu").Range("g4").Value = Label5.Caption

    Me.Hide

    Run "filename"

End Sub

Private Sub MUN_Change()
    Dim r As Integer
    r = 2

    While Range("m" & CStr(r)).Value <> ""
        If Range("m" & CStr(r)).Value = MUN.Text Then
        Label5.Caption = Range("n" & CStr(r)).Value
        End If
        r = r + 1
    Wend

End Sub

Private Sub PROV_Change()
    If PROV.Text = "LAGUNA" Then
        MUN.Text = ""
        MUN.RowSource = "Menu!M26:M56"
    ElseIf PROV.Text = "CAVITE" Then
        MUN.Text = ""
        MUN.RowSource = "Menu!M2:M25"
    ElseIf PROV.Text = "QUEZON" Then
        MUN.Text = ""
        MUN.RowSource = "Menu!M57:M97"
    End If
End Sub

Collectives™ on Stack Overflow

Excel VBA Run Time Error '424' object required

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related