VBA variable declaration problem

[blueishgray]

Hello.

I am having trouble getting code which was written in Excel 2002 to run in Excel 2003 pretty much because '03 won't run without declaring all variables. what is annoying me is that in the compiler, the "Require Variable Declaration" is unchecked. So my question is, why is it still causing problems when the code runs perfect in Excel '02?


Thanks
Ian

 

[blueishgray]

Also,

the compiler versions are the same, ver. 6.3

 

[Tom Urtis]

(1)
Can't tell unless you post the code

(2)
Maybe an Option Explicit statement is at the top of every module whose code does not work because some / all variables are not declared.

(3)
This you wrote...
"because '03 won't run without declaring all variables"
...is factually incorrect.

 

[blueishgray]

Sorry,

When I said that '03 wouldn't run without declaring variable I simply meant that it wasnt running on the computers here with '03 even though "requiring variable declaration" was unchecked.

And there is way too much code to post but one of the many places it stops is whenever there is a For-Next loop...

i.e.: For Each Cell in Range.Cells....

the error it gives is that it cannot find the object or library with "Cell" being highlighted....

I have to believe that it is some setting in VB to make it work perfectly fine in '02 but not '03...

Last, would VB automatically place the "option explicit" statement at the top of the module? Cause I have not.

Does that help??
Thanks
Ian

 

[Tom Urtis]

the error it gives is that it cannot find the object or library with "Cell" being highlighted....

Could be a reference needing to be established in the computer using 2003. Make note of the refs established for the computer where the macro works, and compare that list to the refs in the computer using 2003. Get the same refs established and that might solve this issue (guessing here because you said "library" was part of the message that popped up, but it would be easier to diagnose if you posted the code). You can find those refs in the VBE by clicking on Tools > References, and taking a look in the Available references pane.

Last, would VB automatically place the "option explicit" statement at the top of the module?

Yes, go into the VBE, click on Tools > Options > Editor tab, and select "Require variable declaration". This will automatically place the Option Explicit at the top of new (not existing) modules. This setting is one that 99.99% of Excel programmers should have; it greatly reduces the chance for errors in code-writing by forcing variable declarations and proper syntax.

 

[blueishgray]

here is another place where I am having problems and it stops in '03:

Sub IndicatorBar(count, total)
Dim r As Integer, c As Integer
Dim PctDone As Single
PctDone = count / (total)
With ProgressIndicator
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
' The DoEvents statement is responsible for the form updating
DoEvents

End Sub

this is taken from j-walk.com
and it accompanies the userfrom 'ProgressIndicator'...It gives the same "cannot find library" error at 'Format(..'
to me that should be a funtion of .Caption and not even a variable problem so I really don't have a clue with what to do there. at least with the other stuff I can go back and make sure absolutely everything is declared (which most stuff is except in random places).

Anyways, thanks for any help.
ian

 

[blueishgray]

okay...

the variable declaration is something I need to just fix.

however, I looked into the references and with respect to the code in the previous post for the progress indicator, the computer the code works on has references for Microsoft Forms 2.0 Object Library which I cant even find on the computers that don't work, so I am sure that this is where the "Format" object reference is.

Is there a way to transfer the reference? maybe just manually look it up in the file system and copy the file over to the other computers??

Thanks agian,
Ian

 

[blueishgray]

transfering the Forms reference file didn't do anything...

this exact error at "Format(.." is

Compile error:
Can't find project ro library


I'm obviously just a little clueless as to what do to.
Thanks,
Ian

 

[Tom Urtis]

Not wanting to reinvent the wheel, what I'd suggest is to go to John's page that discusses this tip, which is at
http://j-walk.com/ss/excel/tips/tip34.htm

and download the example he provides...a link is near the top of the page and looks like this:

"To download a working example, click here."


Take a look at that downloaded workbook and how it works based on the controls in his example, and then take a very close look to compare how you changed the code to fit what you are doing. It's hard from a distance to see your spreadsheet situation. My guess is you are close to where you need to be, you just might be translating his application for the code into a situation for your workbook where a couple things (objects, captions vs values, who knows) would fail.

 

[blueishgray]

Tom

Thanks for the help. I put John's file into the '03 computers and it worked fine.
So I checked my own modified code and everything seemed legit.

I went back to looking at the references and noticed though that in my file I had references for some Microsoft Web Tools that wasn't available in the '03 computers. I unchecked that reference and it works just fine now on all the computers.

So whatever the crap that means, it works now. I appreciate your help, it was indeed a references problem.

Thanks,
Ian