Improper use of a Property

Question

The code is supposed to compare two excel files and then create a new excel file showing the differences.

On the first sheet of the file, I’ve created a button and then opened it opened and put the code in. However, when I run the code I get an error saying

improper use of a Property

and .Worksheets is marked. Or the problem can be something related to Private Sub CommandButton1_Click()

I have done some research online and was unsuccessful so I’m hoping to get a bit of help.

Option Explicit

Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim report As Workbook 
Dim ws1 As Worksheet, ws2 As Worksheet
Dim difference As Long
Dim row As Long, col As Integer
Set report = Workbooks.Add

Set ws1 = ThisWorkbook.Worksheets(1)
Set ws2 = ThisWorkbook.Worksheets(1)

With ThisWorkbook.Worksheets("Sheet1")
    ws1row = .Rows.Count
    ws1col = .Columns.Count
End With
With ThisWorkbook.Worksheets("Sheet2")
    ws2row = .Rows.Count
    ws2col = .Columns.Count
End With

maxrow = ws1row
maxcol = ws1col

If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col

difference = 0

For col = 1 To maxcol
    For row = 1 To maxrow
    colval1 = ""
    colval2 = ""
    colval1 = ws1.Cells(row, col).Formula
    colval2 = ws2.Cells(row, col).Formula

    If colval1 <> colval2 Then
    difference = difference + 1
    Cells(row, col).Formula = colval1 & "<> " & colval2
    Cells(row, col).Interior.Color = 255
    Cells(row, col).Font.ColorIndex = 2
    Cells(row, col).Font.Bold = True
    End If
Next row
Next col

Columns("A:B").ColumnWidth = 25
report.Saved = True

If difference = 0 Then
report.Close False
End If
Set report = Nothing

MsgBox difference & " cells contain different data! ", vbInformation, "Comparing Two Worksheets"

End Sub

