Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
Hi guys,
I'm using Dave Peterson's sub, http://tinyurl.com/ejpmo (sub is pasted below for easy ref) It works terrific 99.999% of the time (and I'm really grateful), ... but I'm still unable to overcome a few instances of sub halts where the error msg would read: Run-time error '-2147024809 (80070057) The specified value is out of range and Debug would point at this line: myCell.Comment.Shape.Fill.UserPicture PictFileName (a line within Sub InsertPicComment) In Excel, the comment would be created for that particular cell where the sub halts, but w/o the picture inserted I've checked that the hyperlink for the cell is fine (clicking on it opens the image) and the linked image size (.jpg) is also no different (about the same size) from those for other cells where the sub ran beautifully Repeated attempts to reset and re-run the sub on just that single stubborn cell proved fruitless (I've had to resort to manually inserting the pic into the comment for such cases <g) Wondering whether there's anything I could do over here to overcome the above If the above is difficult to resolve, I'd like the sub to skip over the instance(s) and just continue running .. P/s: I'm using Excel 97 in Win XP -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Option Explicit Sub testme() 'select range, Dave Peterson Dim wks As Worksheet Dim myFormula As String Dim QuotePos As Long Dim myRng As Range Dim myCell As Range Set wks = ActiveSheet With wks Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, .UsedRange) On Error GoTo 0 End With If myRng Is Nothing Then MsgBox "not in the used range" Exit Sub End If For Each myCell In myRng.Cells If myCell.HasFormula Then myFormula = LCase(myCell.Formula) If myFormula Like "=hyperlink(""*" Then myFormula = Mid(myFormula, 13) QuotePos = InStr(1, myFormula, Chr(34), vbTextCompare) If QuotePos = 0 Then 'do nothing Else myFormula = Left(myFormula, QuotePos - 1) If myCell.Column 1 Then myCell.Offset(0, -1).Value = myFormula End If Select Case Right(myFormula, 4) Case Is = ".jpg", ".bmp", ".gif", ".png" Call InsertPicComment(myCell, _ myFormula) End Select End If End If End If Next myCell End Sub Sub InsertPicComment(myCell As Range, PictFileName As String) Dim testStr As String testStr = "" On Error Resume Next testStr = Dir(PictFileName) On Error GoTo 0 If testStr = "" Then 'do nothing, picture not found Else If myCell.Comment Is Nothing Then myCell.AddComment Text:="" 'testStr Else myCell.Comment.Text Text:="" 'myCell.Comment.Text & "--" & testStr End If myCell.Comment.Shape.Fill.UserPicture PictFileName End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specifiedvalue is out of range
If you start a new worksheet and recreate that hyperlink manually -- not copy
and paste, what happens? (My guess is that it's not the code--something else (who knows what) is happening.) Max wrote: Hi guys, I'm using Dave Peterson's sub, http://tinyurl.com/ejpmo (sub is pasted below for easy ref) It works terrific 99.999% of the time (and I'm really grateful), .. but I'm still unable to overcome a few instances of sub halts where the error msg would read: Run-time error '-2147024809 (80070057) The specified value is out of range and Debug would point at this line: myCell.Comment.Shape.Fill.UserPicture PictFileName (a line within Sub InsertPicComment) In Excel, the comment would be created for that particular cell where the sub halts, but w/o the picture inserted I've checked that the hyperlink for the cell is fine (clicking on it opens the image) and the linked image size (.jpg) is also no different (about the same size) from those for other cells where the sub ran beautifully Repeated attempts to reset and re-run the sub on just that single stubborn cell proved fruitless (I've had to resort to manually inserting the pic into the comment for such cases <g) Wondering whether there's anything I could do over here to overcome the above If the above is difficult to resolve, I'd like the sub to skip over the instance(s) and just continue running .. P/s: I'm using Excel 97 in Win XP -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Option Explicit Sub testme() 'select range, Dave Peterson Dim wks As Worksheet Dim myFormula As String Dim QuotePos As Long Dim myRng As Range Dim myCell As Range Set wks = ActiveSheet With wks Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, .UsedRange) On Error GoTo 0 End With If myRng Is Nothing Then MsgBox "not in the used range" Exit Sub End If For Each myCell In myRng.Cells If myCell.HasFormula Then myFormula = LCase(myCell.Formula) If myFormula Like "=hyperlink(""*" Then myFormula = Mid(myFormula, 13) QuotePos = InStr(1, myFormula, Chr(34), vbTextCompare) If QuotePos = 0 Then 'do nothing Else myFormula = Left(myFormula, QuotePos - 1) If myCell.Column 1 Then myCell.Offset(0, -1).Value = myFormula End If Select Case Right(myFormula, 4) Case Is = ".jpg", ".bmp", ".gif", ".png" Call InsertPicComment(myCell, _ myFormula) End Select End If End If End If Next myCell End Sub Sub InsertPicComment(myCell As Range, PictFileName As String) Dim testStr As String testStr = "" On Error Resume Next testStr = Dir(PictFileName) On Error GoTo 0 If testStr = "" Then 'do nothing, picture not found Else If myCell.Comment Is Nothing Then myCell.AddComment Text:="" 'testStr Else myCell.Comment.Text Text:="" 'myCell.Comment.Text & "--" & testStr End If myCell.Comment.Shape.Fill.UserPicture PictFileName End If End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
Dave, thanks for comments.
Let me try this at the office tomorrow. (The source images are located in a network drive) Will post back further here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Peterson" wrote in message ... If you start a new worksheet and recreate that hyperlink manually -- not copy and paste, what happens? (My guess is that it's not the code--something else (who knows what) is happening.) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
"Dave Peterson" wrote:
If you start a new worksheet and recreate that hyperlink manually -- not copy and paste, what happens? Just tried it as suggested above, [ via Insert Hyperlink, checked link is good, then manually "converted" the cell's hyperlink to a HYPERLINK formula, double-checked formula link's good as well and then ran the sub ] but was hit with the same error msg with these few stubborn cells The sub writes the path ok in the col to the left, but the pics don't insert in the comments (empty comment created) Could I request for a tweak to the sub to skip such instances and resume its run with perhaps an "X" written in an empty col ? (say, 5 cols to the right of the selected col with the HYPERLINK formula) I could then filter out these "X"'s for manual insertion/action later Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specifiedvalue is out of range
What does the hyperlink look like (just curious)?
Option Explicit Sub testme() 'select range, Dave Peterson Dim wks As Worksheet Dim myFormula As String Dim QuotePos As Long Dim myRng As Range Dim myCell As Range Set wks = ActiveSheet With wks Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, .UsedRange) On Error GoTo 0 End With If myRng Is Nothing Then MsgBox "not in the used range" Exit Sub End If For Each myCell In myRng.Cells If myCell.HasFormula Then myFormula = LCase(myCell.Formula) If myFormula Like "=hyperlink(""*" Then myFormula = Mid(myFormula, 13) QuotePos = InStr(1, myFormula, Chr(34), vbTextCompare) If QuotePos = 0 Then 'do nothing Else myFormula = Left(myFormula, QuotePos - 1) If myCell.Column 1 Then myCell.Offset(0, -1).Value = myFormula End If Select Case Right(myFormula, 4) Case Is = ".jpg", ".bmp", ".gif", ".png" If InsertPicComment(myCell, myFormula) = True Then myCell.Offset(0, 5).Value = "" Else myCell.Offset(0, 5).Value = "x" End If End Select End If End If End If Next myCell End Sub Function InsertPicComment(myCell As Range, PictFileName As String) As Boolean Dim testStr As String testStr = "" On Error Resume Next testStr = Dir(PictFileName) On Error GoTo 0 InsertPicComment = False If testStr = "" Then 'do nothing, picture not found Else If myCell.Comment Is Nothing Then myCell.AddComment Text:="" 'testStr Else myCell.Comment.Text Text:="" 'myCell.Comment.Text & "--" & testStr End If On Error Resume Next myCell.Comment.Shape.Fill.UserPicture PictFileName If Err.Number < 0 Then 'it failed Err.Clear Else InsertPicComment = True End If On Error Resume Next End If End Function Max wrote: "Dave Peterson" wrote: If you start a new worksheet and recreate that hyperlink manually -- not copy and paste, what happens? Just tried it as suggested above, [ via Insert Hyperlink, checked link is good, then manually "converted" the cell's hyperlink to a HYPERLINK formula, double-checked formula link's good as well and then ran the sub ] but was hit with the same error msg with these few stubborn cells The sub writes the path ok in the col to the left, but the pics don't insert in the comments (empty comment created) Could I request for a tweak to the sub to skip such instances and resume its run with perhaps an "X" written in an empty col ? (say, 5 cols to the right of the selected col with the HYPERLINK formula) I could then filter out these "X"'s for manual insertion/action later Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
Marvellous, Dave, the revised sub runs exactly as required.
Many thanks ! .. What does the hyperlink look like (just curious)? An example is: =HYPERLINK("G:\xxx\zzz_picBrowser\MX TK Rd 396\MX TK Road ( Side View zzz) Pic 2.jpg","MX TK Road ( Side View zzz) Pic 2.jpg") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specifiedvalue is out of range
I don't see any problem with your hyperlink sample. And I still don't know why
it would fail. Ah, who knows????? Max wrote: Marvellous, Dave, the revised sub runs exactly as required. Many thanks ! .. What does the hyperlink look like (just curious)? An example is: =HYPERLINK("G:\xxx\zzz_picBrowser\MX TK Rd 396\MX TK Road ( Side View zzz) Pic 2.jpg","MX TK Road ( Side View zzz) Pic 2.jpg") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
"Dave Peterson" wrote:
I don't see any problem with your hyperlink sample. And I still don't know why it would fail. Ah, who knows????? Neither do I .. the beauty in little mysteries linger <bg Thanks again for the options you provided For the record, your subs have since sailed through 2 other heavy duty runs to insert 1,000 and 1,500 images Resultant file sizes are a massive: 100 MB, 350 MB and take a while to open/close, but everything works fine. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specifiedvalue is out of range
100MB and 350MB. Good gawd, I'm surprised anything works. <bg
Max wrote: "Dave Peterson" wrote: I don't see any problem with your hyperlink sample. And I still don't know why it would fail. Ah, who knows????? Neither do I .. the beauty in little mysteries linger <bg Thanks again for the options you provided For the record, your subs have since sailed through 2 other heavy duty runs to insert 1,000 and 1,500 images Resultant file sizes are a massive: 100 MB, 350 MB and take a while to open/close, but everything works fine. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
"Dave Peterson"
100MB and 350MB. Good gawd, I'm surprised anything works. <bg .... there's a 600+MB one done up, and under testing ... so far it looks fine, too <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specifiedvalue is out of range
It sounds scary to me.
Hope you have backups! Max wrote: "Dave Peterson" 100MB and 350MB. Good gawd, I'm surprised anything works. <bg ... there's a 600+MB one done up, and under testing .. so far it looks fine, too <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub halts with Run-time error '-2147024809 (80070057) specified value is out of range
"Dave Peterson" wrote:
It sounds scary to me. Hope you have backups! Certainly, thanks ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cycle time out of range error | Excel Discussion (Misc queries) | |||
Run-time error 9 (Subscript out of range) | Excel Programming | |||
How do you sum a range of more than 30 cells. I get error each time I try. | Excel Worksheet Functions | |||
Run Time Error 9, Script Out of Range | Excel Programming | |||
Run time error 9 (subscript out of range) | Excel Programming |