Calling Multiple Subs within A Sub - Having Issues

[dlssargent]

I have been working on a project and I have ALL the various task/sub/functions that I must execute complete. I am trying to call various public subs from one public sub I am hoping ties everthing together; however, the subs/functions within the main sub are not responding to the call from the main function. IF i step through the MAIN sub the using F8 the subs individual sub work fine.

For Example: I have three subs below. Sub C uses A and B, if I step through C using F8 everything works fine, but if I go Sub C and run it the sub doesn't work completely. What would be the rationale for this, is there something with Excel's vba that I am unaware of. THanks in advance.

A.
Public Sub Get_DemandData_Char()
Dim longDemLast_Row As Long
Dim Worksheet_DemandSheet As Worksheet
Dim range_DemandSheet As Range
Application.ScreenUpdating = False
Set Worksheet_DemandSheet = ThisWorkbook.Worksheets("Optimization tool download")
longDemLast_Row = IIf(IsEmpty(Worksheet_DemandSheet.Range("A65536")), Worksheet_DemandSheet.Range("A65536").End(xlUp).Row, 65536)
Set range_DemandSheet = Worksheet_DemandSheet.Range("A1:AK" & longDemLast_Row)
ActiveWorkbook.Names.Add Name:="Data_D", RefersTo:=range_DemandSheet
Set Worksheet_DemandSheet = Nothing
Set range_DemandSheet = Nothing
Call Get_TranslationData_Char
End Sub


B.
Public Sub Get_TranslationData_Char()
Dim longTransLast_Row As Long
Dim Worksheet_TranslateSheet As Worksheet
Dim range_TranslateSheet As Range
Set Worksheet_TranslateSheet = ThisWorkbook.Worksheets("Translation Matrix")
longTransLast_Row = IIf(IsEmpty(Worksheet_TranslateSheet.Range("A65536")), Worksheet_TranslateSheet.Range("A65536").End(xlUp).Row, 65536)
Set range_TranslateSheet = Worksheet_TranslateSheet.Range("A3:E" & longTransLast_Row)
ActiveWorkbook.Names.Add Name:="Data_T", RefersTo:=range_TranslateSheet
Set Worksheet_TranslateSheet = Nothing
Set range_TranslateSheet = Nothing
End Sub


C.
Sub FindInconsisBWCharliandSAP()
Call Get_DemandData_Char
Dim Demand_TranslateQuery As QueryTable
Dim wksSheet As Worksheet
Dim strConnect As String
Dim strSql As String
Dim rowCount As Integer
Dim rangeSpec As String
Set wksSheet = ThisWorkbook.Sheets("SAPDiscrep")
rowCount = wksSheet.UsedRange.Rows.Count
rangeSpec = "A1:" & "E" & rowCount
wksSheet.Range(rangeSpec).ClearContents
strSql = "SELECT Data_D.Material, Data_D.IntMatDesc, Data_T.SAPNUM FROM Data_D Left JOIN Data_T ON Data_D.Material = Data_T.SAPNUM "
strSql = strSql + "Where(Data_T.SAPNUM is Null) GROUP BY Data_D.Material, Data_D.IntMatDesc,Data_T.SAPNUM;"
strConnect = "ODBC;Driver={Microsoft Excel Driver (*.xls)};DBQ=" & ThisWorkbook.FullName
strConnect = strConnect & ";DriverId=790;DefaultDir=" & ThisWorkbook.Path
If wksSheet.QueryTables.Count > 0 Then wksSheet.QueryTables(1).Delete
wksSheet.Cells.Clear
Set Demand_TranslateQuery = wksSheet.QueryTables.Add(strConnect, wksSheet.Range("A1"), strSql)
Demand_TranslateQuery.Refresh (True)
Set Demand_TranslateQuery = Nothing
Application.ScreenUpdating = True
With WorksheetFunction
If .CountA(wksSheet.Cells) > 1 Then
MsgBox "There was discrepancy between SAP upload and translation matrix. Scroll to the right for SAPDiscrep tab"
End With
End Sub

[mdmackillop]

I came across something similar a while ago. It seemed that excel was not doing something "fast enough". In this case, it might be something like Data-T is not "complete" before the code tries to use it.
Try adding some debug eg

Debug.print Range("Data_T").cells.count
to pin down the problem. Maybe you could then add a delaying loop until the item exists.

[Bob Phillips]

Try calling the subs using Ontime, with a second delay, or even no delay, like so



Application.OnTime Now + TimeSerial(0, 0, 0), "Get_TranslationData_Char"

[dlssargent]

That did not work? Any other suggestions? Why would "Call" not work?

Thanks in advance