Results 1 to 21 of 21

Thread: VBA Compile

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    VBA Compile

    HI,

    Newish to the forum....

    I'd like to intercept/catch the error message posted by debug/compile in VBA.

    Part of the problem is that it's not an error... debug.compile has been successful!

    Been googling for a couple of days now... :-(

    Can anyone help please?

    Very much TIA

    Lisa

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: VBA Compile

    Hi Lisa,

    Welcome to VBForums.

    You sort of posted in the wrong forum area. If you look, there's a forum here specifically for VBA related questions. Here's a link to it. Although, this VB6 forum is substantially more active.

    I do quite a bit of VBA programming, but I'm not sure I understand your question. My first thought is a screen-capture when the compile error comes up. But you say it compiled successfully.

    Are we talking about a runtime error? That's a bit different from a compiler error. To "catch" runtime errors you need to use the On Error Goto [label] or On Error Resume Next statements. That's where I'd start.

    Many will discourage the On Error Resume Next approach, as your program will just keep chugging along even though things might not be copacetic. However, I will admit that I occasionally use it.

    The On Error Goto [label] is actually much nicer. It allows you the opportunity to report to the user what went wrong, possibly with an opportunity to correct the problem.

    All The Best,
    Elroy

    EDIT1: Also, if you give us a few more specifics about the problem, I'm sure there are many of us who would jump in and try to help.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: VBA Compile

    It would help if you tell us what the error is you are talking about, the conditions under which it occurs and the section of code where the error occurs.
    Without more info we can only guess and give generic info.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Hi,

    Apologies for posting in the wrong place! Having said that it may be applicable to VB as well but I don't know that IDE. Does it have a debug option on the menu and does that have a compile option?

    Anyway... More detail.

    I create a new document, workbook, database, presentation, whatever.
    Go to the VBE
    Insert a module with Option Explicit
    Insert the following sub...

    Code:
    Option Explicit
    
    Sub subTest()
    slString = "Test"
    End Sub
    Now I click Debug / Compile

    Name:  vbforums 1.jpg
Views: 415
Size:  26.7 KB

    I get a compile error...

    Name:  vbforums 2.PNG
Views: 360
Size:  13.7 KB

    The whole point being that when running the procedure the VBE does a compile first and stops on any errors like this. Then you run again and stop on the next one then you run again and .... I think you might know what happens.

    I've managed to write code that will do a compile and when you press OK will comment out that line and do another compile so I get a report of all the compile errors in sort of one go... you just keep pressing return. Then do a CtrlH to remove all of the comments.

    But! I can't get what type of error. Just Line number Module name and the errant line text.

    I'd like to catch / grab / trap ... somehow get hold of... that message... "Variable not defined".

    It's not possible to do use an On Error clause because there isn't any error. The VBE is reporting correctly. On Error is for runtime anyway.

    I believe there must be a way because there are 3rd party programs that advertise being able to do this though I haven't tried any myself.

    Possibly with an API call.

    I hope this helps explain things.

    I have been googling for quite a while now and also trawled some forums including this one but not found a definitive answer.

    Can anyone help please?

    VMTIA

    Lisa
    Last edited by lisagreen; Mar 31st, 2018 at 07:09 AM.

  5. #5
    Lively Member
    Join Date
    Mar 2018
    Posts
    64

    Re: VBA Compile

    Hi lisagreen,

    The statement "Option Explicit" means : If you want to use a variable, you must declare it first. So if you change your code to below, it will work properly.
    Code:
    Option Explicit
    
    Sub subTest()
        Dim slString as String
    
        slString = "Test"
    End Sub

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: VBA Compile

    Also note that error trapping is for run time errors. What you are getting is a compile error. It is trapped by the IDE at compile time. The IDE is telling you what the error is and where it occurs. You must correct these errors before your program will compile.

  7. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: VBA Compile

    Hi Lisa,

    Yes, what's been said is correct. And just for your FYI, in many ways, the VB6 IDE and the VBA IDE are very similar. We don't explicitly have a Debug/Compile statement, but we have the equivalent. (For others, that would just be compiling and specify to compile to p-code.)

    We also have the ability to just "run" the project without bothering with the compile first, which is what it sounds like you're doing. And, when we do that, we could potentially run into problems very similar to what you've outlined.

    However, here's my take on the whole thing: My primary project is literally 100s of BAS and FRM modules. And I somewhat frequently make enhancements to it. However, whenever I'm working on it, I'm always very careful to keep it fairly close to something that will successfully compile. If I get to a point to where I've got more than two or three syntax errors, I get so nervous that I must slow down and fix them before I proceed with further development.

    Now, I'm guessing, but it sounds like you've possibly decided to add the "Option Explicit" statement to some of your modules. (This could potentially instantaneously, introduce many syntax errors to your code.) As described above, this requires that all of your variables be explicitly declared (with a Dim, or Static, or Public, etc). And, without this "Option Explicit" if you use a variable that doesn't exist, it will just implicitly create one that's of the Variant type.

    Around here, almost everyone (including me) will rather strongly recommend that you use Option Explicit. And, if I had your project, I'd take the time to put it in. But it may be a bit of work, especially if this is a well developed project that didn't have it to begin with. I'd just take it module at a time (and I have no idea how many modules we're talking about). Just put it at the top of a module, and then, possibly as a first pass, declare all the needed variables (in each procedure) as a Variant. When that's done, that would put you back into the same place you were before the Option Explicit was there.

    Variants are sort of weird animals (i.e., variables). They're like chameleons. They can take on the "flavor" of almost any other type of variable. However, there is a bit of a performance penalty for using them. Also, it's sometimes not clear what they are. For instance, a particular Variant may be a String in one moment and a Double in the next moment, but it's never more than one type. Finding out is what the VarType() and TypeName() functions are for.

    After this first pass (of getting all your variables declared as Variants), I'd then tend to go back and figure out what the variables should specifically be (String, Integer, Long, Double, etc), and declare them properly.

    All of this would take appropriate testing, of course. However, once you started getting the hang of it, you'd start getting confidence that you were doing it correctly.

    I hope that helps.

    Good Luck,
    Elroy
    Last edited by Elroy; Mar 31st, 2018 at 09:22 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Hi Elroy,

    I agree with everything you've said!

    It's likely that I'm not being to clear... apologies.

    I have "Require Variable Declaration" ticked in the VBE Options so every time I create a module or class or form and so on Option Explicit is automatically added. The piece of code in my example is just that. An example deliberately written to show a compile error.

    I can already get a complete list of compile errors with line numbers module names and line text.

    The point of my question is... How do you get the text from the compile error message so I can add that to the list.

    Once again apologies for not giving a clear enough explanation.

    TIA

    Lisa

  9. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: VBA Compile

    Hi Lisa,

    No need to apologize.

    I suppose, with respect to syntax errors (which the compiler will catch), we all try to just not make them. Or, if we're concerned after having written some substantial chunk of code, we hit that "Compile" button to check, immediately fixing anything that pops up.

    Now, once a piece of code successfully compiles, it should never have any problems re-compiling in the future. I have many modules I haven't looked at in years. However, when I re-compile my project, I totally trust that they'll compile with no problems, as they always have in the past.

    I know that doesn't exactly answer your question. But I just don't think there's any way for either a VB6 or VBA compiler to just keep going and compile a list of errors. Rather, they're more designed to just stop on the first one, giving you the opportunity for you to fix it. Then, you compile again, finding the next one ... etc etc. Hopefully, there's never more than a small handful of syntax errors.

    All The Best,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: VBA Compile

    Quote Originally Posted by lisagreen View Post
    The point of my question is... How do you get the text from the compile error message so I can add that to the list.
    Short answer is that you don't. Compile errors happen before the related code is executed so trapping them at run time is not possible.
    You could in theory call the compiler from a command line and redirect the output to a text file then examine that I suppose. I've never did it but it might provide some info, likely not the full info you seek.

    Not sure why this type of issue would need to be added to a list as these errors only happen after editing code incorrectly and the code will never fully compile unless they are fixed so this is only something you would see during development and never in a final release.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    I'm sorry but I beg to differ.... Here is a raw list of errors generated...

    1134 mHolding Else

    1136 mHolding End If

    1140 mHolding If fncIsArrayAllocated(spParameters) Then

    1142 mHolding Else

    1144 mHolding End If

    1200 mHolding subMsgBox spExtra, vbCritical

    442 mVBAForTheVBECode subSetExcelReportPoint blnlOption

    445 mVBAForTheVBECode subSetAccessReportPoint

    448 mVBAForTheVBECode subSetWordReportPoint blnlOption

    453 mVBAForTheVBECode smAaa = slAaa

    454 mVBAForTheVBECode lngmBbb = lnglBbb

    1073 mVBAForTheVBECode lnglSLine = clWhereAreWe.CurrentModuleLine

    1077 mVBAForTheVBECode slCurrentLine = clWhereAreWe.CurrentLine

    1157 mVBAForTheVBECode lnglSLine = clWhereAreWe.CurrentModuleLine

    1161 mVBAForTheVBECode slCurrentLine = clWhereAreWe.CurrentLine


    It looks like a lot but some are generated by the process and can be weeded out.

    @Datamiser... Of course never in a final release.

  12. #12
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: VBA Compile

    Lisa,

    Maybe that's a feature of the latest versions of the VBA. It's certainly not the way VB6 works. This suggests that maybe you should bounce over to the VBA forum and talk to those folks. A link that'll take you over is in post #2 (above).

    Best Of Luck,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Ummm ... not feature of VBA at all. All my own code.

    And nothing fancy either! so I'm sure it would work in VB. Positive in fact.

    Lisa

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA Compile

    @lisa
    i think you are probably further ahead in this than most anyone here, though if you have to press ok for each compile error, i would have thought in most cases, it would be as easy to fix the error directly

    as mentioned by datamiser,
    for vb6 you can specify where to log errors, when using command line compiling, but i doubt there is any similar option for vba
    /out
    Allows you to specify a file to receive errors. Must be used with /m, /deploy, or /rebuild. The errors are placed in this file, along with other status information. If you do not use this option, command line build errors will be displayed in a message box.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Ok... done it. A bit rough at the edges but I think that can be smoothed out. I think it's probably timing issues.

    Attached is a txt file produced with no user intervention.

    Lisa
    Attached Files Attached Files

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA Compile

    i would like to see the code /processes you used to do this
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: VBA Compile

    Quote Originally Posted by westconn1 View Post
    i would like to see the code /processes you used to do this
    Me, too. ;-)

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Hello guys/gals,

    I'm happy to send the current code PM but I'm not happy about the code. I'd rather post a more stable solution. If you want what I've got just say though.

    At the moment I'm using the clipboard to send messages to the two different instances of the application and I don't think that's such a good idea.
    So I'm looking at other methods.... currently experimenting with environment variables.

    I've seen on the net that some use messages to a defined object like a form. My feeling is that I would loose asynchronicity but I'm not really sure.

    Any ideas about sending small strings between instances of a running application????

    Oh... and please remember I said it was a bit rough... Please don't poop on me!!

    Lisa
    Last edited by lisagreen; Apr 13th, 2018 at 04:50 PM.

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Ok... Though I don't really like it... not sure why.. just feels uncomfortable... I'm using the registry to pass messages between the two asynchronous application instances.

    I'm getting another issue though.

    Can anyone please confirm that the code

    Application.VBE.CommandBars.FindControl(ID:=578).Execute

    ... gives a -2147467259 error if debug/compile is greayed out in the VBA VBE please?

    TIA
    Lisa

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA Compile

    to avoid error like
    Code:
    Dim cb As CommandBarButton
    Set cb = Application.VBE.CommandBars.FindControl(ID:=578)
    If cb.Enabled Then
        cb.Execute
    End If
    but as that menu item always seems to be not enabled while any code is running, seems a bit pointless
    tested excel 2000
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: VBA Compile

    Hi.... I'm posting this "as is" and expect criticism.

    Here is my current *VERY RAW* solution to compiling in VBA with no intervention.

    All files go into the same folder.

    Compile.txt is the compile "report".
    log.txt is a er ... log file.

    To start logging uncomment the calls to sublog in the two doc files.

    VBA for the VBE is where the main StartCompile sub is. Use that to start everything. I've deleted the text to the book so it will be a suprise! .

    doc2b Is a "child" document that gets started by a shell call from VBA for the VBE. It also uses a sub called subLog and the same applies about commenting. The code is started from the auto open sub which makes the thing asynchronous.

    Not going to mark this solved yet until and if I get any feedback.

    Please be gentle with me!!!

    Lisa
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width