Replies

Graig,
Thanks for pointing that out.
That wasn’t my algorithm and my expertise (if any) lies elsewhere.

The “Int” functions in the code could be changed to “Fix” functions and that would handle the positive/negative issue.
Int rounds negative numbers down while Fix truncates them.
The displayed answer, however, would have multiple minus signs.
Following your suggestion about using the absolute value, I’ve modified the code using that approach…

‘–
Function Convert_Degree(ByRef Decimal_Deg As Variant) As Variant
Dim Degrees As Double
Dim Minutes As Double
Dim Seconds As Double
Dim strSign As String

If Not IsNumeric(Decimal_Deg) Then
Convert_Degree = “Number Required”
Exit Function
Else
If Sgn(Decimal_Deg) > -1 Then
strSign = ” ”
Else
strSign = “-”
End If
Decimal_Deg = Abs(Decimal_Deg)
End If

‘Set degree to Integer of Argument Passed
Degrees = Int(Decimal_Deg)
‘Set minutes to 60 times the number to the right
‘of the decimal for the variable Decimal_Deg
Minutes = (Decimal_Deg – Degrees) * 60
‘Set seconds to 60 times the number to the right of the
‘decimal for the variable Minute
Seconds = Format$(((Minutes – Int(Minutes)) * 60), “0.0###”)
‘Returns the Result of degree conversion
‘(for example, 10.46 = 10~ 27 ‘ 36″)
Convert_Degree = strSign & Degrees & “° ” & Int(Minutes) & “‘ ” _
& Seconds & Chr$(34)
End Function
‘–

Jim Cone
Portland, Oregon USA
Primitive Software Files

Application.DisplayAlerts = False
‘ Your code
Application.DisplayAlerts = True

Also, add… Application.DisplayAlerts = True to your error handler.
‘–
Jim Cone
Portland, Oregon USA
http://tinyurl.com/SpecialSort

You didn’t mention the xl version you are using or on what line the error occurs.
My guess is that…
For Each Wks In Workbooks(“CHEX”).Sheets

Should be…
For Each Wks In Workbooks(“CHEX.xls”).Sheets
‘–
Jim Cone
Portland, Oregon USA
XL Companion add-in

If you are calling the function from a worksheet cell (instead of calling it using code) then
Replace…
If TypeName(Decimal_Deg) “Double” Then
Convert_Degree = “Number required”
Exit Function
End If

With…
If Not IsNumeric(Decimal_Deg) Then
Convert_Degree = “Number Required”
Exit Function
End If

–or–
Just remove those 4 lines.
That code segment is useful only if someone not familiar with your workbook is using it.
‘–
Jim Cone
Portland, Oregon USA
Special Sort add-in review

Change this line…
Seconds = Format(((Minutes – Int(Minutes)) * 60), “0”)
To…
Seconds = Format(((Minutes – Int(Minutes)) * 60), “0.0###”)
‘–

Going a little further, I did some cleanup on the code…
Function Convert_Degree(ByRef Decimal_Deg As Variant) As Variant
Dim Degrees As Double
Dim Minutes As Double
Dim Seconds As Double

If TypeName(Decimal_Deg) “Double” Then
Convert_Degree = “Number required”
Exit Function
End If

‘Set degree to Integer of Argument Passed
Degrees = Int(Decimal_Deg)
‘Set minutes to 60 times the number to the right
‘of the decimal for the variable Decimal_Deg
Minutes = (Decimal_Deg – Degrees) * 60
‘Set seconds to 60 times the number to the right of the
‘decimal for the variable Minute
Seconds = Format(((Minutes – Int(Minutes)) * 60), “0.0###”)
‘Returns the Result of degree conversion
‘(for example, 10.46 = 10~ 27 ‘ 36″)
Convert_Degree = ” ” & Degrees & “° ” & Int(Minutes) & “‘ ” _
& Seconds & Chr(34)
End Function
‘–
Jim Cone
Portland, Oregon USA
Extras for Excel add-in

A few hundred charts in a workbook will probably cause problems.
If you don’t have that many, you could try moving a sheet at a time to another workbook and test the new workbook after each sheet is added.
You may find one sheet/charts that is causing the trouble. (or you may not).
Of course, if your problems exists in nearly all workbooks then the above is a time waster.

On the other hand, the problem could be hardware related.
This diagnostic tool (from Microsoft) helped me out one time by finding a bad ram chip…
Windows Memory Diagnostic
Even if your problem is something else (most likely) this tool is nice to have in the drawer.
Good Luck,
‘–
Jim Cone
Portland, Oregon USA
Excel add-ins

Re: “What does that mean?”

I don’t know. And my idea bag is pretty limited.
You should make sure your printer drivers are up to date and if that
makes no difference try another printer on your system.
‘–
Jim Cone
Portland, Oregon USA
Special Sort add-in

1. Clearing the Windows Temp file won’t hurt anything and off times makes most things better… Start | Run | %temp%
2. Does the problem occur with just one/two particular files?
….If so what is the file size? Are there are large number of charts or objects… Home (tab) | Find & Select | Go to Special | Objects
….Are there a large number of Styles? 50 or so is normal in xl2007, several thousand and you are in trouble.
3. xl2007 comes with a “Diagnostics” utility… Big Round Button | Excel Options (button) | Resources | Run…Diagnostics.
‘–
Jim Cone
Portland, Oregon USA
Cleanup Formats/Styles add-in

=MOD(INT((ROW()-1)/3),2)=1
‘–
Jim Cone
Portland, Oregon USA
Shade Data Rows add-in

0.## as a custom number format will do that.
The decimal point will be displayed, however, in all numbers…
2 as 2.
‘–
Jim Cone
Portland, Oregon USA
Special Sort add-in

MS changed the syntax for sorting in xl2007.
It added no real value. They have no shame.
The following should work in all XL versions from xl97 thru xl2007.
There may be other “improvements” in xl2010 ?
‘–
Sub UniversalSort()
With ActiveSheet
.Columns(“A:F”).Sort key1:=.Range(“E2″), _
order1:=xlDescending, header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
‘–
Jim Cone
Portland, Oregon USA
Review: Special Sort add-in

Suggest you change…
lLinkTest = InStr(1, lCell.Value, “.xls]”)

To…
lLinkTest = InStr(1, lCell.Formula, “.xls]”)

Also that check will always return 0 for a xl2007 workbook extension.
You might want to just check for both a bracket and an exclamation point.

Your check will be quick but not complete as you can also have links in charts, shapes, hyperlinks, names, pivot tables and ?
‘–
Jim Cone
Portland, Oregon USA
( Special Sort add-in )

XL2003 has a Link Sources method that returns an array of links in a workbook.
I haven’t tested it, but I suspect it may not find every type of link possible.
For something quick and dirty you can do a UsedRange search for a bracket… “[”
‘–
Jim Cone
Portland, Oregon
XL add-ins

The Find Link Excel add-in from Bill Manville was updated in February 2009.
I have not tried it on xl2007 maybe you should and let us know…
FinkLink

Jim Cone
Portland, Oregon USA
Special Sort

Your goal seek cell (E19) does not have a formula in it. So changing other cells will not have any effect on that cell.