VBA code in one Module referencing Public Variables Declared inanother Module failing

[Khurram]

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

 

[Joel]

Names is a worksheet object not a workbook object. try adding the sheet name.

LocationsDCWName = Range(ThisWorkbook.Sheets("Sheet1").Names
("CoreLoc").RefersTo).Value

 

[Dave Peterson]

Names can belong to either a worksheet or a workbook. They can be local or
global.

 

[Dave Peterson]

I'd try replacing this:

22 LocationsDCWName = Range(ThisWorkbook.Names("CoreLoc").RefersTo).Value
with
22 LocationsDCWName = ThisWorkbook.Names("CoreLoc").RefersToRange.Value

Actually, since you know where that range is:
("CoreLoc" is defined as ='DCW Reference Data'!$B$4)

I'd use:

LocationsDCWName _
= thisworkbook.worksheets("DCW reference data").range("CoreLoc").Value

I find this syntax easier to understand and easier to debug when things go
wrong.

 

[Deecrypt]

Thanks guys,
Using the code "LocationsDCWName = ThisWorkbook.Worksheets("DCW
reference data").Range("CoreLoc").Value", provided by yourselves, I
was able to get past Line 22. Incidentally just to let you know, I
turned off the interbal Error Handler in the code to see what error
Excel was throwing when I was getting stuck at line 22. This was
"Runtime error "1004': Method 'Range' of object '_Global' failed".

My script however now is failing at line 26 which is "Sheets
(glCoreLocSheet).Select". It is a simple selection of a worksheet in
the target Excel document (where I am importind data from).
glCoreLocSheet has been defined in the StartProgram Module and has the
value Core Locations.

Turning the Error Handling code off is giving me the following Excel
VBA error "Runtime Error '9': Subscript out of Range". If I type in
the value of the sheet directly in the code i.e "Sheets("Core
Locations").Select", I can get past the code but there are several
variables defined in the StartProgram Module and it make the code a
lot messy. I tried to copy all the variables and place them in the
same module as the Sub I am running but same error shows. It seems
that I am unable to access these variable in my Sub which apparetly
was not a problem in the past.

Thank you for your help again!!!

 

[Deecrypt]

Figured out the issue. Although the variables being used in the
StartProgram module were initiated perfectly fine, the values being
assigned to them via the "Sub StartSetColumns()" were not infact not
being assigned. This is because this sub should have been running at
everytime the workbook opens however the code for it was not in the
ThisWorkbook object which can be found via the VBE. The code was
either missed or not copied over from the pervious version of this
document. Below is that code just for completness sake. Thank you
for you help.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Call ShowAllSheets
Call StartSetColumns
Call DisableCopyCutAndPaste
Call FirstSheet
Application.ScreenUpdating = True
End Sub


Kind regards