Compile Error

[GregR]

I use ExcelXP at home and Excel 2000 here at work. I am getting a compile
error on this line at work:

myStr = LCase(.Formula)

This module works at home but not here at work. Why? TIA

Greg

 

[Gary Brown]

I think you may not have the Analysis Toolpak add-in turned on at work. To
do this, select TOOLS / ADD-INS... then make sure 'Analysis Toolpak' and
'Analysis Toolpak - VBA' are checked.
HTH,
Gary Brown

 

[Dave Peterson]

I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide whether you need that
reference or if you can delete it.

If you need it (or something like it), you may want to consider using late
binding.

Your code can find the current version for each pc and use that.

Tom Ogilvy posted this recently:

Here are some more extensive references on binding:

Use late binding - don't have a reference to excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible

 

[GregR]

Dave, here is the code I'm using, it may even be some of yours. I can't find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg

 

[Chip Pearson]

What is the compiler error message?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Dave, here is the code I'm using, it may even be some of yours.
I can't find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng =
wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg

 

[Dave Peterson]

It's not a problem with the code. You have to go to the pc that has the
trouble, then load up that workbook.

Then go to the VBE and then select that project.

Then you'll look in Tools|References and scroll through that list.

Code fails here ----- myStr = LCase(.Formula)

That line shouldn't fail.

Dave, here is the code I'm using, it may even be some of yours. I can't find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg

I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide whether you need that
reference or if you can delete it.

If you need it (or something like it), you may want to consider using late
binding.

Your code can find the current version for each pc and use that.

Tom Ogilvy posted this recently:

Here are some more extensive references on binding:

Use late binding - don't have a reference to excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible

 

[Dave Peterson]

It's not a problem with the code. You have to go to the pc that has the
trouble, then load up that workbook.

Then go to the VBE and then select that project.

Then you'll look in Tools|References and scroll through that list.

Code fails here ----- myStr = LCase(.Formula)

That line shouldn't fail.

Dave, here is the code I'm using, it may even be some of yours. I can't find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg

I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide whether you need that
reference or if you can delete it.

If you need it (or something like it), you may want to consider using late
binding.

Your code can find the current version for each pc and use that.

Tom Ogilvy posted this recently:

Here are some more extensive references on binding:

Use late binding - don't have a reference to excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible

 

[GregR]

Chip, "Can't find library or project" TIA

Greg

What is the compiler error message?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Dave, here is the code I'm using, it may even be some of yours.
I can't find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng =
wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg

I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide
whether you need that
reference or if you can delete it.

If you need it (or something like it), you may want to
consider using late
binding.

Your code can find the current version for each pc and use
that.

Tom Ogilvy posted this recently:

Here are some more extensive references on binding:

Use late binding - don't have a reference to excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications
Whenever Possible



GregR wrote:

I use ExcelXP at home and Excel 2000 here at work. I am
getting a compile
error on this line at work:

myStr = LCase(.Formula)

This module works at home but not here at work. Why? TIA

Greg

 

[GregR]

Dave, the code is in my personal.xls, which is loaded. In the VB editor I go
to the module, check tools/references and check for any missing references.
As far as I can see none are missing. TIA

Greg

It's not a problem with the code. You have to go to the pc that has the
trouble, then load up that workbook.

Then go to the VBE and then select that project.

Then you'll look in Tools|References and scroll through that list.

Code fails here ----- myStr = LCase(.Formula)

That line shouldn't fail.

Dave, here is the code I'm using, it may even be some of yours. I can't find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg

I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide whether you need that
reference or if you can delete it.

If you need it (or something like it), you may want to consider using late
binding.

Your code can find the current version for each pc and use that.

Tom Ogilvy posted this recently:

Here are some more extensive references on binding:

Use late binding - don't have a reference to excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible



GregR wrote:

I use ExcelXP at home and Excel 2000 here at work. I am getting a compile
error on this line at work:

myStr = LCase(.Formula)

This module works at home but not here at work. Why? TIA

Greg

 

[GregR]

Dave, I'm sorry the object I'm missing is the outlook and word object 10
library.

Greg

Dave, the code is in my personal.xls, which is loaded. In the VB editor I go
to the module, check tools/references and check for any missing references.
As far as I can see none are missing. TIA

Greg

It's not a problem with the code. You have to go to the pc that has the
trouble, then load up that workbook.

Then go to the VBE and then select that project.

Then you'll look in Tools|References and scroll through that list.

Code fails here ----- myStr = LCase(.Formula)

That line shouldn't fail.

can't

find

any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg
I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide whether you
need that
reference or if you can delete it.

If you need it (or something like it), you may want to consider

using

 

[Dave Peterson]

If you use Word or Outlook in this project (and if the code you posted is the
only thing in it, then you don't.)

You can remove the references.

In fact, you may want to remove the reference in the original workbook and
redistribute that saved copy.

If you actually use Word or Outlook, you may want to look into that late-binding
stuff.

Another link for late binding using Outlook:

Dick Kusleika has a web page at:
http://www.dicks-clicks.com/excel/olBinding.htm
that explains this with Outlook

Dave, I'm sorry the object I'm missing is the outlook and word object 10
library.

Greg

Dave, the code is in my personal.xls, which is loaded. In the VB editor I go
to the module, check tools/references and check for any missing references.
As far as I can see none are missing. TIA

Greg

It's not a problem with the code. You have to go to the pc that has the
trouble, then load up that workbook.

Then go to the VBE and then select that project.

Then you'll look in Tools|References and scroll through that list.

Code fails here ----- myStr = LCase(.Formula)

That line shouldn't fail.

GregR wrote:

Dave, here is the code I'm using, it may even be some of yours. I

can't

find
any missing references. Am I missing something?

Sub BreakLinks()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
Code fails here ----- myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

The code fails on the noted line. TIA

Greg
I'd check for any missing references in the VBE.

With your project selected, click on tools|references

Look for Missing. When you find it, you'll have to decide whether you
need that
reference or if you can delete it.

If you need it (or something like it), you may want to consider

using

late
binding.

Your code can find the current version for each pc and use that.

Tom Ogilvy posted this recently:

Here are some more extensive references on binding:

Use late binding - don't have a reference to excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible



GregR wrote:

I use ExcelXP at home and Excel 2000 here at work. I am getting a
compile
error on this line at work:

myStr = LCase(.Formula)

This module works at home but not here at work. Why? TIA

Greg