• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code works only in debugmode

J

jits50

New Member
I connect to sql server and run a stored proc. The output is pulled into worksheet "pullsql". From there using vba code I copy pulled info into other worksheets. When in debug mode all works fine. But in run mode the copy into other worksheets does not happen.
 
Can we see a copy of your code? Perhaps you are executing a commands too quickly, and query is not having time to actually run before macro finishes?
 
[pre]
Code: 
Sub putval()
Dim Vol_loc, Price_loc, Multi_loc
Dim lsheet As Worksheet
Dim Elem_Vol As Double
Dim Analysis_Period, Multi_Acct As String
Dim Elem_Price As Double
Dim Ctr, Vol_Prd_Ctr, OldSheet, NewSheet As Integer

With ActiveWorkbook.Connections("RPM_Server").OLEDBConnection
.CommandText = "EXECUTE dbo.Key_Acct_Vol '" & Range("b3").Value & "'"
End With
ActiveWorkbook.Connections("RPM_Server").Refresh
' output is pulled in sheet23

For Ctr = 2 To 881
'    Vol_loc = "Sheet" & Range("U" & ctr).Value & "!" & Range("V" & ctr).Value
' Vol_loc takes location of volume cell for that element
Vol_loc = Sheet23.Range("C" & Ctr).Value
Price_loc = Sheet23.Range("D" & Ctr).Value
Multi_loc = "K" & Sheet23.Range("E" & Ctr).Value
'lsheet stores the sheet# of the product group for the element
NewSheet = Sheet23.Cells(Ctr, 2).Value
If OldSheet <> NewSheet Then
Set lsheet = ThisWorkbook.Sheets(Sheet23.Cells(Ctr, 2).Value)
OldSheet = NewSheet
End If
' Get volume and price
If Sheet23.Cells(Ctr, Vol_Prd_Ctr).Value <> "" Then
Elem_Vol = Sheet23.Cells(Ctr, Vol_Prd_Ctr).Value
If Analysis_Period = "Quarterly" Then
' Quarterly volume is calculated based on monthly volume
Elem_Vol = Elem_Vol * 3
End If
' Display volume and price in the respective sheets
lsheet.Range(Vol_loc).Value = Elem_Vol
Multi_Acct = Sheet23.Cells(Ctr, 9).Value
Elem_Price = Sheet23.Cells(Ctr, 8).Value
lsheet.Range(Price_loc).Value = Elem_Price
End If
Next

End Sub
[/pre]
 
hmm. for the most part, everything looks pretty good. you might try putting in some stops in the code, or some

[pre]
Code: 
debug.print <something>
lines in your code to help see what's going on. On a different note, you might want to look at your Dim statements, as I don't think you have them defined the way you want. For instance, this line:

[pre][code]Dim Analysis_Period, Multi_Acct As String
[/pre]
I think you meant to define both variables as a string. However, VB actually reads that as:

Dim Analysis_Period as Variant, Multi_Acct as String[/code][/pre]
Instead of grouping all the variables into the definition at end, only the last variable gets the definition.

http://www.cpearson.com/excel/DeclaringVariables.aspx
 
Hi, jits50!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


A first thing that raises an error is statement "Vol_loc = Sheet23.Range("C" & Ctr).Value": shouldn't it be "Vol_loc = Worksheets("Sheet23").Range("C" & Ctr).Value"?


Regards!
 
It is not showing error on that line. The place sheet23 where the data from the stored procedure is retrieved in, does not get refreshed while the script is running. Only after the script has ended sheet23 gets the new data. I tried putting delay code such as sleep and wait etc. But even after the delay when the code is encountered to read data from sheet23, there is nothing. Once the code is fully executed, sheet23 gets the output data. The funny thing is when I put a debug stop immediately after executing the stored proc, I do see the sheet23 with the new data. I assumed putting a 10 seconds delay would be more than enough. Instead it just shows "Connecting to datasource" on the status bar during the delay.
 
Hi, jits50!


As far as I can see, I won't be able to reproduce your exact case as I don't have the source, but despite of this a sample file would help a lot.


If it's not possible, please copy the definition of sheet23 object/variable run the step by step with F8, stop at the first line, and write down and copy its value.


Regards!
 
Hi ,


To add to what the others have posted , my comment is that I am unable to see how this code segment is working "fine" , even in debug mode , since there are quite a few statements which can result in errors !


Vol_Prd_Ctr is declared , but not defined ; as Luke has pointed out , Excel will take it as a Variant , and in the absence of any assignment , it will probably have a value Empty. With this , the statement :


Cells(Ctr, Vol_Prd_Ctr)


cannot work "fine".


I , for one , am interested to look at your workbook ; can you upload it ?


Narayan
 
You must log in or register to reply here.
Back
Top