Within a macro written using Visual Basic, I use the .Delete or
..ClearContents method to erase a range. When I call the macro from the
immediate window, it always works. When I call the macro from within the
workbook, it usually does not work. (I have seen occassions that work, but I
have not identified a pattern.) The place the macro is called is on a
different worksheet from where the range to be deleted resides. I have not
turned on any protection modes.

Nice to post your macro for comments
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"TimWms" <(E-Mail Removed)> wrote in message
news:A073E231-4A41-470E-9374-(E-Mail Removed)...
> Within a macro written using Visual Basic, I use the .Delete or
> .ClearContents method to erase a range. When I call the macro from the
> immediate window, it always works. When I call the macro from within the
> workbook, it usually does not work. (I have seen occassions that work, but
> I
> have not identified a pattern.) The place the macro is called is on a
> different worksheet from where the range to be deleted resides. I have not
> turned on any protection modes.

> When I call the macro from within the
> workbook, it usually does not work.

If you mean you have a formula that calls your VBA function, a UDF, it will
never work as it can only return a value. It cannot change the interface in
any way such as erase a range. A least not without writing your UDF in a
rather unusual way!

Regards,
Peter T



If by "macro
"TimWms" <(E-Mail Removed)> wrote in message
news:A073E231-4A41-470E-9374-(E-Mail Removed)...
> Within a macro written using Visual Basic, I use the .Delete or
> .ClearContents method to erase a range. When I call the macro from the
> immediate window, it always works. When I call the macro from within the
> workbook, it usually does not work. (I have seen occassions that work, but
> I
> have not identified a pattern.) The place the macro is called is on a
> different worksheet from where the range to be deleted resides. I have not
> turned on any protection modes.

I'm not sure if you will be able to follow along....

Rem Load Option Chain Pricing from Yahoo using the web address
Rem http://finance.yahoo.com/q/os?m=2008-01
Rem NOTE: Whenever a .refresh is executed, function may be called again from
the beginning, function must be reentrant

Function LoadChains(Stock As String, NextExp As Date, Series As Integer) As
Boolean

Dim ws As Worksheet
Dim conn As String, newconn As String
Dim i As Integer
Static LastCRef(4) As Date


If NextExp <> 0 Then
Set ws = Worksheets("Chain " & Format(Series, "#0"))

If ws.QueryTables.Count <> 0 Then
conn = ws.QueryTables(1).Connection
End If

newconn = "URL;http://finance.yahoo.com/q/os?s=" _
& Stock _
& "&m=" & Format(NextExp, "yyyy") & "-" & Format(NextExp, "m")

If conn <> newconn Then
Do While ws.QueryTables.Count <> 0
ws.QueryTables(1).Delete
Loop
LastCRef(Series) = 0
If ws.Cells(1, 1) <> Empty Then
ws.Range("A1:O50").ClearContents
End If
With ws.QueryTables.Add( _
Connection:=newconn, Destination:=ws.Cells(1, 1))
.BackgroundQuery = False
.EnableRefresh = True
.EnableEditing = True
.FillAdjacentFormulas = True
Rem .Name = "Chain" & Format(Series, "#0") & "_" & Stock
.RefreshOnFileOpen = True
.RefreshStyle = xlOverwriteCells
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "8,12"
End With
End If
If (LastCRef(Series) - LastClose()) < 0 Then ' if close has
occurred since last refresh
ws.QueryTables(1).Refresh (False)
LastCRef(Series) = Now
End If
LoadChains = True

Else ' NextExp = 0 - do nothing
LoadChains = False
End If
End Function

This is the sort of thing I do for clients on a daily basis.

I think you would be much better off using data>import external data>new>put
in your url>import.
Then record a macro while doing. Then modify to put into a loop and copy
desired data from the fetch sheet to another sheet with just the data you
want. Send a workbook to my address below with your symbols, etc and I will
take a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"TimWms" <(E-Mail Removed)> wrote in message
news:BAC765D6-13F8-4AEC-9A38-(E-Mail Removed)...
> I'm not sure if you will be able to follow along....
>
> Rem Load Option Chain Pricing from Yahoo using the web address
> Rem http://finance.yahoo.com/q/os?m=2008-01
> Rem NOTE: Whenever a .refresh is executed, function may be called again
> from
> the beginning, function must be reentrant
>
> Function LoadChains(Stock As String, NextExp As Date, Series As Integer)
> As
> Boolean
>
> Dim ws As Worksheet
> Dim conn As String, newconn As String
> Dim i As Integer
> Static LastCRef(4) As Date
>
>
> If NextExp <> 0 Then
> Set ws = Worksheets("Chain " & Format(Series, "#0"))
>
> If ws.QueryTables.Count <> 0 Then
> conn = ws.QueryTables(1).Connection
> End If
>
> newconn = "URL;http://finance.yahoo.com/q/os?s=" _
> & Stock _
> & "&m=" & Format(NextExp, "yyyy") & "-" & Format(NextExp, "m")
>
> If conn <> newconn Then
> Do While ws.QueryTables.Count <> 0
> ws.QueryTables(1).Delete
> Loop
> LastCRef(Series) = 0
> If ws.Cells(1, 1) <> Empty Then
> ws.Range("A1:O50").ClearContents
> End If
> With ws.QueryTables.Add( _
> Connection:=newconn, Destination:=ws.Cells(1, 1))
> .BackgroundQuery = False
> .EnableRefresh = True
> .EnableEditing = True
> .FillAdjacentFormulas = True
> Rem .Name = "Chain" & Format(Series, "#0") & "_" & Stock
> .RefreshOnFileOpen = True
> .RefreshStyle = xlOverwriteCells
> .SaveData = True
> .WebSelectionType = xlSpecifiedTables
> .WebTables = "8,12"
> End With
> End If
> If (LastCRef(Series) - LastClose()) < 0 Then ' if close has
> occurred since last refresh
> ws.QueryTables(1).Refresh (False)
> LastCRef(Series) = Now
> End If
> LoadChains = True
>
> Else ' NextExp = 0 - do nothing
> LoadChains = False
> End If
> End Function
>

