Need help with Function

V

vzq032372

New Member
Joined
Dec 17, 2015
Messages
37
Hello,
I am trying to create a function so that I can set the opened workbook as the variable to reference in other Sub-routines. Here is a snippet of what I started based on how I was using it before separating the sub routines.

VBA Code: 
Function closedbook() As Variant
Dim closedbook As Workbook
On Error Resume Next
Set closedbook = Workbooks.Open(FilePath)
On Error GoTo 0
End Function

This is a snippet of the sub-routine I am trying to call it in and how it's used
VBA Code: 
Sub ImportSheets()

UserForm1.Show vbModeless
 
Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
   
    Dim DialogBox As FileDialog
    Dim FilePath As String
    Dim SheetName As String
    Dim wb As Workbook
    Dim closedbook As Workbook
   
    Set wb = ThisWorkbook
   
    wb.Sheets("Revision History").Visible = True

'Allow user to select the Estimate files that will be copied
    Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)
            DialogBox.InitialView = msoFileDialogViewList
            DialogBox.InitialFileName = "\\nu.com\data\SharedData\Estimating\"
            DialogBox.Title = "Select Estimates to copy " '& FileType
            DialogBox.AllowMultiSelect = True
            DialogBox.Filters.Clear
            DialogBox.Show

        If DialogBox.SelectedItems.Count = 1 Then
           FilePath = DialogBox.SelectedItems(1)
        End If
    Dim i As Integer
'Makes a count of the number of files selected
    For i = 1 To DialogBox.SelectedItems.Count

'What are we copying...
    FilePath = DialogBox.SelectedItems(i)

     [B]Set closedbook = Workbooks.Open(FilePath)
    
     closedbook.Activate[/B]
    
    
        Call UnProtect
        Call StartImport
 
         [B]closedbook.Close SaveChanges:=False[/B]
    Next i
    
    Call Protect

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
 Unload UserForm1
 End Sub

This is where I start pulling information and formatting in the opened workbook
VBA Code: 
Sub StartImport()
            [B]closedbook.Sheets("Data Input Sheet").Select[/B]

'do some stuff
       [B]closedbook.Sheets(Sheetcover).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)[/B]
        ThisWorkbook.Worksheets(Sheetcover).Activate
       
        Dim cov As String
            cov = wb.Sheets(Sheetcover).Range("D1").Value & "_" & Range("C1").Value

End Sub

Any ideas?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
What specifically is the problem you are having? I don't see the need for the function "closedbook" so perhaps you could explain.

Lastly, you have a function called "closedbook" (Function closedbook) which contains a workbook variable also called "closedbook" (Dim closedbook As Workbook) . That's a bad idea because it creates confusion about what you are trying to accomplish. . Better to make the function name different from the variable name.
 
Upvote 0
rlv01 said:
What specifically is the problem you are having? I don't see the need for the function "closedbook" so perhaps you could explain.

Lastly, you have a function called "closedbook" (Function closedbook) which contains a workbook variable also called "closedbook" (Dim closedbook As Workbook) . That's a bad idea because it creates confusion about what you are trying to accomplish. . Better to make the function name different from the variable name.
Click to expand...
closedbook = Workbooks.Open(FilePath)

I would like to reference closedbook in my sub-routines so that it maintains the variable of the opened workbook. If not a function, then a Public Sub? Public Const? not sure which way to go with this
 
Upvote 0
Perhaps something like this (not tested)
VBA Code: 
Sub ImportSheets()
    
    UserForm1.Show vbModeless
    
    Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    
    Dim DialogBox As FileDialog
    Dim FilePath As String
    Dim SheetName As String
    Dim wb As Workbook
    Dim closedbook As Workbook
    
    Set wb = ThisWorkbook
    
    wb.Sheets("Revision History").Visible = True
    
    'Allow user to select the Estimate files that will be copied
    Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)
    DialogBox.InitialView = msoFileDialogViewList
    DialogBox.InitialFileName = "\\nu.com\data\SharedData\Estimating\"
    DialogBox.Title = "Select Estimates to copy " '& FileType
    DialogBox.AllowMultiSelect = True
    DialogBox.Filters.Clear
    DialogBox.Show
    
    If DialogBox.SelectedItems.Count = 1 Then
        FilePath = DialogBox.SelectedItems(1)
    End If
    
    With CreateObject("Scripting.FileSystemObject")
    Dim i As Integer
        'Makes a count of the number of files selected
        For i = 1 To DialogBox.SelectedItems.Count
            
            'What are we copying...
            FilePath = DialogBox.SelectedItems(i)
            If .FileExists(FilePath) Then
                Set closedbook = Workbooks.Open(FilePath)
                closedbook.Activate
                
                Call Unprotect
                Call StartImport(closedbook, SheetName)
                
                closedbook.Close SaveChanges:=False
            Else
                Debug.Print "Filepath " & i & " not found: " & FilePath
            End If
        Next i
    End With
    Call Protect
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    Unload UserForm1
End Sub


For this, you need to define what you want Sheetcover to be
VBA Code: 
Sub StartImport(ByRef closedbook As Workbook, Sheetcover As String)
    closedbook.Sheets("Data Input Sheet").Select
    
    'do some stuff
    closedbook.Sheets(Sheetcover).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ThisWorkbook.Worksheets(Sheetcover).Activate
    
    Dim cov As String
    cov = ThisWorkbook.Sheets(Sheetcover).Range("D1").Value & "_" & Range("C1").Value
   
