"Set" problem

[Ian]

I have created a number of spreadsheets for use on numerous laptops,
currently running Office 2000. These spreadsheets work fine on my laptop and
previous versions ran OK on all the remote laptops. The latest issue one of
these sheets appears to be causing problems on other systems, but works fine
here.

I've tried this sheet on 2 local PCs (running Office 2k & Office XP) and see
these problems. There are 2 manifestations of what seems to be the same
problem.

Initially an input form runs. This requires certain inputs before the OK
button is enabled. At any stage a Cancel button is available.

1. No inputs are made and the Cancel button is clicked.
The code stops with a compile error stating Can't find project or library.
The code in question is:

With Worksheets("Lookup")
Set Early_8000_23 = .Range("Early_8000_23") '\
End With

There are many other "Set" statements following this, but however many I
comment out, the first one available produces the same error. The section of
the line highlighted is the first "Early_8000_23".

2. All required inputs except one are made and CommandButton3 is clicked.
This should enter the current date into the TextBox2. The code causing
problems is:

Private Sub CommandButton3_Click()
TextBox2.Value = Format(Date, "dd/mm/yyyy")
End Sub

The reference highlighted here is "Date"
CommandButton3 has been clicked as part of the input process and has entered
the current date correctly in TextBox2.

The real questions are:
1. Why should this be happening now, when a previous issue of these sheets
worked fine (these particular parts of the code haven't changed)
2. Why should this particular workbook be affected when the others (using
exactly the same Date code and very similar Set statements) work fine.

There's obviously something specific to this workbook, but I can't figure
out what. It would appear that it isn't specifically related to the code
highlighted. I checked the add-ins on my laptop and there aren't any
selected, so that's not the issue.

The only thing I can see is that my laptop is running VB6.5 (Office 2k SP3)
whereas my other Office 2k is only VB6.0 (not even SP1 on this machine) and
Office XP is VB6.3 (SP3). This seems odd as office XP is newer than Office
2000 so I would have thought XP SP3 would have included at least VB6.5.

 

[Tom Ogilvy]

After the error has occured, go into the VBE and make sure your workbook is
the activeproject (selected in the project explorer). Then do
Tools=>References (you may need to hit reset first). You should see at least
one reference marked as MISSING. If so, this is the source of your problem
and needs to be removed (if not needed) or fixed by finding the correct
reference.

References are specific to each workbook, so this is consistent with your
assessment that it is associated with the particular workbook.

 

[Chip Pearson]

It sounds like one or more of the VBA Type Library references got screwed
up. See www.cpearson.com/Excel/MissingReferences.aspx for a description of
the problem and several remedies to try to fix the problem.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Ian]

Thanks Chip and Tom.

For some reason the workbook was looking for Ref Edit Control (REFEDIT.DLL).
As I have already deployed this workbook to 20+ other people, is it going to
be a case of replacing the entire workbook, or can I use code in another
sheet to update this?

I already use this method to apply periodic modifications to workbooks, but
that is limited to replacing ranges within the sheet, not modifying the code
(or in this case references) in the workbook. Indeed, I don't even know if
it is possible to do either of these.

Many thanks.

Ian

 

[Chip Pearson]

Ian,

It is certainly possible to use code to modify other code or other
attributes of the VB Project of a workbook. See
http://www.cpearson.com/Excel/vbe.aspx for details and examples.

You could distribute a workbook to the end users that would fix any
troublesome workbooks the user may have. Put the following code in a code
module of a workbook, link it to a button on Sheet1, and send this workbook
out to the users. They should have this workbook and the workbook with the
bad reference open at the same time, and when they click on the button on
Sheet1 of this workbook, the code will run and will either delete and
recreate the reference to RefEdit.dll, remove and not recreate the reference
to RefEdit, or do nothing at all. The action (or lack thereof) is user
choice. This workbook requires a reference to the "Microsoft Visual Basic
For Applications Extensibility 5.3 Library" (in VBA, Tools menu, Reference,
check this entry in the list).

