[nquadr]

Hi I have written the following code, and I am trying to use wbNew and wbOld in a new function. I am totally new to VBA programming, and I am trying to do this for my internship/work.

Sub CreateWB()

    
    Dim wbExcel As Object
    Set wbExcel = CreateObject("Excel.Application")
    Dim wbNew As Workbook
    Dim wbOld As Workbook
    wbExcel.Visible = True
    
    Set wbNew = wbExcel.Workbooks.Open("filename1")
    Set wbOld = wbExcel.Workbooks.Open("filename2")
    
        
End Sub

2nd function

Sub Comparison()

    
    wbOld.ActiveSheet.Range("F1").Select
    


End Sub

Unfortunately, I cannot get it to work.
I know that the wbOld and wbNew is out of scope and therefore not working, but I do not know how to set them up as global variable.

Any help would be greatly appreciated. Thanks.

[BobRodes]

You don't have to make them a "global variable" exactly. Just put the declarations at the top of your code in the "General Declarations" area, outside of any subs or functions. That way, they'll be visible to anything in the code window.

[nquadr]

BobRodes, on 08 June 2012 - 10:48 AM, said:

You don't have to make them a "global variable" exactly. Just put the declarations at the top of your code in the "General Declarations" area, outside of any subs or functions. That way, they'll be visible to anything in the code window.

So I should just have it as the first function or are you saying that I should have the code there without Sub and End Sub?

I am getting a compile error: "Invalid outside procedure"

This post has been edited by nquadr: 08 June 2012 - 11:32 AM

[BobRodes]

nquadr, on 08 June 2012 - 01:29 PM, said:

So I should just have it as the first function or are you saying that I should have the code there without Sub and End Sub?

I am getting a compile error: "Invalid outside procedure"

The latter. Put ONLY the variable declarations there (lines 6 and 7 in your code). Is that what you did?

Your code window should have "(General)" in the top left dropdown list, and "(Declarations)" in the top right dropdown list. If you have a line that says "Option Explicit" the declarations should go right below that.

This post has been edited by BobRodes: 08 June 2012 - 11:40 AM

[nquadr]

BobRodes, on 08 June 2012 - 11:37 AM, said:

nquadr, on 08 June 2012 - 01:29 PM, said:

So I should just have it as the first function or are you saying that I should have the code there without Sub and End Sub?

I am getting a compile error: "Invalid outside procedure"

The latter. Put ONLY the variable declarations there (lines 6 and 7 in your code). Is that what you did?

Your code window should have "(General)" in the top left dropdown list, and "(Declarations)" in the top right dropdown list.

Yes, but now I get Run-time error '91' Object variable or With block variable not set.


I run the CreateWB() code to open my files, but I need to set wbOld and wbNew but I am not aware of a method that allows me to set the file except the open method, but all that does is opens the file again, and that is what I am trying to avoid. I am testing each line of my code and don't want it to open many copies of the same file.

Do you know of another way to set the object? The ones I have tried like the Activate method has not worked for me.

[BobRodes]

Doesn't help me much if you don't tell me what line of code gives the error, because you don't tell me which object "the" object is. Not sure what you mean by "set the object" either, let alone "set the file."

I don't understand what this has to do with "testing each line of your code", either, because I have no idea what the code does.

[nquadr]

BobRodes, on 08 June 2012 - 11:53 AM, said:

Doesn't help me much if you don't tell me what line of code gives the error, because you don't tell me which object "the" object is. Not sure what you mean by "set the object" either, let alone "set the file."

I don't understand what this has to do with "testing each line of your code", either, because I have no idea what the code does.

Sorry, I should have explained what I am trying to acccomplish. I am trying to automate a report given to me from two excel files. We shall call one wbOld and the other wbNew. I have written most of the code for it, and I will post it below.

The trouble I am having is I will need to do a comparison of two columns that are in different excel files and report the differences as well as the similarities and update the file with appropriate comments. (similar to a vlookup)

I am having trouble with Sub CreateWB() and Sub Comparison(). Since I set objects with values in the Sub CreateWB() for my workbook, my objects are only set in that scope. I need Sub Comparison() to be able to test the comparison so I wrote a line of code to check if it executes in the appropriate excel file. For now if I can run code between two excel files it will allow me to proceed to code for the comparisons.

Hope that helps. The error is related to the code in Sub Comparison(). Thanks.


Declarations

    Dim wbNew As Workbook
    Dim wbOld As Workbook

 Sub DeleteRows1to7()

    ActiveSheet.Range("A1:A7").Select
    Selection.EntireRow.Delete
    
End Sub

ub deleteAge()
    Application.ScreenUpdating = False
    Dim dValue, dCount As Integer
    
    For dCount = 1 To 2000
            
    dValue = ActiveCell.Value
    If dValue < 15 Then
    Selection.EntireRow.Delete
    dValue = ActiveCell.Select
    
    Else
    ActiveCell.Offset(1, 0).Select
       End If
    
    Next dCount
    Application.ScreenUpdating = True

Sub replaceColR()

    Dim rValue As String
    ActiveSheet.Range("R2").Select
    
    For rCount = 1 To 2000

        rValue = ActiveCell.Value
        
        If rValue = "B" Or rValue = "b" Then
        ActiveCell.Value = "Buy-In Due"
        ActiveCell.Offset(1, 0).Select
        
        ElseIf rValue = "R" Or rValue = "r" Then
        ActiveCell.Value = "Re-issue"
        ActiveCell.Offset(1, 0).Select
        
        Else
        ActiveCell.Offset(1, 0).Select
        
        
        
        End If
    Next rCount
    ActiveSheet.Range("R1").Select

These are the two I am having problems with. Sub CreateWB() works, whereas Sub Comparison() does not.

Sub CreateWB()

    
    Dim wbExcel As Object
    Set wbExcel = CreateObject("Excel.Application")
    Dim wbNew As Workbook
    Dim wbOld As Workbook
    wbExcel.Visible = True
    
    Set wbNew = wbExcel.Workbooks.Open("filename1")
    Set wbOld = wbExcel.Workbooks.Open("filename2")
    
    wbOld.ActiveSheet.Range("S1").Select
    
    
    
        
End Sub

Sub Comparison()

    wbOld.ActiveSheet.Range("F1").Select
    
    



End Sub

This post has been edited by nquadr: 08 June 2012 - 12:12 PM

[BobRodes]

where do you have your Comparison sub?

[maj3091]

remove lines 6 and 7 from CreateWB, these are local declarations in that sub and will be used before your General Dec ones, hence the error in Comparison when you try to access wbOld.

[BobRodes]

oo! I missed that. Good catch, maj.

nq, if you have variables in the general declarations area, and you have variables of the same name in your sub, your sub will think you are referring to the ones in your sub instead of the ones in the general declarations area. So, you instantiated the ones in your sub, and then your Comparison tried to look at the ones in the gen dec area and you hadn't instantiated those.

[nquadr]

Hi I have tried the suggestions, however when I try to run the code

Sub Comparison()

    wbOld.ActiveSheet.Range("F1").Select

End Sub

I get a Run-time error '424' Object Required.

I think it has to do with instantiation. I am only creating an instance of wbOld and wbNew when I set the files in the Sub CreateWB(). I have not created an instance of the object in Sub Comparison(). I am not sure as of how to accomplish this since my knowledge limits me to only the Open method.

Any ideas?

This post has been edited by nquadr: 11 June 2012 - 05:28 AM

[nquadr]

I did some research and at the top of the workbook I did this edit to the code and it works.

Public wbNew As Workbook
    Public wbOld As Workbook