End Sub

also, you are not doing anything with variable "cov" , so why have it there?
 
Upvote 0
Solution
rlv01 said:
Perhaps something like this (not tested)
VBA Code: 
Sub ImportSheets()
   
    UserForm1.Show vbModeless
   
    Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
   
    Dim DialogBox As FileDialog
    Dim FilePath As String
    Dim SheetName As String
    Dim wb As Workbook
    Dim closedbook As Workbook
   
    Set wb = ThisWorkbook
   
    wb.Sheets("Revision History").Visible = True
   
    'Allow user to select the Estimate files that will be copied
    Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)
    DialogBox.InitialView = msoFileDialogViewList
    DialogBox.InitialFileName = "\\nu.com\data\SharedData\Estimating\"
    DialogBox.Title = "Select Estimates to copy " '& FileType
    DialogBox.AllowMultiSelect = True
    DialogBox.Filters.Clear
    DialogBox.Show
   
    If DialogBox.SelectedItems.Count = 1 Then
        FilePath = DialogBox.SelectedItems(1)
    End If
   
    With CreateObject("Scripting.FileSystemObject")
    Dim i As Integer
        'Makes a count of the number of files selected
        For i = 1 To DialogBox.SelectedItems.Count
           
            'What are we copying...
            FilePath = DialogBox.SelectedItems(i)
            If .FileExists(FilePath) Then
                Set closedbook = Workbooks.Open(FilePath)
                closedbook.Activate
               
                Call Unprotect
                Call StartImport(closedbook, SheetName)
               
                closedbook.Close SaveChanges:=False
            Else
                Debug.Print "Filepath " & i & " not found: " & FilePath
            End If
        Next i
    End With
    Call Protect
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
    Unload UserForm1
End Sub


For this, you need to define what you want Sheetcover to be
VBA Code: 
Sub StartImport(ByRef closedbook As Workbook, Sheetcover As String)
    closedbook.Sheets("Data Input Sheet").Select
   
    'do some stuff
    closedbook.Sheets(Sheetcover).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ThisWorkbook.Worksheets(Sheetcover).Activate
   
    Dim cov As String
    cov = ThisWorkbook.Sheets(Sheetcover).Range("D1").Value & "_" & Range("C1").Value
  
End Sub

also, you are not doing anything with variable "cov" , so why have it there?
Click to expand...
I will test your code and let you know.

When closedbook is opened, I have it doing a bunch of things including getting values from different areas and using those to name the sheet after it's copied. The code then goes back to closedbook and gets information from another sheet and copies it back and when it does it will rename the sheet with cov & "whatever" sheet name it is.
 
Upvote 0
rlv01 said:
Perhaps something like this (not tested)
VBA Code: 
Sub ImportSheets()
   
    UserForm1.Show vbModeless
   
    Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
   
    Dim DialogBox As FileDialog
    Dim FilePath As String
    Dim SheetName As String
    Dim wb As Workbook
    Dim closedbook As Workbook
   
    Set wb = ThisWorkbook
   
    wb.Sheets("Revision History").Visible = True
   
    'Allow user to select the Estimate files that will be copied
    Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)
    DialogBox.InitialView = msoFileDialogViewList
    DialogBox.InitialFileName = "\\nu.com\data\SharedData\Estimating\"
    DialogBox.Title = "Select Estimates to copy " '& FileType
    DialogBox.AllowMultiSelect = True
    DialogBox.Filters.Clear
    DialogBox.Show
   
    If DialogBox.SelectedItems.Count = 1 Then
        FilePath = DialogBox.SelectedItems(1)
    End If
   
    With CreateObject("Scripting.FileSystemObject")
    Dim i As Integer
        'Makes a count of the number of files selected
        For i = 1 To DialogBox.SelectedItems.Count
           
            'What are we copying...
            FilePath = DialogBox.SelectedItems(i)
            If .FileExists(FilePath) Then
                Set closedbook = Workbooks.Open(FilePath)
                closedbook.Activate
               
                Call Unprotect
                Call StartImport(closedbook, SheetName)
               
                closedbook.Close SaveChanges:=False
            Else
                Debug.Print "Filepath " & i & " not found: " & FilePath
            End If
        Next i
    End With
    Call Protect
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
    Unload UserForm1
End Sub


For this, you need to define what you want Sheetcover to be
VBA Code: 
Sub StartImport(ByRef closedbook As Workbook, Sheetcover As String)
    closedbook.Sheets("Data Input Sheet").Select
   
    'do some stuff
    closedbook.Sheets(Sheetcover).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ThisWorkbook.Worksheets(Sheetcover).Activate
   
    Dim cov As String
    cov = ThisWorkbook.Sheets(Sheetcover).Range("D1").Value & "_" & Range("C1").Value
  
End Sub

also, you are not doing anything with variable "cov" , so why have it there?
Click to expand...
@rlv01
Your code worked perfectly!!! Thank you for taking the time!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA: Copy sheet and paste as values to closed workbook
Replies
8
Views
1K
cofracr
C
E
  • Solved
Convert Subroutine to Function?
Replies
8
Views
393
EssKayKay
E
E
  • Question Question
Code doesn’t work- HELP
Replies
3
Views
472
Domenic
D
E
  • Locked
  • Question Question
Save file as pdf - VBA HELP
Replies
1
Views
437
Fluff
Fluff
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
241
Micron
M

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top