Compile error: Variable not defined

[jrpuig1977]

Hello everyone,

I got this Excel file from a website. It is a utility for Primavera P6.

Please do check this link to have a better understanding of the utility's function.

http://www.plannertuts.com/primavera...t-the-sdk-840/

Problem is...it ain't working. I think there's something wrong in it's VB.

Appreciate your help guys.

Thanks in advance!

J

P.S.

I'm a novice in VB, I tried researching but can't break the ice

[dredwolf]

cannot load your file, because I get an 'Object not defined' error
so....what is the offending object, or do you have some add-ins that are not a part of "Regular" VBA/Excel ? (I may not be the only person willing to look at, but, if we cant even get the file to open, that is not a good sign )

[jrpuig1977]

Hello dredwolf,

Thank you very much for looking at my post!

I got that file from

http://www.plannertuts.com/primavera...t-the-sdk-840/

I was able to open it but when I started following what it says on the tutorial, the embedded functions in Excel coded with VB doesn't seem to work.

Below the tutorial were some comments concerning the same issue as mine which I followed through but still can't figure it out

Hope someone will be able to solve the issue.

Thanks again mate!

Cheers,

J

[dredwolf]

It was not a problem looking, it's just I can not open the file, keeps giving me the error, and while I am willing to go days on end without sleep, I am not sure I want to do it to solve someone else's (and I DO NOT mean you, the person who made this is who I mean) problem)...maybe someone else has used this before, but I am not willing to download such a flaky product, sorry

[OllieB]

I had a quick look.

The problem appears to be related to the usage of the Common Dialog control on one of the forms (frmPrompt).

You can open the workbook by keeping the shift key pressed while the workbook is being opened by Excel to bypass any autostart code. The next step would be to goto Tools/References and to remove the Common Controls reference (which may be shown as missing).

The next step would be to change the frmPrompt form (and underlying code) to use the standard available Excel File/Folder dialogue (i.e. Application.FileDialog(msoFileDialogFilePicker)
to select the file to be processed.

[watersev]

hi jrpuig1977, it looks you have library reference missing. You can check that by VBE - Tools - References. In order for the code to work there should be a checked reference for "Microsoft Common Dialog Control 6.0 (SP6)". I suppose it is missing in your case. To correct that click VBE - Tools - References - Browse and locate file: comdlg32.ocx, select it and click OK. Now when this reference is checked no such error should appear.

PS. Basically that is the question to the developer/s for the product support

[OllieB]

@watersev, you are entirely correct, but the funny thing is that I do not have the text "MISSING", but do get compilation errors.

When I try to access the properties of the common dialog object on the form I get an error that the object has not been properly installed.

As the object appears to be used for no other purposes of selecting a file object via a dialogue, I suggested to rewrite that bit of code. But he should indeed first check whether the missing reference suggestion solves the problem.

[jrpuig1977]

hi watersev, "Microsoft Common Dialog Control 6.0 (SP6)" is checked by default. When I got the fresh download of this file I cleared the object "MISSING" under tools, reference in VB.

Now, following your advise (click VBE - Tools - References - Browse and locate file: comdlg32.ocx), I cannot locate the file comdlg32.ocx. I even tried to locate in windows search but cannot find it anywhere. What does this mean?

Appreciate your time.

[jrpuig1977]

hi OllieB, thank you very much for your time.

I cannot follow through your advise:
"The next step would be to change the frmPrompt form (and underlying code) to use the standard available Excel File/Folder dialogue (i.e. Application.FileDialog(msoFileDialogFilePicker)
to select the file to be processed."

sorry mate i'm a novice in VB.

[jrpuig1977]

Hello guys!

Problem sovled thru' watersev's advise.

Cheers!

"watersev
Re: Compile error: Variable not defined

hi jrpuig1977, it looks you have library reference missing. You can check that by VBE - Tools - References. In order for the code to work there should be a checked reference for "Microsoft Common Dialog Control 6.0 (SP6)". I suppose it is missing in your case. To correct that click VBE - Tools - References - Browse and locate file: comdlg32.ocx, select it and click OK. Now when this reference is checked no such error should appear.

PS. Basically that is the question to the developer/s for the product support"

[ckkalberta]

This solution is not working for me, even after I correct the References as per "VBE - Tools - References - Browse and locate file: comdlg32.ocx, select it and click OK", I still get the Compile error: Variable not defined with CommonDialog highlighted...what can I try other than this.....I am not a VBA user so please help. I am using the same file referenced above in Excel 2010. Thanks.

[ckkalberta]

"The next step would be to change the frmPrompt form (and underlying code) to use the standard available Excel File/Folder dialogue (i.e. Application.FileDialog(msoFileDialogFilePicker)
to select the file to be processed."

OllieB, how do I do this?