Issue No. 09 (July 15, 1999)

[Item URL]

**********************************

COMMENTS

Welcome to the 9th issue of the Excel Experts E-letter (or EEE), by 
David Hager. EEE is a semi-monthly publication. Feel free to distribute
copies of EEE to your friends and colleagues. 

Back issues are available for download from the EEE web page located on John
Walkenbach's web site. New issues are normally available on the 1st and
16th of each month.

http://www.j-walk.com/ss/excel/eee/index.htm

**********************************

TOP EXCEL WEB SITES

An interesting product that allows Excel (version >5) to be used as an 
Internet data browser has just been released. You can find the details on 
this at:

http://www.inventure.com

**********************************

WORKSHEET FORMULA TIP

Created by Laurent Longre

This formula counts the number of cells in the discontiguous range that 
contain a value greater than 20.

=INDEX(FREQUENCY((A1,A3,A5),20),2)



**********************************

POWER FORMULA TECHNIQUE

Created by Jan Karel Pieterse

This is an example of how to pass arguments to defined formulas. It consists
of the named formula called Myref (see below), which evaluates the string of
the formula of the "active" cell (the cell that calls one of the other named
formulas in this example). In order to use these formulas, a trick is 
involved. The function must be called like this:

=IF(ROW(ref),NameOfTheDefinedFormula)


The Myref function finds the "Row(" part in the string of the formula and 
takes all text between that and the first closing paren to be a valid cell
reference. Myref in part is used as an argument to the other functions.

Define Myref as:

=MID(GET.CELL(6,A1),FIND("ROW(",GET.CELL(6,A1))+4,FIND(")",GET.CELL(6,A1))-
FIND("ROW(",GET.CELL(6,A1))-4)

Please take into account, that when defining this formula:
- your active cell HAS TO BE cell A1
- You should NOT use absolute refs (no dollar signs)
- You should NOT use sheetrefs ("Sheet1!")
- When editing Myref, you should REMOVE ALL sheetrefs XL places there itself.
Note, that Myref has to be adapted for other language versions of Excel, 
which may have a different word for the ROW function (change the string 
"ROW(" and the two 4's in the formula accordingly).

IsFormula
=GET.CELL(48,INDIRECT(myref)))+0*now()

CellColor
=get.cell(63,indirect(MyRef))+0*now()

example:

=IF(ROW(D3),CellColor)

Shows the colournumber of the background of cell D3

RowIsHidden
=IF(GET.CELL(17,INDIRECT(Myref))=0,TRUE,FALSE)+0*now()

RowHeight
=GET.CELL(17,INDIRECT(Myref))+0*NOW()


**********************************

VBA CODE EXAMPLES 

By John Green

This procedure finds cells on a worksheet containing data displayed as #####...

Sub FindIncorrectDataDisplay()

  Dim rng As Range
  For Each rng In ActiveSheet.UsedRange
    If IsNumeric(rng.Value) And Left(rng.Text, 1) = "#" Then
      MsgBox "Column too narrow for " & rng.Address
    End If
  Next rng

End Sub


By Nick Hodge

This procedure prints out all cell comments from a workbook.

First, create a text file on your desktop, (or change the reference in the 
code), called test.txt and run the code below.

This will write each comment, on all worksheets, with it's address and sheet
no. to the txt file and close it.  

Sub writeComments()
Dim mycomment As Comment
Dim mySht As Worksheet
Open "C:\Windows\Desktop\Test.txt" For Output As #1
For Each mySht In Worksheets
    For Each mycomment In Worksheets(mySht.Name).Comments
Print #1, "From " & mycomment.Parent.Parent.Name _
        & mycomment.Parent.Address _
            & " Comes the comment: " _
                & mycomment.Text
    Next mycomment
Next mySht
Close #1
End Sub--



By Laurent Longre

This procedure looks up the Windows 95 serial number.

Declare Function RegOpenKeyExA Lib "Advapi32" _
  (ByVal hkey As Long, ByVal lpszSubKey As String, _
  ByVal dwReserved As Long, ByVal samDesired As Long, _
  phkResult As Long) As Long

Declare Function RegQueryValueExA Lib "Advapi32" _
  (ByVal hkey As Long, ByVal lpszValueName As String, _
  lpwReserved As Long, lpdwType As Long, _
  ByVal lpbData As String, lpcbData As Long) As Long

Declare Function RegCloseKey Lib "Advapi32" _
  (ByVal hkey As Long) As Long

Sub Win95SerialNumber()

  Dim hkey As Long
  Dim Buffer As String
  Dim lgBuf As Long
  
  If RegOpenKeyExA(&H80000002, "Software\Microsoft\Windows" _
    & "\CurrentVersion", 0, &H960277, hkey) Then Exit Sub
  RegQueryValueExA hkey, "ProductId", 0, 1, Buffer, lgBuf
  Buffer = Space(lgBuf)
  If RegQueryValueExA(hkey, "ProductId", 0, 1, Buffer, lgBuf) = 0 _
    Then MsgBox "Serial number = " & Buffer
  RegCloseKey hkey

End Sub


By Chip Pearson

This procedure removes tabs and carriage returns from cells in the active 
worksheet.

Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
    With TheCell
    If .HasFormula = False Then
        .Value = Application.WorksheetFunction.Clean(.Value)
    End If
    End With
Next TheCell
End Sub

By Mark Lundberg

The following procedure is a workaround to the lack of a straightforward 
programmatic way to turn off the 'Break on Unhandled Errors in Class Module'
option in the VBE.

