calling it a night. at this pace I'll be able to commit in 1-2 "shifts"
going to be interesting to see how ANTLR handles multiline signatures and declarations. I think tweaking the code to work with ANTLR automagically fixes the issue with multiline instructions.
The following line:
ThisWorkbook.Sheets(vSheets(i, 1)).Visible = xlSheetVisible
generates the error:
Run-time error '-2147417848 (80010108)':
Method 'Visible' of object '_Worksheet' failed
But if I click play in the debugger, execution finishes without a problem. So the line causes my code...
@Amejel I know a ton of analysts in the food (consumer packaged goods industry) that swear by it. But they have other responsibilities as well (VBA is just a part of it).
I'm doing ISO 9001 with this company. The easiest way to collect DATA as it turns out people do not like hassle of papersheets, is to do it with ADODB and Excel.
Consider the following code in VBA.
Sub CompileTest()
Dim Ap As Application
Dim Wb As Workbook
Dim Ws As Worksheet
Debug.Print Ap.XXX ' No compiler error
Debug.Print Wb.XXX ' No compiler error
Debug.Print Ws.XXX ' Compiler error
End Sub
When I compile this, I don't g...
On any given line in my code, can I use Debug.Print() together with some other command to find out what would happen if my code would encounter an error on that line?
Can I use any statement within Debug.Print to find out if the current error handling procedure is to Goto 0, Resume Next or Goto ...
The following line:
ThisWorkbook.Sheets(vSheets(i, 1)).Visible = xlSheetVisible
generates the error:
Run-time error '-2147417848 (80010108)':
Method 'Visible' of object '_Worksheet' failed
But if I click play in the debugger, execution finishes without a problem. So the line causes my code...
Public Sub VBACompilerIsMad()
Dim app As Application
Debug.Print app.ThisMemberDoesNotExist
Dim wb As Workbook
Debug.Print wb.ThisMemberDoesNotExist
End Sub
Consider the following code:
Public Sub VBACompilerIsMad()
Dim app As Application
Debug.Print app.ThisMemberDoesNotExist
Dim wb As Workbook
Debug.Print wb.ThisMemberDoesNotExist
End Sub
When I compile this, I don't get a compiler error for referring to an inexisting member o...
Good afternoon,
I am looking to make this macro run faster and more efficiently (less code).
I have a weekly report of control charts that was handed down to me and I am looking to automate it. Pulling in the data is a manual task but changing the ranges for each Chart Series is the part I am ...
Well, it's just a hypothesis, but here it is:
I recall that you can call a stored procedure on an ADODB.Connection object like a native method.
(The examples for this on several msdn sites look oddly messed up, no idea why).
So there is a mechanism of something like 'anonymous/dynamic methods' i...
I am looking to make this macro run faster and more efficiently (less code).
I have a weekly report of control charts that was handed down to me and I am looking to automate it. Pulling in the data is a manual task but changing the ranges for each Chart Series is the part I am automating. I ha...
they're looking at the ActiveSheet and work off the default members, making it hard-to-parse code, but I don't think it's broken... depends on what the data looks like, but it could work I think.
I think this post could benefit a screenshot showing some sample data; it would make it easier to understand what's special about C3 & C4. — Mat's Mug7 secs ago
@Mat'sMug @Vogel612 Those would work if there is a number value in for example Cells(3, 3) since that will return the default .Value although he doesn't specify.
The only problem is that the code works, but not as intended - if your code doesn't do what you want it to be doing, then it's not ready for a peer review (and trust me, it needs one! ;)... you need to pass a Worksheet object as a parameter to your procedure, and then have some code that will iterate the sheets you're interested in, and call the above Sub with the worksheet parameter. On this site answers won't change what the code is doing, we'll give you hints on what to change to better how the code is doing what it does. Asking to change what the code does is off-topic :( — Mat's Mug23 secs ago
@RubberDuck it's a shame to have vote to close this question.
@200_success @Jamal (@rolfl?) is it really asking to change what the code is doing? Would it be off-topic if the OP was edited to say "I have this code, it works well against the active sheet. How could it be made more extensible, so that I could reuse it against any worksheet?"
FWIW, I've retracted my close vote. This is working code in need for a peer review, with an opportunity for extensibility - it's just a matter of defining what exactly is "works as intended" in this case; it does work, only it requires a manual process. I will post an answer later, for sure! — Mat's Mug4 mins ago
@RubberDuck that depends solely on interpretation of the OP. I'm leaving it open.
ugh. OP edited the post......... and gives your close vote some weight @RubberDuck
VBA questions that meet the Stack Overflow criteria should be posted on Stack Overflow or migrated to Stack Overflow. Proper Stack Overflow criteria would include either an attempt at the code with a specific error, or a functionality question of a specific object or method (hopefully with code ...
@RubberDuck I need some help here... I have two String SSIS variables, and I'm trying to populate them with the result of this query, using an "Execute SQL Task" (Control Flow):
SELECT cast(MIN(CalendarDate) as varchar) MinDate, cast(MAX(CalendarDate) as varchar) MaxDate FROM dwd.FiscalCalendars;
if I make them dates and remove the cast, I get a date in december 1899 (0); so I figured I'd use strings... and I get nothing, the variables remain empty. WTF?
ugh. I expected the "Variables" list to show me their values... so I sent them into a bogus table with another SQL Task... and I see the correct values there. I guess it's fine then. Casting to varchar because struggling to make it work with dates
oh damn
it works if I cast the datetime values to.... datetime
wow
that's plain stupid
SELECT cast(MIN(CalendarDate) as datetime) MinDate, cast(MAX(CalendarDate) as datetime) MaxDate FROM dwd.FiscalCalendars;
if I don't cast it, SSIS sees the two values as strings...
DQ rule #1 filters for a specific company code (basically filters out test data); #2 rejects orders with an order date before the first date in the fiscal calendars table; #3 rejects orders with an order date after the last date in the fiscal calendars table, and #4 rejects order headers without an order number - all that is defined and documented in the [DQ] database. I'll use this information to produce meta-reports and notifications :)
Consider the following code in VBA.
Sub CompileTest()
Dim Ap As Application
Dim Wb As Workbook
Dim Ws As Worksheet
Debug.Print Ap.XXX ' No compiler error
Debug.Print Wb.XXX ' No compiler error
Debug.Print Ws.XXX ' Compiler error
End Sub
When I compile this, I don't g...
Good afternoon,
I am looking to make this macro run faster and more efficiently (less code).
I have a weekly report of control charts that was handed down to me and I am looking to automate it. Pulling in the data is a manual task but changing the ranges for each Chart Series is the part I am ...
VBA Rubberducking
VBA Rubberducking