Regardless of how i import the data, I want to erase the previous data
automatically. Try this with a new workbook:
Type text into cell A1.
Add this macro into module -
Function EraseA1() As Boolean

If (ActiveSheet.Cells(1, 1) <> Empty) Then
ActiveSheet.Cells(1, 1).Delete
End If
If (ActiveSheet.Cells(1, 1) <> Empty) Then
EraseA1 = False
Else
EraseA1 = True
End If
End Function

Now in another cell on the same worksheet use the formula =EraseA1(). The
result is FALSE.
Now in the immediate window type x = EraseA1(), and Cell A1 on the worksheet
gets erased.

What am I missing?

"Don Guillett" wrote:

> This is the sort of thing I do for clients on a daily basis.
>
> I think you would be much better off using data>import external data>new>put
> in your url>import.
> Then record a macro while doing. Then modify to put into a loop and copy
> desired data from the fetch sheet to another sheet with just the data you
> want. Send a workbook to my address below with your symbols, etc and I will
> take a look.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "TimWms" <(E-Mail Removed)> wrote in message
> news:BAC765D6-13F8-4AEC-9A38-(E-Mail Removed)...
> > I'm not sure if you will be able to follow along....
> >
> > Rem Load Option Chain Pricing from Yahoo using the web address
> > Rem http://finance.yahoo.com/q/os?m=2008-01
> > Rem NOTE: Whenever a .refresh is executed, function may be called again
> > from
> > the beginning, function must be reentrant
> >
> > Function LoadChains(Stock As String, NextExp As Date, Series As Integer)
> > As
> > Boolean
> >
> > Dim ws As Worksheet
> > Dim conn As String, newconn As String
> > Dim i As Integer
> > Static LastCRef(4) As Date
> >
> >
> > If NextExp <> 0 Then
> > Set ws = Worksheets("Chain " & Format(Series, "#0"))
> >
> > If ws.QueryTables.Count <> 0 Then
> > conn = ws.QueryTables(1).Connection
> > End If
> >
> > newconn = "URL;http://finance.yahoo.com/q/os?s=" _
> > & Stock _
> > & "&m=" & Format(NextExp, "yyyy") & "-" & Format(NextExp, "m")
> >
> > If conn <> newconn Then
> > Do While ws.QueryTables.Count <> 0
> > ws.QueryTables(1).Delete
> > Loop
> > LastCRef(Series) = 0
> > If ws.Cells(1, 1) <> Empty Then
> > ws.Range("A1:O50").ClearContents
> > End If
> > With ws.QueryTables.Add( _
> > Connection:=newconn, Destination:=ws.Cells(1, 1))
> > .BackgroundQuery = False
> > .EnableRefresh = True
> > .EnableEditing = True
> > .FillAdjacentFormulas = True
> > Rem .Name = "Chain" & Format(Series, "#0") & "_" & Stock
> > .RefreshOnFileOpen = True
> > .RefreshStyle = xlOverwriteCells
> > .SaveData = True
> > .WebSelectionType = xlSpecifiedTables
> > .WebTables = "8,12"
> > End With
> > End If
> > If (LastCRef(Series) - LastClose()) < 0 Then ' if close has
> > occurred since last refresh
> > ws.QueryTables(1).Refresh (False)
> > LastCRef(Series) = Now
> > End If
> > LoadChains = True
> >
> > Else ' NextExp = 0 - do nothing
> > LoadChains = False
> > End If
> > End Function
> >
>
>

All you need to do is set up a query ONE time and refresh( vs delete and
re-create) using this which will automatically get the data for the symbol
and times desired. If you send me your private email to my email below I
will send you a workbook.

