doowell
asked on
Programmatically check VBA compiles as part of release procedure?
This should surely be a very simple one! Either "not possible" or "yes, in one line of code..."
I maintain a number of VBA projects in Excel.
I have a release procedure in each i.e. to copy the Dev Workbook over to UAT before copying to Live.
I would love to have one line in the release macro that says (in pseudocode)
The closest I can get is
but seems this is legacy or even spurious functionality because the online help says it
I'm not trying to create a DLL.
All I want to do is replicate the VB Editor Debug menu's Compile command (which is really just a syntax checker) programmatically and confirm all is well before releasing the update.
Anyone out there know how to do this?
I maintain a number of VBA projects in Excel.
I have a release procedure in each i.e. to copy the Dev Workbook over to UAT before copying to Live.
I would love to have one line in the release macro that says (in pseudocode)
If ThisWorkbook.VBACompiles() Then
ThisWorkbook.SaveCopyAs {uat_path}
Else
{report_compile_error}
End If
The closest I can get is
ThisWorkbook.VBAProject.MakeCompiledFile()
but seems this is legacy or even spurious functionality because the online help says it
"Causes the current project to be written as a DLL. The DLL name is specified by the BuildFileName property."
I'm not trying to create a DLL.
All I want to do is replicate the VB Editor Debug menu's Compile command (which is really just a syntax checker) programmatically and confirm all is well before releasing the update.
Anyone out there know how to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The best I think you can do is use:
On Error Resume Next
Application.VBE.CommandBar s.FindCont rol(ID:=57 8).Execute
On Error Goto 0
before your save code. If the compile fails, the rest of the code won't run.
On Error Resume Next
Application.VBE.CommandBar
On Error Goto 0
before your save code. If the compile fails, the rest of the code won't run.
ASKER
Not quite - it'll run if the module in which the release macro resides compiles.
Says nothing about the other modules alas.
So if you have a VBA project of N modules, the release macro will run even if the other N-1 modules won't compile.
Says nothing about the other modules alas.
So if you have a VBA project of N modules, the release macro will run even if the other N-1 modules won't compile.
Ah, should have checked multiple modules.
Then I think the answer is no, since no error is returned, in spite of the compile messages.
Then I think the answer is no, since no error is returned, in spite of the compile messages.
Haven't had time to test it, this launches the manual Compile VBAProject button.
Sub CompileProject()
'
With Application
.ScreenUpdating = False
'
On Error Resume Next
With .VBE
'need the VBE window open for this one
.MainWindow.Visible = True
.CommandBars("Menu Bar").Controls("Debug") _
.Controls("Compile VBAProject").Execute
.MainWindow.Visible = False
End With
'
.ScreenUpdating = True
End With
'
End Sub
Sub CompileProject()
'
With Application
.ScreenUpdating = False
'
On Error Resume Next
With .VBE
'need the VBE window open for this one
.MainWindow.Visible = True
.CommandBars("Menu Bar").Controls("Debug") _
.Controls("Compile VBAProject").Execute
.MainWindow.Visible = False
End With
'
.ScreenUpdating = True
End With
'
End Sub
Oh, I see rorya already posted this method ... and got stuck as well.
ASKER
All, excellent contributions.
Invoking the Compile menu option does run the compiler but it doesn't return the result of it (e.g. True for success).
The return value of the .Execute method seems to be empty - when I get invoke it...
Because ... hen in Debugging mode, the "Compile..." option is greyed-out under the Debug menu. Trying to execute its command therefore throws an error.
And even when not debugging, the menu option remains greyed out until I edit the code in a way that causes the project to be reset.
So we're making progress but not yet there.
Any further thoughts?!
Invoking the Compile menu option does run the compiler but it doesn't return the result of it (e.g. True for success).
The return value of the .Execute method seems to be empty - when I get invoke it...
Because ... hen in Debugging mode, the "Compile..." option is greyed-out under the Debug menu. Trying to execute its command therefore throws an error.
And even when not debugging, the menu option remains greyed out until I edit the code in a way that causes the project to be reset.
So we're making progress but not yet there.
Any further thoughts?!
ASKER
Sorry few typos - corrections here
All, excellent contributions.
Invoking the Compile menu option does run the compiler but it doesn't return the result of it (e.g. True for success).
The return value of the .Execute method seems to be empty - when I get *TO* invoke it...
Because ... *when* in Debugging mode, the "Compile..." option is greyed-out under the Debug menu. Trying to execute its command therefore throws an error.
And even when not debugging, the menu option remains greyed out until I edit the code in a way that causes the project to be reset.
So we're making progress but not yet there.
Any further thoughts?!
All, excellent contributions.
Invoking the Compile menu option does run the compiler but it doesn't return the result of it (e.g. True for success).
The return value of the .Execute method seems to be empty - when I get *TO* invoke it...
Because ... *when* in Debugging mode, the "Compile..." option is greyed-out under the Debug menu. Trying to execute its command therefore throws an error.
And even when not debugging, the menu option remains greyed out until I edit the code in a way that causes the project to be reset.
So we're making progress but not yet there.
Any further thoughts?!
I thought of checking the dll to be empty if code does not compile aftter running a MakedCompileFile command, turns out this command is only avaialble for specific project for Office Developer package users, not for plain Excel VBA.
I guess you already use Explicit (which will prevent some of the errrors raised by the compile you look for)?
I guess you already use Explicit (which will prevent some of the errrors raised by the compile you look for)?
ASKER
@calacuccia
Thanks again. Not sure I understand your comment
"I guess you already use Explicit (which will prevent some of the errrors raised by the compile you look for)?"
Thanks again. Not sure I understand your comment
"I guess you already use Explicit (which will prevent some of the errrors raised by the compile you look for)?"
ASKER
Ah right sorry yes I get you now. yes, Option Explicit is part of my gospel ;o)
If the menu item is disabled, then you are SOL.
The only thing I can think of currently would be to execute the button, hook the resulting message box, if any, and return a variable using the hook procedure. Just need to figure out the implementation. :)
The only thing I can think of currently would be to execute the button, hook the resulting message box, if any, and return a variable using the hook procedure. Just need to figure out the implementation. :)
Option Explicit in each code window on top
Forget it, does not change anything, it only raises variable errors upon compiling.
ASKER
I have a feeling I'm going to have to admit defeat on this one.
It would be SO NICE to be able to ensure all VBA code compiles prior to release.
Having said that I understand VBA will soon have C# alongside it. It's about time that VBA was updated to VB.Net.
It would be SO NICE to be able to ensure all VBA code compiles prior to release.
Having said that I understand VBA will soon have C# alongside it. It's about time that VBA was updated to VB.Net.
I've been trying to find Compile Error handling documentation or topics, but every thread I read goes towards handling the Compile Error cause and prevent it from happening, rather than trapping it.
I get the same feeling as you, except catching the message Box from a compile Error, but have no clue right now how to do this.
I get the same feeling as you, except catching the message Box from a compile Error, but have no clue right now how to do this.
ASKER
My word -- I have rarely come across anything that this illustrious community cannot solve!
Let's hope some inspiration strikes.
Meanwhile it's still quite interesting seeing how the hive mind here is working.
Let's hope some inspiration strikes.
Meanwhile it's still quite interesting seeing how the hive mind here is working.
Currently my attempts to hook the message box are stuck as I can't get the right ThreadID (it seems not to be the one running the VBA). May have to resort to using a timer to see if a messagebox comes up after executing the button and then set a variable from there.
ASKER
Wow I really appreciate the collective determination here.
Seems like this is going to be a real bi_ch to solve.
I can live without this but on the other hand will greatly appreciate a working answer!
Best wishes to all (and don't work too hard).
Seems like this is going to be a real bi_ch to solve.
I can live without this but on the other hand will greatly appreciate a working answer!
Best wishes to all (and don't work too hard).
ASKER
Yes but the problem is no solution has appeared - what should I do?
ASKER
OK having read the link you provide, I will wait another week or so for a solution otherwise I will ask for the question to be deleted
ASKER
As per the Help Page, I am following this guidance:
Leave a request in the Community Support topic area to delete the question (no solution)
Before you ask the Moderators to help you in this circumstance, take the time to post a message in your question stating your intention to have the question deleted or closed, and make sure you have responded to all of the Experts' comments. Then post a question in the Community Support Zone asking the Moderators to delete your question. Remember to post a link to the original question in your request. If there are no comments in the question, one of the Moderators will delete the question. If there are comments, the Moderator will post notice of your request, and will give the participants four days to object. You may be required to post your reason for asking for the deletion, and objections will be taken into account.
Leave a request in the Community Support topic area to delete the question (no solution)
Before you ask the Moderators to help you in this circumstance, take the time to post a message in your question stating your intention to have the question deleted or closed, and make sure you have responded to all of the Experts' comments. Then post a question in the Community Support Zone asking the Moderators to delete your question. Remember to post a link to the original question in your request. If there are no comments in the question, one of the Moderators will delete the question. If there are comments, the Moderator will post notice of your request, and will give the participants four days to object. You may be required to post your reason for asking for the deletion, and objections will be taken into account.
It would seem to me that your question was answered (initially by stavros41) it's just that the answer was no. That is still a valid answer though.
(there's no object button on the mobile site, but you can consider this one)
(there's no object button on the mobile site, but you can consider this one)
ASKER
Sorry thought I had closed this but obviously not...
ASKER
Seems no solution is possible so have awarded points to the person who pointed this out first
ASKER