Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Subroutines from sub-routine
I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned to
the CLICK event of their own button. The first subroutine performs a pass-thru extraction from a SQL database. There is a SUMMARY sheet in the workbook that then does some calculations on the data that is extracted using the DCount and DCountA functions into specific cells on the SUMMARY sheet. The second sub-routine copies the resultant values calculated using the DCount and DCountA functions onto another workbook that I'm going to use to generate a trend chart. If I click the first button and then the second button, every thing works as I'd expect. What I want to do is call the second sub-routine as the last line in the first sub-routine. When I do this the first sub-routine works the same but the second sub-routine puts zeroes instead of the values from the SUMMARY sheet. Help... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Subroutines from sub-routine
Post the before and after code, what works run independently, and how you
merged them. -- __________________________________ HTH Bob "Jeff" wrote in message ... I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned to the CLICK event of their own button. The first subroutine performs a pass-thru extraction from a SQL database. There is a SUMMARY sheet in the workbook that then does some calculations on the data that is extracted using the DCount and DCountA functions into specific cells on the SUMMARY sheet. The second sub-routine copies the resultant values calculated using the DCount and DCountA functions onto another workbook that I'm going to use to generate a trend chart. If I click the first button and then the second button, every thing works as I'd expect. What I want to do is call the second sub-routine as the last line in the first sub-routine. When I do this the first sub-routine works the same but the second sub-routine puts zeroes instead of the values from the SUMMARY sheet. Help... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Subroutines from sub-routine
It works when I run them independently but not this way.
Sub Get_Data() Worksheets("PlaterMetricsData").Range("A1").Select With ActiveSheet.QueryTables.Add(insert args here) .CommandText = "EXEC ...." .Name = "PlaterData" .Refresh End With Populate_CurrentDataTable Worksheets("Summary").Select End Sub Sub Populate_CurrentDataTable() Dim dteDate As Date, iShift3Bars As Integer, iShift3Cycles As Integer dteDate = Worksheets("Summary").Range("C1").Value Worksheets("Summary").Select iShift3Bars = Worksheets("Summary").Range("M5").Value iShift3Cycles = Worksheets("Summary").Range("M6").Value If Worksheets("Summary").Range("F1").Value = 1 Then Worksheets("CycleInfoSQL").Select ActiveSheet.Range("A3").Select Do While IsEmpty(ActiveCell) = False ActiveCell.Offset(1, 0).Select Loop ActiveCell.Value = dteDate ActiveCell.Offset(0, 1).Value = iShift3Bars ActiveCell.Offset(0, 2).Value = iShift3Cycles End If Worksheets("Summary").Select End Sub "Bob Phillips" wrote: Post the before and after code, what works run independently, and how you merged them. -- __________________________________ HTH Bob "Jeff" wrote in message ... I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned to the CLICK event of their own button. The first subroutine performs a pass-thru extraction from a SQL database. There is a SUMMARY sheet in the workbook that then does some calculations on the data that is extracted using the DCount and DCountA functions into specific cells on the SUMMARY sheet. The second sub-routine copies the resultant values calculated using the DCount and DCountA functions onto another workbook that I'm going to use to generate a trend chart. If I click the first button and then the second button, every thing works as I'd expect. What I want to do is call the second sub-routine as the last line in the first sub-routine. When I do this the first sub-routine works the same but the second sub-routine puts zeroes instead of the values from the SUMMARY sheet. Help... . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Subroutines from sub-routine
step into Get_Data
set the next step at the line Populate_CurrentDataTable then step (F8) ...is there an error message? Are these two procedures in a standard module? If they're on a sheet code page, are they on the same page? "Jeff" wrote: It works when I run them independently but not this way. Sub Get_Data() Worksheets("PlaterMetricsData").Range("A1").Select With ActiveSheet.QueryTables.Add(insert args here) .CommandText = "EXEC ...." .Name = "PlaterData" .Refresh End With Populate_CurrentDataTable Worksheets("Summary").Select End Sub Sub Populate_CurrentDataTable() Dim dteDate As Date, iShift3Bars As Integer, iShift3Cycles As Integer dteDate = Worksheets("Summary").Range("C1").Value Worksheets("Summary").Select iShift3Bars = Worksheets("Summary").Range("M5").Value iShift3Cycles = Worksheets("Summary").Range("M6").Value If Worksheets("Summary").Range("F1").Value = 1 Then Worksheets("CycleInfoSQL").Select ActiveSheet.Range("A3").Select Do While IsEmpty(ActiveCell) = False ActiveCell.Offset(1, 0).Select Loop ActiveCell.Value = dteDate ActiveCell.Offset(0, 1).Value = iShift3Bars ActiveCell.Offset(0, 2).Value = iShift3Cycles End If Worksheets("Summary").Select End Sub "Bob Phillips" wrote: Post the before and after code, what works run independently, and how you merged them. -- __________________________________ HTH Bob "Jeff" wrote in message ... I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned to the CLICK event of their own button. The first subroutine performs a pass-thru extraction from a SQL database. There is a SUMMARY sheet in the workbook that then does some calculations on the data that is extracted using the DCount and DCountA functions into specific cells on the SUMMARY sheet. The second sub-routine copies the resultant values calculated using the DCount and DCountA functions onto another workbook that I'm going to use to generate a trend chart. If I click the first button and then the second button, every thing works as I'd expect. What I want to do is call the second sub-routine as the last line in the first sub-routine. When I do this the first sub-routine works the same but the second sub-routine puts zeroes instead of the values from the SUMMARY sheet. Help... . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Subroutines from sub-routine
When I put a Breakpoint in the code and then run it; subsequently, stepping
(F8) through the code - including following the call statement into the next routine, everything works fine. When I remove the breakpoint and let it run un-interrupted...it puts zeroes in all the fields. It's almost like the source data isn't getting updated or something and when I manually cause the code to stop, it works. "Patrick Molloy" wrote: step into Get_Data set the next step at the line Populate_CurrentDataTable then step (F8) ...is there an error message? Are these two procedures in a standard module? If they're on a sheet code page, are they on the same page? "Jeff" wrote: It works when I run them independently but not this way. Sub Get_Data() Worksheets("PlaterMetricsData").Range("A1").Select With ActiveSheet.QueryTables.Add(insert args here) .CommandText = "EXEC ...." .Name = "PlaterData" .Refresh End With Populate_CurrentDataTable Worksheets("Summary").Select End Sub Sub Populate_CurrentDataTable() Dim dteDate As Date, iShift3Bars As Integer, iShift3Cycles As Integer dteDate = Worksheets("Summary").Range("C1").Value Worksheets("Summary").Select iShift3Bars = Worksheets("Summary").Range("M5").Value iShift3Cycles = Worksheets("Summary").Range("M6").Value If Worksheets("Summary").Range("F1").Value = 1 Then Worksheets("CycleInfoSQL").Select ActiveSheet.Range("A3").Select Do While IsEmpty(ActiveCell) = False ActiveCell.Offset(1, 0).Select Loop ActiveCell.Value = dteDate ActiveCell.Offset(0, 1).Value = iShift3Bars ActiveCell.Offset(0, 2).Value = iShift3Cycles End If Worksheets("Summary").Select End Sub "Bob Phillips" wrote: Post the before and after code, what works run independently, and how you merged them. -- __________________________________ HTH Bob "Jeff" wrote in message ... I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned to the CLICK event of their own button. The first subroutine performs a pass-thru extraction from a SQL database. There is a SUMMARY sheet in the workbook that then does some calculations on the data that is extracted using the DCount and DCountA functions into specific cells on the SUMMARY sheet. The second sub-routine copies the resultant values calculated using the DCount and DCountA functions onto another workbook that I'm going to use to generate a trend chart. If I click the first button and then the second button, every thing works as I'd expect. What I want to do is call the second sub-routine as the last line in the first sub-routine. When I do this the first sub-routine works the same but the second sub-routine puts zeroes instead of the values from the SUMMARY sheet. Help... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling subroutines and passing variables | Excel Programming | |||
Calling subroutines and passing variables | Excel Programming | |||
Calling subroutines from Class Modules | Excel Programming | |||
Calling Add-In subroutines from other modules | Excel Programming | |||
Calling Subroutines/User Functions from Macros | Excel Programming |