Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code in one Module referencing Public Variables Declared inanother Module failing
Hi all,
I really hope you guys can help as I have nowhere else to check. In a new job, I have inherited a Excel document that contains a large amount of VBA code. One of the Worksheets contains a button that imports data from another Excel document used by another part of the business. The data in the target Excel document is always in the same place. I am listing two pieces of VBA code below. One is the Sub assigned to the button on the Excel document that is importing the data. This is in a module called "ImportCore". I have manually added line numbers to the code for ease of reference. The second is the declarations from another module called "StartProgram". These contain the variables that are being utilised in the ImportCore module. The importing is done by me opening both the destination and target Excel documents. The name of the target document from which we are importing is placed in a specific cell in the destination doc. This cell is a named range called "CoreLoc" and is defined as ='DCW Reference Data'!$B$4. In the destination Excel doc, I click on the button that triggers Sub CoreLocLink(). The code here seems to fail at line 22. While investigating, I noticed that if I replace the variables with the actual values, the code progresses. I have made sure that all variables are declared as Public and that both modules have an Option Explicit declaration at the beginging however still not working. Please help!!!! Import Core Module Option Explicit Sub CoreLocLink() 1 Dim wbCoreDCW As Workbook 2 Dim wbESMDCW As Workbook 3 Dim ArrCoreArray() As Variant 4 Dim MyPath As String 5 Dim MyCorePath As String 6 Dim LocationsDCWName As String 7 Dim LastLn As Integer 8 Dim ArrayCounter As Integer 9 Dim InnerLoop As Integer 10 Dim UnitType As String 11 Dim K As String 12 13 'Call StartProgram 14 15 Application.ScreenUpdating = False 16 On Error GoTo ErrorHandler 17 'Get the path of this worksheet 18 MyPath = ThisWorkbook.FullName 19 Set wbESMDCW = ThisWorkbook 20 MyCorePath = Left(MyPath, Len(StrReverse(MyPath)) - InStr(1, StrReverse(MyPath), "\")) 21 'Get the locations DCW Name from the named range CoreLoc 22 LocationsDCWName = Range(ThisWorkbook.Names ("CoreLoc").RefersTo).Value 23 'Get the last line of location Display on the DCW 24 Application.ScreenUpdating = False 25 Windows(LocationsDCWName).Activate 26 Sheets(glCoreLocSheet).Select 27 LastLn = Cells(Rows.Count, glCoreLocDescCol).End(xlUp).Row 28 'Set the Array to be 2 dimensional, with 3 columns and as many 29 'rows as there are lines on the Core DCW. 30 ReDim ArrCoreArray(LastLn, 2) 31 'Loop through each row on the locations DCW if 32 'there is data there 33 If LastLn glCoreLocStRow Then 34 ArrayCounter = 0 35 InnerLoop = glCoreLocStRow + 1 36 Do 37 'Populate the Facility Code 38 ArrCoreArray(ArrayCounter, 0) = Cells(InnerLoop, glCoreLocNACSCol).Value 39 Do 40 UnitType = Cells(InnerLoop, glCoreLocAmbCol).Value 41 'If the location for this line is Ambulatory, then populate the array 42 If Trim(UnitType) = "Ambulatory Care Area" Or Trim (UnitType) = "Nurse Ward" Then 43 ArrCoreArray(ArrayCounter, 1) = Cells(InnerLoop, glCoreLocDescCol).Value 44 ArrCoreArray(ArrayCounter, 2) = Cells(InnerLoop, glCoreLocDispCol).Value 45 ArrayCounter = ArrayCounter + 1 46 End If 47 InnerLoop = InnerLoop + 1 48 'Check if we've changed NACS code 49 Range("A" & InnerLoop).Select 50 K = Cells(InnerLoop, glCoreLocNACSCol).Value 51 Loop Until K < "" Or InnerLoop = LastLn + 1 52 Loop Until InnerLoop = LastLn + 1 53 If ArrCoreArray(ArrayCounter, 2) = "" Then 54 ArrCoreArray(ArrayCounter, 0) = "" 55 End If 56 57 'The Target Data is now in the array, ready to be written to the destination document 58 59 wbESMDCW.Sheets(glSchLocSheet).Activate 60 Range(Cells(glSchLocStRow + 1, glSchLocFacCodeCol), Cells (LastLn + 1, glSchLocAmbDispCol)).Value 61= ArrCoreArray() 62 End If 63 Application.ScreenUpdating = True 64 Application.EnableEvents = True 65 Exit Sub 66ErrorHandler: 67 MsgBox ("There has been an error. Please check that a valid Core DCW name is entered on the Reference 68Data Tab, and that this document is open on your computer") 69 Application.ScreenUpdating = True 70 Application.EnableEvents = True 71End Sub --------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------- StartProgram Module Option Explicit 'Need to declare a "Design Mode" flag to identify if we're trying to 'insert columns to the sheet. Public glDesignMode As Boolean 'Now need to declare Sheets Public glRefDataSheet As String Public glPersonnelSheet As String Public glSchLocSheet As String Public glBooksSheet As String Public glSlotSheet As String Public glTemplateSheet As String Public glApptTypeSheet As String Public glPrepsSheet As String Public glGuideSheet As String Public glApptSetSheet As String Public glgroupSheet As String Public glCABSheet As String Public glPickLstSheet As String 'Declare globals to store sheet start Rows Public glRefDataStRow As Integer Public glPersonnelStRow As Integer Public glSchLocStRow As Integer Public glBooksStRow As Integer Public glSlotStRow As Integer Public glTemplateStRow As Integer Public glApptTypeStRow As Integer Public glPrepsStRow As Integer Public glGuideStRow As Integer Public glApptSetStRow As Integer Public glgroupStRow As Integer Public glCABStRow As Integer Public glPickLstStRow As Integer 'Declare globals for the columns that we need to reference in the code Public glRefDataValCol As Integer Public glPersonnelLNCol As Integer Public glPersonnelFNCol As Integer Public glPersonnelMNCol As Integer Public glPersonnelRoleCol As Integer Public glPersonnelFullCol As Integer Public glSchLocSchFacIdCol As Integer Public glSchLocFacCodeCol As Integer Public glSchLocAmbDispCol As Integer Public glSchLocAmbDescCol As Integer Public glBooksResCol As Integer Public glBooksModCol As Integer Public glSlotsModCol As Integer Public glTemplateResCol As Integer Public glTemplateNmCol As Integer Public glTemplateSlotCol As Integer Public glTemplateDayBCol As Integer Public glTemplateDayECol As Integer Public glTemplateSlotSCol As Integer Public glTemplateSlotECol As Integer Public glTemplateModCol As Integer Public glTemplateSlotResCol As Integer Public glApptTypeNmCol As Integer Public glApptTypeModCol As Integer Public glPrepsModCol As Integer Public glGuideModCol As Integer Public glApptSetPrepCol As Integer Public glApptSetPostCol As Integer Public glApptSetModCol As Integer Public glGroupModCol As Integer Public glCABModCol As Integer Public glPickLstFacIdCol As Integer Public glPickLstSiteCdCol As Integer Public glPickLstNACSCol As Integer Public glPickLstApptCol As Integer Public glPickLstTrtFnCol As Integer Public glPickLstMnSpecCol As Integer 'Declare Globals to represent the Core Locations DCW information Public glCoreLocNACSCol As Integer Public glCoreLocDispCol As Integer Public glCoreLocDescCol As Integer Public glCoreLocAmbCol As Integer Public glCoreLocStRow As Integer Public glCoreLocSheet As String 'Declare Globals to represent the Core Personnel DCW information Public glCorePerSheet As String Public glCorePerMenSheet As String Public glCorePerExIdCol As Integer Public glCorePerPosCol As Integer Public glCorePerLNCol As Integer Public glCorePerFNCol As Integer Public glCorePerMNCol As Integer Public glCorePerActiveIndCol As Integer ' The column in the personnel DCW that holds the active indicator: 0 or 1 Public glCorePerModificationCol As Integer ' The column in the personnel DCW that holds the modification flag: Addition, Modification, Deletion Public glCorePerStRow As Integer Sub StartSetColumns() ''================================================ ======= '' Program: SetColumns '' Desc: This routine runs upon opening the spreadsheet, it stores the '' the startrows and columns that are needed for the other macros to '' run robustly. ''================================================ ======= 'Please set this flag to true if you are adding columns to the DCW. glDesignMode = False 'In case this has been called from a worksheetChange we need to turn off screen flicker and events 'and store the sheet to return to. Dim ReturnSheet As Worksheet Application.ScreenUpdating = False Application.EnableEvents = False Set ReturnSheet = ThisWorkbook.ActiveSheet 'Set the names of the sheets glRefDataSheet = "DCW Reference Data" glPersonnelSheet = "Personnel For Scheduling" glSchLocSheet = "Locations for Scheduling" glBooksSheet = "Bookshelf and Resources" glSlotSheet = "Appointment Slots" glTemplateSheet = "Default Schedules - Templates" glApptTypeSheet = "Appt Types & Resources" glPrepsSheet = "Person Preparations or Post " glGuideSheet = "Scheduling Guidelines" glApptSetSheet = "Appointment Type Settings" glgroupSheet = "Group Appts" glCABSheet = "Choose and Book" glPickLstSheet = "Pick Lists" 'set the names of the title row columns glRefDataStRow = 3 glPersonnelStRow = 3 glSchLocStRow = 3 glBooksStRow = 3 glSlotStRow = 3 glTemplateStRow = 3 glApptTypeStRow = 3 glPrepsStRow = 3 glGuideStRow = 3 glApptSetStRow = 3 glgroupStRow = 3 glCABStRow = 3 glPickLstStRow = 1 'Declare strings to represent the titles of these columns 'These will be used to find the correct column Dim glRefDataVal As String Dim glPersonnelLN As String Dim glPersonnelFN As String Dim glPersonnelMN As String Dim glPersonnelRole As String Dim glPersonnelFull As String Dim glPersonnelModification As String Dim glPersonnelActive As String Dim glSchLocSchFacId As String Dim glSchLocFacCode As String Dim glSchLocAmbDisp As String Dim glSchLocAmbDesc As String Dim glBooksRes As String Dim glSchMod As String Dim glTemplateRes As String Dim glTemplateNm As String Dim glTemplateSlot As String Dim glTemplateDayB As String Dim glTemplateDayE As String Dim glTemplateSlotS As String Dim glTemplateSlotE As String Dim glTemplateSlotRes As String Dim glApptTypeNm As String Dim glApptSetPrep As String Dim glApptSetPost As String Dim glApptSetMid As String Dim glGroupMod As String Dim glCABMod As String Dim glPickLstFacId As String Dim glPickLstSiteCd As String Dim glPickLstNACS As String Dim glPickLstAppt As String Dim glPickLstTrtFn As String Dim glPickLstMnSpec As String 'Set these to represent the column names glRefDataVal = "Value" glPersonnelLN = "Last Name" glPersonnelFN = "First Name" glPersonnelMN = "Middle Name" glPersonnelRole = "Role" glPersonnelFull = "Full Name" glPersonnelActive = "Active Indicator" glPersonnelModification = "Modifications" glSchLocSchFacId = "Scheduling Facility Identifier" glSchLocFacCode = "Facility Code" glSchLocAmbDesc = "Ambulatory Location" glSchLocAmbDisp = "Amb Location Display" glBooksRes = "Resource Display" glSchMod = "Modified?" glTemplateRes = "Resource(s)" glTemplateNm = "Template Name" glTemplateSlot = "Slot Type" glTemplateDayB = "Day Begin" glTemplateDayE = "Day End" glTemplateSlotS = "Slot Start Time" glTemplateSlotE = "Slot End Time" glTemplateSlotRes = "Resource(s)Slot Type" glApptTypeNm = "Appointment Type Name" glApptSetPrep = "Person Prep Name" glApptSetPost = "Post Instruction Name" glGroupMod = "Modified?" glPickLstFacId = "Facility Identifier" glPickLstSiteCd = "Site Code" glPickLstNACS = "NACS Code" glPickLstAppt = "Appointment Types" glPickLstTrtFn = "Treatment Function" glPickLstMnSpec = "Main Specialty" 'Set the Core Values here - These will be used in the CoreLink Sub routine. glCoreLocSheet = "Core Locations" glCorePerSheet = "Personnel" glCorePerMenSheet = "Community Personnel" glCoreLocNACSCol = 1 glCoreLocDispCol = 10 glCoreLocDescCol = 9 glCoreLocAmbCol = 8 glCoreLocStRow = 5 glCoreLocStRow = 5 glCorePerExIdCol = 1 glCorePerPosCol = 8 glCorePerLNCol = 19 glCorePerFNCol = 20 glCorePerMNCol = 21 glCorePerStRow = 5 glCorePerActiveIndCol = 12 glCorePerModificationCol = 83 'Set these variables to reflect the columns in the DCW Application.EnableEvents = False 'Declare globals for the columns that we need to reference in the code glRefDataValCol = FindColumn(glRefDataVal, glRefDataSheet, glRefDataStRow) glPersonnelLNCol = FindColumn(glPersonnelLN, "Personnel For Scheduling", glPersonnelStRow) glPersonnelFNCol = FindColumn(glPersonnelFN, glPersonnelSheet, glPersonnelStRow) glPersonnelMNCol = FindColumn(glPersonnelMN, glPersonnelSheet, glPersonnelStRow) glPersonnelRoleCol = FindColumn(glPersonnelRole, glPersonnelSheet, glPersonnelStRow) glPersonnelFullCol = FindColumn(glPersonnelFull, glPersonnelSheet, glPersonnelStRow) glSchLocSchFacIdCol = FindColumn(glSchLocSchFacId, glSchLocSheet, glSchLocStRow) glSchLocFacCodeCol = FindColumn(glSchLocFacCode, glSchLocSheet, glSchLocStRow) glSchLocAmbDispCol = FindColumn(glSchLocAmbDisp, glSchLocSheet, glSchLocStRow) glSchLocAmbDescCol = FindColumn(glSchLocAmbDesc, glSchLocSheet, glSchLocStRow) 'glBooksResCol = FindColumn(glBooksRes, glBooksSheet, glBooksStRow) 'glBooksModCol = FindColumn(glSchMod, glBooksSheet, glBooksStRow) 'glSlotsModCol = FindColumn(glSchMod, glSlotSheet, glSlotStRow) 'glTemplateResCol = FindColumn(glTemplateRes, glTemplateSheet, glTemplateStRow) 'glTemplateNmCol = FindColumn(glTemplateNm, glTemplateSheet, glTemplateStRow) 'glTemplateSlotCol = FindColumn(glTemplateSlot, glTemplateSheet, glTemplateStRow) 'glTemplateDayBCol = FindColumn(glTemplateDayB, glTemplateSheet, glTemplateStRow) 'glTemplateDayECol = FindColumn(glTemplateDayE, glTemplateSheet, glTemplateStRow) 'glTemplateSlotSCol = FindColumn(glTemplateSlotS, glTemplateSheet, glTemplateStRow) 'glTemplateSlotECol = FindColumn(glTemplateSlotE, glTemplateSheet, glTemplateStRow) 'glTemplateModCol = FindColumn(glSchMod, glTemplateSheet, glTemplateStRow) 'glTemplateSlotResCol = FindColumn(glTemplateSlotRes, glTemplateSheet, glTemplateStRow) 'glApptTypeNmCol = FindColumn(glApptTypeNm, glApptTypeSheet, glApptTypeStRow) 'glApptTypeModCol = FindColumn(glSchMod, glApptTypeSheet, glApptTypeStRow) 'glPrepsModCol = FindColumn(glSchMod, glPrepsSheet, glPrepsStRow) 'glGuideModCol = FindColumn(glSchMod, glGuideSheet, glGuideStRow) 'glApptSetPrepCol = FindColumn(glApptSetPrep, glApptSetSheet, glApptSetStRow) 'glApptSetPostCol = FindColumn(glApptSetPost, glApptSetSheet, glApptSetStRow) 'glApptSetModCol = FindColumn(glSchMod, glApptSetSheet, glApptSetStRow) 'glGroupModCol = FindColumn(glGroupMod, glgroupSheet, glgroupStRow) 'glCABModCol = FindColumn(glSchMod, glCABSheet, glCABStRow) 'glPickLstFacIdCol = FindColumn(glPickLstFacId, glPickLstSheet, glPickLstStRow) 'glPickLstSiteCdCol = FindColumn(glPickLstSiteCd, glPickLstSheet, glPickLstStRow) 'glPickLstNACSCol = FindColumn(glPickLstNACS, glPickLstSheet, glPickLstStRow) 'glPickLstApptCol = FindColumn(glPickLstAppt, glPickLstSheet, glPickLstStRow) 'glPickLstTrtFnCol = FindColumn(glPickLstTrtFn, glPickLstSheet, glPickLstStRow) 'glPickLstMnSpecCol = FindColumn(glPickLstMnSpec, glPickLstSheet, glPickLstStRow) ReturnSheet.Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub --------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables in Module vs. Public | Excel Discussion (Misc queries) | |||
sharing variables between form and module code | Excel Programming | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
Run worksheet module code from workbook module? | Excel Programming | |||
How to reference a public array (declared in module) from a proced | Excel Programming |