Results 1 to 7 of 7

Can't get variables to pass from one procedure to same in the same module

Threaded View

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Orion, Michigan
    MS-Off Ver
    2003
    Posts
    3

    Can't get variables to pass from one procedure to same in the same module

    The code I have works great as separate procedures. I decided to combine the 2 procedures as the code is mostly the same with a few differences in which cells are copied to the array to save to different log sheets. Each Procedure has a Inputbox to get the year of interest (2014, 2015, 2016....) that will be used to open AND in the event the Log does not exist Save a new one from a master using the year code in the file name. Based on a cell value I am doing an If_Then_Else to determine to run one procedure or both. If I run them separately as individual procedures they work fine. When I combined them I decided to call one procedure (SaveToLog_Audit) from inside (SaveToLog_Replc). I removed the Date input from (SaveToLog_Audit) letting (SaveToLog_Replc) get that date code and pass it along. I don't want to have (SaveToLog_Audit) ask for a date a second time as it run since I basically want it to be inobtrusive (no need to ask twice when I can pass 'Ans' to it.

    I have tried setting public Ans and used Explicit at the beginning with both procedures in same module --> didn't work. I tried Dim Ans As String after option Explicit --> Didn't work. I tried Public Ans as String --> didn't work. Pretty much I couldn't get it to pass the variable 'Ans" no matter what I tried. I'm still pretty new to coding VBA so any help would be great. Showing the basic code, only the parts that have issues. I apologize in advance if I didn't tag this correctly.

         Option Explicit
         Public Ans As String
           
         Public Sub SaveToLog_Replc()
         
         '  Determine scrap and whether to save to Replacement_Credit tracking log
         Dim ScrapQty As Range
         '  Date entry for proper Dated log save
         Dim Ans As String
         Dim c As Boolean
         Dim fs As Object
         '  Declares path, newFile and fName variables
         Dim path As String, newFile As String, fName As String
           
         Set Sheet2 = ActiveSheet
         Set ScrapQty = Worksheets("Main").Range("M_Qty_Scrap")
         
         Application.ScreenUpdating = False
            
         '  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
            Ans = InputBox("Enter Log Year" & _
             "" & vbCrLf, "Year Selection", Format(Date, "YYYY"))
               If Ans = "" Then
                  Exit Sub
               End If
         '  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
            
            '  If scrap qty is greater than Zero (0) save to both Logs
            If ScrapQty > 0 Then
            ' If True
               MsgBox "Saving to Replacement Log and Audit Log"
            
            ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            '    Save to Audit Log then Save to Replacement Log
              '  --> 'Ans' VALUE NEEDS TO PASS TO THE PROCEDURE
              '  --> 'Ans' DOES NOT PASS TO THIS PROCEDURE AT END OF MODULE
     --->   Call SaveToLog_Audit
            ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            ' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            '  Sub routine to Save Data to Replacement Log
            '  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            '     Load Array code here for SaveToLog_Replc
            '  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            '  ***************************************************************
               'Check if Replacement Log exists. If not open Master, rename then save it. If it exists open it.
                Set fs = CreateObject("Scripting.FileSystemObject")
                '  Filename to check for
                c = fs.fileExists("S:\RECORDS\Logs\Replacement Log " & Ans & ".xls")
                  If Not c Then
                     'MsgBox "The file doesn't exist!"
                     '  File doesn't exist. Open the Blank Master
                     Workbooks.Open Filename:="S:\RECORDS\Logs\_MASTER Replacement Log.xls"
                     fName = "Replacement Log " & Ans & ".xls"              ' Set fName to new FileName
                     newFile = fName                                                  ' Sets new filename as fName
                     path = "S:\RECORDS\Logs\"                                   ' Path to Incoming Audit Logs
                     ActiveWorkbook.SaveAs Filename:=path & newFile     ' Saves as newFile
                  Else
                     'MsgBox "The file exists! Saving data to it."
                     Workbooks.Open Filename:= "S:\RECORDS\Logs\Replacement Log " & Ans & ".xls"
                  End If
            '  ***************************************************************
            '  Unprotect Sheet and Show All Data code here
            '     Find LastRow. Set NextCell position code here
            '  Set the size of the new array and copy MyAr code here
            '     Draw Border Code here
            '  **********************************************
            ActiveWorkbook.Save                 ' Saves Destination Workbook
            ActiveWindow.Close                   ' Closes Destination Workbook
            Application.ScreenUpdating = True
            '  Confirms Save to Log File
            MsgBox "Your Data has been saved to the Log File: " & vbCrLf & vbCrLf _
            & "'Replacement Log " & Ans & ".xls'", vbInformation, "Log Save Confirmation"
            ' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            Else
               ' If False
               MsgBox "Saving to Audit Log Only."
            ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
              ' Save to Audit Log as Normal
              '  --> 'Ans' VALUE NEEDS TO PASS TO THE PROCEDURE
              '  --> 'Ans' DOES NOT PASS TO THIS PROCEDURE AT END OF MODULE
    --->   Call SaveToLog_Audit
            ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            End If
         End Sub
    ----------------------------------------------------------------------------------------     
         Public Sub SaveToLog_Audit()
         
         '  Date entry for proper Dated log save
         Dim Ans As String
         Dim c As Boolean
         Dim fs As Object
         '  Declares path, newFile and fName variables
         Dim path As String, newFile As String, fName As String
         
         Set Sheet2 = ActiveSheet
         Application.ScreenUpdating = False
            '  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            '     Load Array code here
            '  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            '  For debug
            '  --> 'Ans' DOES NOT PASS FROM (SaveToLog_Replc) PROCEDURE IN THIS MODULE
             MsgBox "The value of Ans is: " & vbCrLf & vbCrLf & "'" & Ans & "'"
            '  ********************************************************
             '  Checks if Log File exists. If not open the Master, rename then save it. If it exists open it.
             Set fs = CreateObject("Scripting.FileSystemObject")
             '  Filename to check for
             c = fs.fileExists("S:\RECORDS\Logs\" & Ans & " Audit Log.xls")
               If Not c Then
                  'MsgBox "The file doesn't exist!" original code
                  '  File doesn't exist. Open the Blank Master
                  Workbooks.Open Filename:="S:\RECORDS\Logs\_Master Audit Log.xls"
                  '  Set fName to new FileName
                  fName = Ans & " Audit Log.xls"
                  '  Sets new filename as fName saves to this directory
                  newFile = fName
                  '  Path to Incoming Audit Logs
                  path = "S:\RECORDS\Logs\"
                  '  Saves Blank Master as newFile
                  ActiveWorkbook.SaveAs Filename:=path & newFile
               Else
                  'Workbooks.Open "C:\filename.xls"
                  Workbooks.Open Filename:= _
                  "S:\RECORDS\Logs\" & Ans & " Audit Log.xls"
               End If
            '  ********************************************************
            '  Unprotect Sheet and Show All Data code here
            '     Find LastRow. Set NextCell position code here
            '  Set the size of the new array and copy MyAr code here
            '     Draw Border Code here
            '  **********************************************
            ActiveWorkbook.Save                       ' Saves Destination Workbook
            ActiveWindow.Close                         ' Closes Destination Workbook
            Application.ScreenUpdating = True
            '  Confirms Save to Log File
            MsgBox "Your Data has been saved to the Log File: " & vbCrLf & vbCrLf _
            & "'" & Ans & " Audit Log.xls'", vbInformation, "Log Save Confirmation"
         End Sub
    Last edited by valhalla_33; 05-20-2015 at 10:59 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How can I call a Module or a sub procedure and run it automatically in current module?
    By qzqzjcjp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2014, 11:48 AM
  2. [SOLVED] How to use second procedure (the 2nd Sub) results in first procedure in the same module
    By lubbamkt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2014, 10:17 AM
  3. [SOLVED] Pass date variable to another procedure/module
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2013, 10:16 PM
  4. [SOLVED] Pass variables from userform to module
    By Bishonen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2012, 07:54 AM
  5. [SOLVED] Pass variables from Worksheet_Calculate sub to Module
    By John Michl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2006, 11:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1