Copy variable over from called out code - VBA

[CC268]

I have the following code that simply opens up a window and allows the user to choose the file they want to open:

Sub FileDialog_Open()

With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        If .Show Then
            Set wb = Workbooks.Open(.SelectedItems(1))
        Else
            Exit Sub
        End If
    End With
    
End Sub

It is called out in the following code - the problem is that I keep getting a message on the bolded line below that states, "Object variable or With block variable not set" - so I assume it isn't carrying over the variable "wb" from the FileDialog_Open code. How can I fix this?

Option Explicit

Sub Format_AsBuilt()


Call OptimizeCode_Begin


Dim CopyFromWbk, CopyToWbk, wb As Workbook
Dim ShToCopy As Worksheet
Dim FileName, currentlevel, currentpart, currentserial, currentrev As Variant
Dim inrow, inlevel As Long


Call FileDialog_Open
    
Call Sheet_Selector
    
Set CopyFromWbk = wb
[COLOR=#b22222][B]Set ShToCopy = CopyFromWbk.ActiveSheet[/B][/COLOR]
Set CopyToWbk = ThisWorkbook
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
ActiveSheet.Name = "Sheet1"
CopyFromWbk.Close savechanges:=False


Rows("1:9").Delete
Columns("B:D").Insert
Cells(1, 2) = "NHA Part Number"
Cells(1, 3) = "NHA Serial Number"
Cells(1, 4) = "NHA Rev"
Columns("L:R").EntireColumn.Delete
Columns.AutoFit
ActiveSheet.Cells.UnMerge
Columns("G:G").Select
Selection.Copy
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Copy
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft


Dim partno(6) As Variant 'defining our variables - variant is a data type that can hold any type of value you want
Dim serialno(6) As Variant 'same as above
Dim revno(6) As Variant 'same as above
inrow = 2 'defining the variable "inrow" to equal 2
inlevel = 0 'defining the variable "inlevel" to equal 0


Range("b2:d5000").ClearContents 'this is simply taking the range of b2:d5000 and clearing the contents of the cells


While Cells(inrow, 1) <> "" 'while cell in row 2, column 1...
currentlevel = Cells(inrow, 1) 'the variable currentlevel is equal to the value of the cell in row 2, column 1
currentpart = Cells(inrow, 5) 'the variable currentpart is equal to the value of the cell in row 2, column 5
currentserial = Cells(inrow, 6) 'the variable currentserial is equal to the value of the cell in row 2, column 6
currentrev = Cells(inrow, 7) ' the variable currentrev is equal to the value of the cell in row 2, column 7
partno(currentlevel) = currentpart 'the variable partno in the currentlevel is equal to the variable currentpart (whatever value is in row 2, column 5)
serialno(currentlevel) = currentserial 'the variable serialno in the currentlevel is equal to the variable currentserial (whatever value is in row 2, column 6)
revno(currentlevel) = currentrev 'the variable revno in the currentlevel is equal to the variable currentrev (whatever value is in row 2, column 7)
    
If currentlevel > 1 Then 'if the value in row 2, column 1 is greater than 1 then proceed to the following...
Cells(inrow, 2) = partno(currentlevel - 1) 'the value in row 2, column 2 = value of partno in the current level - 1
Cells(inrow, 3) = serialno(currentlevel - 1) 'the value in row 2, column 3 = value of partno in the current level - 1
Cells(inrow, 4) = revno(currentlevel - 1) 'the value in row 2, column 4 = value of partno in the current level - 1
End If 'end if statement
        
inrow = inrow + 1 'move onto the next row (row 3)
Wend 'end while loop


Columns("A").EntireColumn.Delete


Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Dim Lst As Long
Lst = Range("B" & Rows.Count).End(xlUp).Row


With Range("A1")
    .Value = "1"
    .AutoFill Destination:=Range("A1").Resize(Lst), Type:=xlFillSeries
End With


'Cells.Select
    ' With Selection
       ' .WrapText = False
     'End With
     
'Columns.HorizontalAlignment = xlCenter
'Columns.VerticalAlignment = xlCenter
'Columns.AutoFit
'Rows.AutoFit


'Cells.Select
    'With Selection.Interior
        '.Pattern = xlNone
        '.TintAndShade = 0
        '.PatternTintAndShade = 0
    'End With
    
'Cells.Select
   ' With Selection.Borders
   ' .LineStyle = xlNone
   ' End With


'Range("A1").Select
'ActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell) 'matches vertical scrollbar length to number of rows
'Sheets("MACROS").Select
    
Call OptimizeCode_End
    
End Sub

 

[dmt32]

Hi,

Move your variable to the the very TOP of the module OUTSIDE of any procedure. This will make it available to all procedures in the module.
Also, you need to handle the Cancel button in your procudure.

See following changes:

Dim wb As Workbook <top of="" module


Sub FileDialog_Open()


With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show Then
Set wb = Workbooks.Open(.SelectedItems(1))
Else
'Cancel pressed
Set wb = Nothing
End If
End With

End Sub




Sub Format_AsBuilt()


Dim CopyFromWbk, CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Dim FileName, currentlevel, currentpart, currentserial, currentrev As Variant
Dim inrow, inlevel As Long


Call OptimizeCode_Begin


Call FileDialog_Open

Call Sheet_Selector

'cancel pressed
If wb Is Nothing Then Exit Sub

Set CopyFromWbk = wb


'rest of code


End Sub



Finally, your declarations:

Dim CopyFromWbk, CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Dim FileName, currentlevel, currentpart, currentserial, currentrev As Variant
Dim inrow, inlevel As Long

ONLY the the last variable on each line will be the data type you have specified. You need to declare after each variable the required data type

e.g.

Dim CopyFromWbk As Workbook, CopyToWbk As Workbook

Dave
PS sorry having issues with code tags.</top>

 

[Norie]

You could always change to a function that returns a reference to the workbook the user selects.

Something like this perhaps.

Function FileDialog_Open() As Workbook
Dim wb As Workbook

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        If .Show Then
            Set wb = Workbooks.Open(.SelectedItems(1))
        Else
            Exit Sub
        End If
    End With
    
    Set FileDialog_Open = wb

End Sub

In the main code you could call the function like this.

Set CopyFromWbk = FileDialog_Open()

 

[CC268]

Are you sure about having to declare after each variable the required type? I am using Option Explicit..if it wasn't defined it would tell me?

So I tried what you recommended...still getting the same error. I can't figure out why it won't work.

I may just have to stick to how I originally have it and just have the code within...it works, but the code looks cleaner just calling it out.

 

[CC268]

You could always change to a function that returns a reference to the workbook the user selects.

Something like this perhaps.

Function FileDialog_Open() As Workbook
Dim wb As Workbook

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        If .Show Then
            Set wb = Workbooks.Open(.SelectedItems(1))
        Else
            Exit Sub
        End If
    End With
    
    Set FileDialog_Open = wb

End Sub

In the main code you could call the function like this.

Set CopyFromWbk = FileDialog_Open()

This errors out immediately and says "Exit Sub not allowed in Function or Property"

 

[Norie]

Oops, forgot to change a couple of things.

Function FileDialog_Open() As Workbook
Dim wb As Workbook

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        If .Show Then
            Set wb = Workbooks.Open(.SelectedItems(1))
        Else
            Exit Function
        End If
    End With
    
    Set FileDialog_Open = wb

End Function

PS Option Explicit forces you to declare variables but it doesn't force you to declare them as a certain data type.

 

[dmt32]

Are you sure about having to declare after each variable the required type? I am using Option Explicit..if it wasn't defined it would tell me?

So I tried what you recommended...still getting the same error. I can't figure out why it won't work.

I may just have to stick to how I originally have it and just have the code within...it works, but the code looks cleaner just calling it out.

Very sure, using Option Explicit will report undeclared variables - you had them all declared but as written, not all will be the correct data types. You need to declare them as I gave in my example.

Suggestion assumed that both your codes are in same module if not, you will need to declare your variable as public but I would not do that - Follow Nories suggestion and change your sub to a function.

Dave

 

[CC268]

Very sure, using Option Explicit will report undeclared variables - you had them all declared but as written, not all will be the correct data types. You need to declare them as I gave in my example.

Suggestion assumed that both your codes are in same module if not, you will need to declare your variable as public but I would not do that - Follow Nories suggestion and change your sub to a function.

Dave

Ahh okay that makes sense - and they are in separate modules. I'm not sure if it is best practice for writing VBA...but all my Subs are in separate modules. Then I can just call them as I need.

 

[dmt32]

Ahh okay that makes sense - and they are in separate modules. I'm not sure if it is best practice for writing VBA...but all my Subs are in separate modules. Then I can just call them as I need.

Placing your Subs in separate modules is fine but you need to ensure that any variables needed are within scope of all called modules - common way to do this is to pass them as arguments to the procedure(s) or call a function as per Nories suggestion to return required value / object. You can declare your variables as Public but most would suggest this should be avoided if possible.

Dave

 

[CC268]

You could always change to a function that returns a reference to the workbook the user selects.

Something like this perhaps.

This worked great, thanks a lot!