Re: Check if reference is set

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: B0nj (b12_at_nj)
Date: 05/29/04 

Date: Sat, 29 May 2004 13:02:19 +0100

> They are used on a number of legacy PCs with varying versions of Excel
> and so I use late binding for the released versions.
> However, my natural laziness encourages me to make use of intellisense
> during development.

Perfectly reasonable...

>
> I add a reference to the Excel Object Library for development, then for
> testing, I remove that reference and change a compiler constant.
>
> I'm afraid before I finish, I'm going to spend a long time testing a
> "release" version without realising I've forgotten to uncheck the
> reference to excel.
>
> The commented block in the code below shows the sort of thing i'm trying
> to achieve. Sadly this is useless because the error is found at compile
> time.
>
> '''''''''''''''''''''''''''
> #Const ccDEVELOPING = False
>
> Private Sub Command1_Click()
> #If ccDEVELOPING Then
> Dim xlApp As Excel.application
> Dim xlWb As Excel.Workbook
> Dim xlWs As Excel.Work***
> Form1.Caption = "Development version"
> #Else
> Dim xlApp As Object
> Dim xlWb As Object
> Dim xlWs As Object
> ' Dim test As Long
> ' On Error Resume Next
> ' test = Excel.Workbooks.Count
> ' If Err.Number = 0 Then
> ' MsgBox "Excel reference set", , "Do not release"
> ' End If
> ' On Error GoTo 0
> #End If
> End Sub
> '''''''''''''''''''''''''''

Good idea, I used to do this before I realised I knew the Excel methods
I wanted to use off by heart, which I think you will have to face IS the
quickest
and neatest method, and thus never need the references.
But you can make it easier for yourself by having an instance
of Excel open with the VBA window up as a 'scratch pad' to get the parmaters
you need from intellisense.
I would say that creating an 'Excel wrapper' DLL simply to fudge gaining the
use of
intellisense while still using late binding sounds a horrible solution
likely to cause
more head-scratching than it cures, but it's up to you!

>
> Any bright ideas as to how I can check-up on myself would be much
> appreciated.

You could refrain from compiling it within the IDE, and instead create a
.vbs (VBScript) file which sits in the same directory as the .vbp file,
and performs the following actions:
1) Opens the .vbp file as a text file (which it is, inherantly)
2) Checks if the reference is there, if so, then remove it, by writing
the .vbp file back out with whatever it should look like with the reference
out.
Possibly use regexes if you're feeling fruity, although you may find that
it's easier
than you may initially think as you can read it by line, and each
attribute/reference etc.
will probably be on its own line.
3) Shell VB6.exe with the /m switch to cause the exe to build, also use
the /D switch to set the conditional compilation constant.

You could then elaborate by having a message box at the start, saying
something like 'do you want to compile? [or develop within the IDE?]'
and if the answer is yes, then it compiles. If the answer is no, then it
simply starts the .vbp file.

When you want to compile the project, you can simply perform the following
actions:
1) Check it will compile by testing it with Ctrl+F5 (start with full
compile)
2) Close down VB, saving all files
3) Run the VBS file you created earlier.

As a check, you can create a message box or something at the start which
says 'running in development mode' or something if the dev. mode constant
is on.

Quantcast