`ActiveWorkbook.Worksheets ("Sheet1")` should be `With ActiveWorkbook.Worksheets("Sheet1")` — Tim Stack, Aug 13 '19 at 07:41
Also, use the correct quotation marks. `“”` should not be used, use `""` instead — Tim Stack, Aug 13 '19 at 07:42
I edited the code and used the correct quotation. Now I'm getting "index outside the valid range" — Miriam List, Aug 13 '19 at 07:56
Does the sheet named "Sheet1" exist in the workbook that is currently active? I would strongly advise you ***not*** to refer to active workbooks. Instead, declare a workbook or worksheet variable and set that with preferably the worksheet codename, and if not possible use the sheet's name or index. — Tim Stack, Aug 13 '19 at 07:59
when I first created the sheet is was called "Tabelle1" so I renamed them into Sheet1 without any spaces. I'm sorry but as it for now I'm a total beginner and not sure If I know how to do that — Miriam List, Aug 13 '19 at 08:06
Welcome to SO. You are adding a new workbook with line `Set report = Workbooks.Add` so that new Workbook will have default names in the sheets. Those default names depends on your language settings. Also, when creating a new workbook, **it becomes the active one** so actually when later on you do `ActiveWorkbook.Worksheets("Sheet1")` you are using the new workbook, which is a new one, so it's totally in blank. That may cause the fail If you are trying to activate a sheet in the workbook where you are executing this code, use `ThisWorkbook` instead of `ActiveWorkbook` — Foxfire And Burns And Burns, Aug 13 '19 at 08:16
Also, if you are newbie with VBA, I strongly suggest you to read [Difference between Thisworkbook.name and Activeworkbook.name in VBA](https://stackoverflow.com/questions/35426907/difference-between-thisworkbook-name-and-activeworkbook-name-in-vba) — Foxfire And Burns And Burns, Aug 13 '19 at 08:17
The read the link and now I understand the differences. I switched to using "ThisWorkbook" but now I get en Object required error at "colval1 = ws1.Cells(row, col).Formula". The code just doesn't whant to give me peace — Miriam List, Aug 13 '19 at 08:28
In the code you posted, you never declare `ws1` or `ws2`. First, define those variables (google about **Option Explicit** to prevent non declared variables). Second, you need to assign those variables to worksheets using **Set**. Check [Declare and Set Worksheet](http://codevba.com/excel/set_worksheet.htm#.XVKDn-Mzbcs) — Foxfire And Burns And Burns, Aug 13 '19 at 09:32
@Foxfire And Burns And Burns Can you help me declare ws1 for sheet1 and ws2 for sheet2 ? would really appreciate it — Miriam List, Aug 14 '19 at 11:48
@TimStack answer does it pretty good. To declare the variable, you need `Dim ws1 as Worksheet`. To assign it you need `Set ws1 = AnyWorkbook.Worksheets("nameofworksheet")` where `AnyWorkbook` is a specific workbook (could be `ThisWorkbook`, or `ActiveWorkbook` or `Report`, don't know what you need. You will have to figure out — Foxfire And Burns And Burns, Aug 14 '19 at 11:51
@Foxfire And Burns And Burns, I edited the code above. I think I declared everything right but when I run the code when the report while is creating the file is not responding. I just want to compare sheet1 & sheet2 from one excel file and then output the differences on a new excel file — Miriam List, Aug 14 '19 at 12:12
You've set both variables to refer to exact same worksheet.... how are you going to compare? — Tim Stack, Aug 14 '19 at 12:19
I changed Set ws2 = ThisWorkbook.Worksheets(1) to Set ws2 = ThisWorkbook.Worksheets(2). Same outcome the render file is created but not responding — Miriam List, Aug 14 '19 at 12:25
In both cases you are using `Thisworkbook`, so you are comparing two sheets of same workbook (the one that holds the macro). Is that right? According to your question, you want to compare 2 Excel files, that means 2 different workbooks. — Foxfire And Burns And Burns, Aug 14 '19 at 12:45
@Foxfire And Burns And Burns yes that is the goal comparing two different Excel files. I would keep Set ws1 = ThisWorkbook.Worksheets(1) the same ( holds the macro ). How would I do it for the other file ? — Miriam List, Aug 14 '19 at 16:14

Tim Stack · Accepted Answer · 2019-08-13T09:33:43.357

4

As per my comment, I would use a variable to refer to the worksheet of a specific workbook:

Option Explicit

Private Sub CommandButton1_Click()

 Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
 Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
 Dim report As Workbook, ws As Worksheet 'Declare ws to be a Worksheet variable
 Dim difference As Long, row As Long, col As Integer

 Set report = Workbooks.Add

 'Set the ws variable to be the first worksheet in the newly opened workbook
 Set ws = report.Worksheets(1)

 'You can then use the ws variable to refer to that specific worksheet
 'The variable is not affected by the sheet changing name or position
 'Example:
 ws.Name = "NewSheetName"
 MsgBox "The sheet at position " & ws.Index & " has changed names and is now called " & ws.Name

 'Rest of the code

edited Aug 13 '19 at 09:33

answered Aug 13 '19 at 08:21

Tim Stack

3,209
3
18
39

This seems to good. It technically not part of what this question asks but this is the error I'm getting now Object required error at "colval1 = ws1.Cells(row, col).Formula". – Miriam List Aug 13 '19 at 08:45
I don't see any code setting the `ws1` and `ws2` variables. You're referring to empty variables. You can see how I have set my `ws` variable before referring to it – Tim Stack Aug 13 '19 at 08:59
All I know is that colval1 is supposed to get the value of that row in wk1( worksheet1) and then we compare that row from both worksheets If colval1 <> colval2 . if they are different then do change the font and the other stuff – Miriam List Aug 13 '19 at 09:11
1

Look at my code and how I declare and set a worksheet variable. This variable is a reference to the specific worksheet you set it to. You're supposedly trying to use worksheet variables with `ws1` and `ws2`, but you never set these (link them to worksheets) – Tim Stack Aug 13 '19 at 09:32
1

Let me add that using `Option Explicit` would be a great starting point. This forces you to declare every variable and thus makes sure you don't make any spelling mistakes or empty references. The compiler will immediately realise `ws1` and `ws2` are nonsense variables before the code executes – Tim Stack Aug 13 '19 at 09:37
I have gone through your adjusment and the advice you have given. Now I have to declare ws1 as variable of sheet1 and ws2 as variable of sheet2. how do I do this considering that sheet1 and sheet2 are already created – Miriam List Aug 13 '19 at 11:34
I don't understand the problem because "Sheet1" is also already created. Regardless, read the link – Tim Stack Aug 13 '19 at 11:38
if you can fix the code so I dont get the error at colval1 = ws1.Cells(row, col).Formula so the line actually gets the data from first sheet .That would mean a lot because at this point the comments are just confusing me, if not. I still appreaciate a lot your help. thank u – Miriam List Aug 13 '19 at 11:53
The solution is in my answer... I press you to read it line by line with the commented text. You can't use `ws1` ***without actually linking the variable to the worksheet you mean to refer to***. Look at my code, see how I linked `ws` to a specific worksheet? – Tim Stack Aug 13 '19 at 11:59
You can check the changes I did on the code above. Now I'm getting the error: index outside the valid range at With ActiveWorkbook.Worksheets("Sheet1") – Miriam List Aug 13 '19 at 12:12

score 2 · Answer 2 · answered Aug 13 '19 at 07:42

2

Change this:

ActiveWorkbook.Worksheets ("Sheet1")

to this:

With ActiveWorkbook.Worksheets("Sheet1")

Same for Sheet2

answered Aug 13 '19 at 07:42

braX

11,506
5
20
33

thank you for the answer. Now I get an error at "With ActiveWorkbook.Worksheets("Sheet1")" saying index outside the valid range – Miriam List Aug 13 '19 at 07:51
Is there a sheet called "Sheet1"? or is it called "Sheet 1" (with a space)? – braX Aug 13 '19 at 07:56
I have a gave a comment above. – Miriam List Aug 13 '19 at 08:07

Improper use of a Property

2 Answers2