Sub AClassCanBeAPainInThe()
    Application.SendKeys "%{F11}%TO+{TAB}{RIGHT 2}%E~%{F4}"
End Sub

**********************************

POWER PROGRAMMING TECHNIQUES

Created by Laurent Longre

This example shows how to register functions into user-defined catagories and
provide descriptions for their arguments. The Auto_Open procedure registers 
the two functions, Multiply and Divide in two categories Multiplication and 
Division and provides descriptions of the input parameters.

Const Lib = """c:\windows\system\user32.dll"""
Option Base 1

Private Function Multiply(N1 As Double, N2 As Double) As Double
  Multiply = N1 * N2
End Function

'==========================================

Private Function Divide(N1 As Double, N2 As Double) As Double
  Divide = N1 / N2
End Function

'==========================================

Sub Auto_open()

  Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
    "Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
  Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
    "Multiplies two numbers", """First number"",""Second number """, _
    "CharNextA"

End Sub

'==========================================

Sub Register(FunctionName As String, NbArgs As Integer, _
  Args As String, MacroType As Integer, Category As String, _
  Descr As String, DescrArgs As String, FLib As String)
  
  Application.ExecuteExcel4Macro _
  "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
  & """,""" & FunctionName & """,""" & Args & """," & MacroType _
  & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"

End Sub

'==========================================

Sub Auto_close()
  
  Dim FName, FLib
  Dim I As Integer
  FName = Array("DIVIDE", "MULTIPLY")
  FLib = Array("CharPrevA", "CharNextA")
  For I = 1 To 2
    With Application
      .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
      .ExecuteExcel4Macro "REGISTER(" & Lib & _
        ",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
      .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
    End With
  Next
  
End Sub

Created by Bob Umlas and adaptation to a Function procedure 
by John Walkenbach

This procedure allows for the access of information in a closed workbook
with VBA.

From Bob:

Here's a routine you can incorporate into your programs if you ever have
a need to retrieve data from a file without opening it. In the needed case, 
I had to read any number of files (depending on user's selection from a 
listbox) and determine whether range W11:W36 on sheet "General"
totalled zeroand do one thing if ANY file met that condition or another 
thing if not.

At first, I thought I'd need to open each file, take the sum, then close
it again.

Not true.

By building a string which you can pass into the ExecuteExcel4Macro, you
can access this info directly, without opening the file, making it very fast:

Sub GetDataFromClosedFile()
    filepath = "G:\fsoft\sos\data\ley"
    FileName = "1cA10.sos"  '<==this could change in a loop
    sheetname = "General"
    Strg = "sum('" & filepath & "\[" & FileName & "]" & sheetname &
	"'!r11c23:r36c23)"
    MsgBox ExecuteExcel4Macro(Strg)
End Sub

'In reality, it looked like this:
Sub GetDataFromClosedFile()
    filepath = "G:\fsoft\sos\data\ley"
    sheetname = "General"
    For Each Fl In DialogSheets("DlgMulti").ListBoxes("MainList").List
        Strg = "sum('" & filepath & "\[" & Fl & "]" & sheetname &
	"'!r11c23:r36c23)"
        Ans = ExecuteExcel4Macro(Strg)
        If Ans > 0 Then Exit Sub
    Next
    'none > 0
    '...rest of code goes here
End Sub


From John:

The GetValue function, listed below takes four arguments:


path:  The drive and path to the closed file (e.g., "d:\files") 
file:  The workbook name (e.g., "99budget.xls") 
sheet: The worksheet name (e.g., "Sheet1") 
ref:   The cell reference (e.g., "C4") 


Private Function GetValue(path, file, sheet, range_ref)

'   Retrieves a value from a closed workbook 
   
	Dim arg As String

'   Make sure the file exists 
   
	If Right(path, 1) <> "\" Then path = path & "\"

    	If Dir(path & file) = "" Then        
		GetValue = "File Not Found"
        	Exit Function    
	End If

'   Create the argument

    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(range_ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro

    GetValue = ExecuteExcel4Macro(arg)

    End Function

**********************************

EXCEL 2000 INFORMATION

A workbook containing a hyperlink to a blank worksheet in the same 
workbook, when saved as a Web page and viewed it in a Web browser, will 
do nothing when you click the hyperlink. Excel does not save blank pages
by design when a workbook is saved as a web page to conserve disk space.
You must add some text to the blank page prior to saving it in this way 
for the hyperlink to work. For more information on this, see:

http://support.microsoft.com/support/kb/articles/q221/0/64.asp

**********************************

DID YOU KNOW?...

that aside from the new COM add-ins in Office 2000 and using complied 
xll/dll's, there is no good way to protect the code associated with 
applications built in Excel. There are password crackers/retrievers 
available that can hack the password from most if not all versions of Excel.
However, there is a way to add a layer of protection to your password for a 
VB Project in Excel by using unprintable ASCII characters. The following 
list shows those characters that can be used:

Alt-0128, Alt-0129, Alt-0141, Alt-0142, 
Alt-0143, Alt-0144, Alt-0157, Alt-0158

There is no visual way to tell the difference among these characters, so a 
password of suitable length will provide additional protection. It is likely
that code crackers can be made to display the corresponding ASCII code, but 
at least it makes the password-breaking process a bit more difficult.

**********************************

Issue No.9 OF EEE (PUBLISHED 15Jul1999)
Next issue scheduled for 04Aug1999.
BY David Hager
dchager@compuserve.com

**********************************