The code will look for RefEdit.DLL in Application.Path. If it is not found
there, the user is given the choice of quitting the operation or searching
for RefEdit.dll themselves.

Sub FixRefEdit()

Dim FName As Variant
Dim OldDir As String
Dim VBProj As VBIDE.VBProject
Dim Ref As VBIDE.Reference
Dim WB As Workbook
Dim Res As VbMsgBoxResult
Dim RefEditFileName As String
Dim WBName As String

WBName = InputBox("Enter the name of the workbook to update.")
If WBName = vbNullString Then
MsgBox "No workbook name entered. Cancelling operation", vbOKOnly
Exit Sub
End If
On Error Resume Next
Set WB = Workbooks(WBName)
On Error GoTo 0
If WB Is Nothing Then
MsgBox "Cannot find workbook:" & vbCrLf & _
WBName, vbOKOnly
Exit Sub
End If


RefEditFileName = Application.Path & "\RefEdit.dll"
If Dir(RefEditFileName, vbNormal) = vbNullString Then
Res = MsgBox("The RefEdit file was not found in the expected location:"
& vbCrLf & _
RefEditFileName & vbCrLf & _
"Do you want to search for it yourself?" & vbCrLf & _
"Click 'Yes' to search for the file." & vbCrLf & _
"Click 'No' to terminate this operation", vbYesNo)
If Res = vbNo Then
Exit Sub
End If
OldDir = CurDir
ChDrive Application.Path
ChDir Application.Path
FName = Application.GetOpenFilename("DLL Files,*.dll", , "Search For
RefEdit.dll")
ChDrive OldDir
ChDir OldDir
If FName = False Then
Exit Sub
End If
If InStr(1, FName, "Refedit", vbTextCompare) = 0 Then
' are we sure that the user choose RefEdit.DLL?
Res = MsgBox("The selected file:" & vbCrLf & _
FName & vbCrLf & _
"does not appeat to be the correct file. Are you sure you" &
vbCrLf & _
"want to use this file?", vbYesNo)
If Res = vbNo Then
Exit Sub
End If
End If
RefEditFileName = FName
End If

Set VBProj = WB.VBProject
If VBProj.Protection = vbext_pp_none Then
On Error Resume Next
Set Ref = VBProj.References("REFEDIT")
On Error GoTo 0
If Not Ref Is Nothing Then
Res = MsgBox("RefEdit reference found. Do you want to update it?" &
vbCrLf & _
"Click 'Yes' to remove and recreate the reference to RefEdit." &
vbCrLf & _
"Click 'No' to remove the reference to RefEdit." & vbCrLf & _
"Click 'Cancel' to do nothing with the reference to RefEdit.",
vbYesNoCancel)
Select Case Res
Case vbYes
On Error Resume Next
VBProj.References.Remove Ref
VBProj.References.AddFromFile RefEditFileName
On Error GoTo 0
Case vbNo
VBProj.References.Remove Ref
Case Else
' do nothing
End Select
End If
End If

If WB.Saved = False Then
WB.Save
End If

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Ian]

Thanks, Chip. As usual, a very informative response.

It is certainly possible to use code to modify other code or other
attributes of the VB Project of a workbook. See
http://www.cpearson.com/Excel/vbe.aspx for details and examples.

A very useful article, though the warning about anti-virus raises concerns.
It may be necessary for the end users to temporarily disable the AV on their
laptops. I'll have to look into that.

You could distribute a workbook to the end users that would fix any
troublesome workbooks the user may have. Put the following code in a code
module of a workbook, link it to a button on Sheet1, and send this
workbook out to the users. They should have this workbook and the workbook
with the bad reference open at the same time

I think I'll programmatically open the problem workbook and automatically
remove the reference. I need to make it as easy as possible as some of the
users are not particularly PC literate. When I distribute the fix, it will
be with a batch file to copy the workbook to a specific location and open
the workbook. The code will run on opening.