'Goes in the SHEET module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
sym = Target ' Range("a1") 'ibm
mdate = Range("a2") '2008-03
With ActiveSheet.QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/os?s=" & sym & "&m=" &
mdate
.Refresh BackgroundQuery:=False
End With
End Sub
Calls Strike Puts
Symbol Last Change Bid Ask Volume Open Int Price Symbol Last Change
Bid Ask Volume Open Int
TCE.X 11.4 0 N/A N/A 20 20 25 TOE.X 0.07 0 N/A N/A 10 401
TCY.X 7.1 0 N/A N/A 36 36 27.5 TOY.X 0.22 0 N/A N/A 721 878
TCF.X 6.3 0 N/A N/A 11 160 30 TOF.X 0.43 0 N/A N/A 182 688
TCZ.X 3.7 0 N/A N/A 2 508 32.5 TOZ.X 0.84 0 N/A N/A 250 785
TCG.X 1.98 0 N/A N/A 15,811 21,352 35 TOG.X 1.6 0 N/A N/A 17,291
20,141
TCU.X 0.87 0 N/A N/A 744 6,397 37.5 TOU.X 2.84 0 N/A N/A 222 986
TCH.X 0.29 0 N/A N/A 486 1,523 40 TOH.X 4.9 0 N/A N/A 91 590
TCV.X 0.09 0 N/A N/A 26 1,491 42.5 TOV.X 5.8 0 N/A N/A 15 22
TCI.X 0.05 0 N/A N/A 3 3 45 TOI.X 0 0 N/A N/A 0 0




--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"TimWms" <(E-Mail Removed)> wrote in message
news:A91CB3A1-BFCC-4E30-BBC4-(E-Mail Removed)...
> Regardless of how i import the data, I want to erase the previous data
> automatically. Try this with a new workbook:
> Type text into cell A1.
> Add this macro into module -
> Function EraseA1() As Boolean
>
> If (ActiveSheet.Cells(1, 1) <> Empty) Then
> ActiveSheet.Cells(1, 1).Delete
> End If
> If (ActiveSheet.Cells(1, 1) <> Empty) Then
> EraseA1 = False
> Else
> EraseA1 = True
> End If
> End Function
>
> Now in another cell on the same worksheet use the formula =EraseA1(). The
> result is FALSE.
> Now in the immediate window type x = EraseA1(), and Cell A1 on the
> worksheet
> gets erased.
>
> What am I missing?
>
> "Don Guillett" wrote:
>
>> This is the sort of thing I do for clients on a daily basis.
>>
>> I think you would be much better off using data>import external
>> data>new>put
>> in your url>import.
>> Then record a macro while doing. Then modify to put into a loop and copy
>> desired data from the fetch sheet to another sheet with just the data you
>> want. Send a workbook to my address below with your symbols, etc and I
>> will
>> take a look.
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "TimWms" <(E-Mail Removed)> wrote in message
>> news:BAC765D6-13F8-4AEC-9A38-(E-Mail Removed)...
>> > I'm not sure if you will be able to follow along....
>> >
>> > Rem Load Option Chain Pricing from Yahoo using the web address
>> > Rem http://finance.yahoo.com/q/os?m=2008-01
>> > Rem NOTE: Whenever a .refresh is executed, function may be called again
>> > from
>> > the beginning, function must be reentrant
>> >
>> > Function LoadChains(Stock As String, NextExp As Date, Series As
>> > Integer)
>> > As
>> > Boolean
>> >
>> > Dim ws As Worksheet
>> > Dim conn As String, newconn As String
>> > Dim i As Integer
>> > Static LastCRef(4) As Date
>> >
>> >
>> > If NextExp <> 0 Then
>> > Set ws = Worksheets("Chain " & Format(Series, "#0"))
>> >
>> > If ws.QueryTables.Count <> 0 Then
>> > conn = ws.QueryTables(1).Connection
>> > End If
>> >
>> > newconn = "URL;http://finance.yahoo.com/q/os?s=" _
>> > & Stock _
>> > & "&m=" & Format(NextExp, "yyyy") & "-" & Format(NextExp,
>> > "m")
>> >
>> > If conn <> newconn Then
>> > Do While ws.QueryTables.Count <> 0
>> > ws.QueryTables(1).Delete
>> > Loop
>> > LastCRef(Series) = 0
>> > If ws.Cells(1, 1) <> Empty Then
>> > ws.Range("A1:O50").ClearContents
>> > End If
>> > With ws.QueryTables.Add( _
>> > Connection:=newconn, Destination:=ws.Cells(1, 1))
>> > .BackgroundQuery = False
>> > .EnableRefresh = True
>> > .EnableEditing = True
>> > .FillAdjacentFormulas = True
>> > Rem .Name = "Chain" & Format(Series, "#0") & "_" & Stock
>> > .RefreshOnFileOpen = True
>> > .RefreshStyle = xlOverwriteCells
>> > .SaveData = True
>> > .WebSelectionType = xlSpecifiedTables
>> > .WebTables = "8,12"
>> > End With
>> > End If
>> > If (LastCRef(Series) - LastClose()) < 0 Then ' if close has
>> > occurred since last refresh
>> > ws.QueryTables(1).Refresh (False)
>> > LastCRef(Series) = Now
>> > End If
>> > LoadChains = True
>> >
>> > Else ' NextExp = 0 - do nothing
>> > LoadChains = False
>> > End If
>> > End Function
>> >
>>
>>