This workbook requires a reference to the "Microsoft Visual Basic For
Applications Extensibility 5.3 Library" (in VBA, Tools menu, Reference,
check this entry in the list).

Is this a standard library on all PCs (or at least, all PCs runningMS
Office)? I don't want to end up with any more problems with missing
references.

As an aside, can you think how the problem reference came to be set? I
certainly haven't done it manually, particularly in view of the fact that,
once I remove the reference on my own laptop, it disappears from the list of
available references.

Ian

 

[Ian]

Hi Chip

I've had a chance to try your posted code on another machine with the
erroneous reference. I get a run-time error 1004 on the line

Set VBProj = WB.VBProject

The code is in a new workbook and the reference to Microsoft Visual Basic
For Applications Extensibility 5.3 Library is set in this book. The project
in the problem workbook is not locked.

I tried to output the value of WB with Debug.Print, but nothing was shown.
Is this normal?

Stepping through the code, after the line Set WB = Workbooks(WBName) I
hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of object
'_Workbook' failed>.

Am I missing something obvious?

Ian

 

[Ian]

Just a thought. The file I'm trying to fix is a template (xlt), not xls.
Does this make any difference?
Also, I've opened the problem file without macros running. Does this matter?

Ian

 

[Ian]

Hi Chip

I'm making progress, but still having issues.

I no longer get the runtime error below. This was due to Excel XP not having
"Trust access to Visual Basic Project" ticked in the security settings.

I now have a different problem. After running your code without any
(visible) errors, when I try to open the problem checklist with macros
running:
Excel XP - Excel takes 99% CPU time until I stop it.
Excel 2000 - I get a MSVB message box stating "Error in loading DLL". Help
shows this as error 48 which refers to a lirary specified in the Lib clause
of a Declare statement. There is no Declare statement in my code.

Stepping through your code again, in the section starting with Set VBProj,
the line:

Set Ref=VBProj.References("REFEDIT")

doesn't appear to do anything. Ref=Nothing so the rest of this section is
skipped

In both versions the reference to MISSING: Ref Edit Control is still
present.

Any ideas?

Ian

 

[Ian]

Hi Chip

Any thoughts on this issue?

I'm making progress, but still having issues.

I no longer get the runtime error below. This was due to Excel XP not
having
"Trust access to Visual Basic Project" ticked in the security settings.

I now have a different problem. After running your code without any
(visible) errors, when I try to open the problem checklist with macros
running:
Excel XP - Excel takes 99% CPU time until I stop it.
Excel 2000 - I get a MSVB message box stating "Error in loading DLL". Help
shows this as error 48 which refers to a lirary specified in the Lib clause
of a Declare statement. There is no Declare statement in my code.

Stepping through your code again, in the section starting with Set VBProj,
the line:

Set Ref=VBProj.References("REFEDIT")

doesn't appear to do anything. Ref=Nothing so the rest of this section is
skipped

In both versions the reference to MISSING: Ref Edit Control is still
present.

Any ideas?

Ian

 

[Chip Pearson]

I've been having lots of problems with RefEdit on Vista/Excel2007. I think
I'm going to quit using it. It has always been a troublesome control -- half
assed design and development I would guess. I have no idea why it would
spike the CPU, though.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Ian]

Hi Chip

I've given up on this and reissued the workbooks with the reference removed.
It would have been good to know how the reference had got in there and why I
couldn't remove it programmatically. Still, I've learnt a bit along the way,
so it wasn't all wasted effort

I've been having lots of problems with RefEdit on Vista/Excel2007. I think

I wasn't using it. I don't know what it's for. My workbook just decided it
needed the reference for some reason.

I'm going to quit using it. It has always been a troublesome control --
half assed design and development I would guess. I have no idea why it
would spike the CPU, though.

Are you referring to a different issue here? I don't understand the CPU
reference.


Thanks again for your input.

Ian