The Excel Page

Here are some of the routines we use everyday.

At Affordable Solutions, we pride ourselves in giving users the most complete information.
This is why we have developed a full series of Excel Tutorials:
Visit our Web-Site at http://www.affordsol.be for more information.

How to contact us!

CONTENTS

    Contents   

Excel Databases

• Export an Excel WorkBook to an ASCII Text File
  
• Fill the Cells of an Excel WorkSheet (populate the Worksheet)
  
• Copying and Sorting the Copy of an Excel WorkSheet
  
• Function: Sizing a record to its prefixed length
  
• Assigning a Macro to Double_Click in a List
  
• Removing the Macro to Double_Click in a List
  
• Selecting an Element of a List w/Double_Click
  
• Scroll to the Right
  
• Scroll to the Left
  
• Go to Previous Record
  
• Go to Next Record
  
• Write an ASCII CommaDelimited Table Text File
  
• Convert an Ascii Fixed Length datafile to an Ascii Comma Delimited datafile
  
• Naming Fields: this is a part of the CommonReadSub
  
• Function: Extract a fixed length field from a fixed length record
  
• Read an ASCII FIXED LENGTH datafile (which looks as a table)
  
• Read a Comma Delimited Ascii datafile
  
• Same as ReadAFL as extensions .dat and .afl cover the same datatype
  
• Read an ASCII TAB DELIMITED datafile
  
• Read an ASCII SPACE DELIMITED datafile
  
• Read an ASCII SEMI COLON DELIMITED datafile
  
• Read an XLS worksheet datafile
  
• Common Read Subroutine (used with all Read routines)
  

    Contents   

Cells and their contents

• Fill the Cells of an Excel WorkSheet (populate the Worksheet)
  
• Using Arrays and Ranges
  
• Get a value from a Cell or a Range of a CLOSED Workbook
  
• Sums up a given range of the worksheet
  
• Removes the numbers from a range: not the formulas
  
• Copy a range to another range
  
• Find Last Cells in a Worksheet
  
• Format a cell for EUROS
  
• Coloring Text, Cells, Ranges in a WorkSheet
  
• Text Color of Cell(s) in a range
  
• Function: Returns integer = cell's interior color (not the text color!)
  
• Text Bold of Cell(s) in a range
  
• Unbold Text of Cell(s) in a range
  
• Underline Text of Cell(s) in a range
  
• Put Borders around Cell(s) in a range
  
• Get the real last used column and last used row
  
• Function: Returns the cell type of the upper left cell in a range
  
• Function: Returns Last Cell in a given Column
  
• Function: Returns Last Cell in a given Row
  
• Function: Returns TRUE if cell has a formula
  
• Function: Returns cell's formula or an empty string if cell has no formula
  
• Function: Returns TRUE if cell is hidden
  
• Function: Returns TRUE if cell is bold
  
• Function: Returns TRUE if cell is italic
  
• Function: Returns TRUE if all characters in cell are bold
  
• Function: Returns an acronym for text
  
• Function: Returns true if the first argument is like the second
  
• Function: Returns TRUE if cell contains a string or cell is formatted as Text
  
• Function: Returns nth element of a text string, where elements are separated by specified separator character
  
• Function: Returns its argument, reversed
  
• Function: Scrambles its single-cell argument
  

    Contents   

SPECIAL: Fields, Formulas, Array Formulas

• Naming your Fields (Ranges)
  
• Using Array Formulas
  
• Copying Formulas and Array Formulas
  
• Writing Formulas with VBA code
  
• Assign a Range to an Array
  
• Function: Retrieving a Value in an Array
  
• AutoFiltering Lists
  

    Contents   

Files & Drives

• Function: Returns True if a specified drive letter exists
  
• Function: Returns a string that describes the drive type
  
• Function: Check if a given file exists
  
• Function: Extract a filename from the path
  

    Contents   

Sheets & Applications Functions

• Fill the Cells of an Excel WorkSheet (populate the Worksheet)
  
• Set up your own Headers or Footers by code
  
• Get a value from a Cell or a Range of a CLOSED Workbook
  
• Resets all sheets of a workbook to A1
  
• Sums up a given range of the worksheet
  
• Create a new worksheet in the active workbook
  
• Print the active worksheet
  
• Clearing the contents of a WorkSheet
  
• Function: Returns the sheet name of the cell that contains the function
  
• Function: Returns the workbook name of the cell that contains the function
  
• Function: Returns the application name of the cell that contains the function
  
• Function: Returns Excel's version number
  
• Export Sheet to New WorkBook
  
• Using Excel Functions in VBA
  
• Check if a given Sheet Exists
  

    Contents   

Multi Sheets Functions

• Saving All Workbooks open in Excel
  
• Resets all sheets of a workbook to A1
  
• Create a new worksheet in the active workbook
  
• Add (create) a new workbook
  
• Function: Returns the MAX of all sheets
  
• Copying and Sorting the Copy of an Excel WorkSheet
  
• Function: Returns cell contents at Ref, in sheet offset
  
• Function: Returns the Worksheets (not Sheets) Index
  
• Check if a given Sheet Exists
  

    Contents   

Counting & Summing

• Function: Counts number of values between num1 and num2
  
• Function: Counts visible cells
  
• Function: Sums only visible cells
  
• Function: Emulates Excel's SUM function
  

    Contents   

Date Functions

• Function: NEXTMONDAY(d As Date) As Date
  
• Function: Returns the next specified day
  
• Function: Returns the week of the month for a date
  

    Contents   

Miscellaneous

• Assigning a Macro to Double_Click in a List
  
• Removing the Macro to Double_Click in a List
  
• Clear the immediate window
  
• Closing an application
  
• Function: Cutting a Chain Left or Right of a given character
  
• Put some text on the status bar
  
• Write some text (strin) to the log file c:\ExcelLog.txt
  
• Function: Returns the dimension(settings) of the screen
  
• Function: RANDOMINTEGERS()
  
• Using Excel Functions in VBA
  
• FIND A MATCHING ELEMENT IN A LIST
  
• LIST ALL THE NAMED Ranges in a New WorkSheetof the WorkBook
  
• Computing the Value Added Tax
  

    Contents   

Public Sub ExportWorkBookAsTextFile()

    Contents   

Public Sub FillCells()

    Contents   

Public Sub Sort_a_Sheet()

    Contents   

Public Function SizeRecordField(ByVal iptr As Integer, ByVal istr As String) As String

    Contents   

Public Sub WipeSheet()

    Contents   

Public Sub CloseApp()

    Contents   

Public Sub dbcOn()

    Contents   

Public Sub dbcOff()

    Contents   

Public Sub SelectClient()

    Contents   

Public Sub ScrollRight()

    Contents   

Public Sub ScrollLeft()

    Contents   

Public Sub PreviousRecord()

    Contents   

Public Sub NextRecord()

    Contents   

Public Sub WriteDelimited()

    Contents   

Public Sub LastCells_in_Sheet()

Public Function lng_LastRow()
   lng_LastRow = Range("A:Z").End(xlDown).Row
End Function

    Contents   

Public Sub FormatCellEuro()

    Contents   

Public Sub AFLtoCOD()

    Contents   

Sub CommonReadSub()

    Contents   

Public Function ChainCut(strin As String, sense As String, what As String)

    Contents   

Public Function FileExists(filename) As Boolean

    Contents   

Public Function FldExtract(strin As String, ByVal fldidx As Integer, trimmer As String)

    Contents   

Sub ReadAFL()

    Contents   

Sub ReadCOD()

    Contents   

Sub ReadDAT()

    Contents   

Sub ReadTAD()

    Contents   

Sub ReadSPD()

    Contents   

Sub ReadSCD()

    Contents   

Sub ReadXLS()

    Contents   

Sub CopyRangeToRange(srcrange as String, destrange as String)

    Contents   

Sub PrintSheet()

    Contents   

About Coloring Cells Backgrounds and/or Cells Texts
             Making fonts Bold and/or Underlining the fonts
 Setting Borders anywhere around a Cell

As an example, we'll compute the last cells of the Active Sheet
and calculate the sum of columns B and C

    Contents   

Color of the text in Cell(s)

See my snippet of code at Coloring Text, Cells, Ranges in a WorkSheet

    Contents   

Bold Text in Cell(s)

See my snippet of code at Coloring Text, Cells, Ranges in a WorkSheet

    Contents   

Unbold Text in Cell(s)

See my snippet of code at Coloring Text, Cells, Ranges in a WorkSheet
And change the existing code

    Contents   

Underline Text in Cell(s)

See my snippet of code at Coloring Text, Cells, Ranges in a WorkSheet

    Contents   

Putting Border(s) around Cell(s)

See my snippet of code at Coloring Text, Cells, Ranges in a WorkSheet

    Contents   

Sub WriteToLogFile(strin As String)

    Contents   

Sub ClearImmediateWindow()

    Contents   

Sub ShowOnStatusBar()

    Contents   

Sub AddAWorkbook()

    Contents   

Function GetFileNameFromPath(stfullname As String) As String

    Contents   

    Contents   

Public Function lng_LastRow()
   lng_LastRow = Range("A:Z").End(xlDown).Row
End Function

    Contents   

    Contents   

    Contents   

About Using Arrays and Ranges

As an example, we'll compute the last cells of the Active Sheet
and put data in an array and see how to use it

    Contents   

    Contents   

Public Function CELLTYPE(cell)

    Contents   

Public Function LASTINROW(rng As Range) As Variant
   Dim WorkRange As Range
   Dim i As Integer, CellCount As Integer
   Application.Volatile
   Set WorkRange = rng.Rows(1).EntireRow
   Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
   CellCount = WorkRange.Count
   For i = CellCount To 1 Step -1
      If Not IsEmpty(WorkRange(i)) Then
         LASTINROW = WorkRange(i).Value
         Exit Function
      End If
   Next i
End Function

Public Function lng_LastRow()
   lng_LastRow = Range("A:Z").End(xlDown).Row
End Function

    Contents   

Public Function LASTINCOLUMN(rng As Range) As Variant
   Dim WorkRange As Range
   Dim i As Integer, CellCount As Integer
   Application.Volatile
   Set WorkRange = rng.Columns(1).EntireColumn
   Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
   CellCount = WorkRange.Count
   For i = CellCount To 1 Step -1
      If Not IsEmpty(WorkRange(i)) Then
         LASTINCOLUMN = WorkRange(i).Value
         Exit Function
      End If
   Next i
End Function

Public Function lng_LastRow()
   lng_LastRow = Range("A:Z").End(xlDown).Row
End Function

    Contents   

Public Function CELLHASFORMULA(cell) As Boolean

    Contents   

Public Function CELLFORMULA(cell) As String

    Contents   

Public Function CELLISHIDDEN(cell) As Boolean

    Contents   

Public Function ISBOLD(cell) As Boolean

    Contents   

Public Function ISITALIC(cell) As Boolean

    Contents   

Public Function ALLBOLD(cell) As Boolean

    Contents   

Public Function FILLCOLOR(cell) As Integer

    Contents   

Public Function REVERSETEXT(text) As String

    Contents   

Public Function SCRAMBLE(text)

    Contents   

Public Function ACRONYM(text) As String

    Contents   

Public Function ISLIKE(text As String, pattern As String) As Boolean

    Contents   

Function CellHasText(cell) As Boolean

    Contents   

Function ExtractElement(Txt, n, Separator) As String

    Contents   

'Naming fields

    Contents   

'UsingArrayFormulas
   Sheets("BalGen").Select
   GetRealLastCells

    Contents   

'CopyingFormulas

    Contents   

    Contents   

Public Sub AssignRangeToArray()

    Contents   

    Contents   

Sub CopyAndAutoFilter()

    Contents   

Public Function GetValue(path, file, sheet, ref)

Getting the Value of one Cell out of a closed Workbook ... and ... Getting the Value of a range of Cells out of a closed Workbook

Public Sub TestGetOneCellValue()
   Dim p, f, s, a, srcRow, srcCol, destRow, destCol
   p = "c:\Mes documents\xlworks\xltxt"
   f = "OneFormulaReport.xls"
   s = "Report"
   Application.ScreenUpdating = False

Public Sub TestGetRangeValue()
   Dim p, f, s, a, r, c
   p = "c:\Mes documents\xlworks\xltxt"
   f = "OneFormulaReport.xls"
   s = "Report"
   Application.ScreenUpdating = False

    Contents   

Sub ResetAllSheetsToA1()

    Contents   

Function COUNTBETWEEN(rng, num1, num2)

    Contents   

Function SUMVISIBLE(rng)

    Contents   

Function NEXTMONDAY(d As Date) As Date
   Dim TestDay As Date
   TestDay = d + 1
   Do Until WeekDay(TestDay) = 2
      TestDay = TestDay + 1
   Loop
   NEXTMONDAY = TestDay
End Function

    Contents   

Function NEXTDAY(d As Date, day As Integer) As Variant

    Contents   

Function MONTHWEEK(d As Date) As Integer

    Contents   

    Contents   

    Contents   

Function COUNTVISIBLE(rng)

    Contents   

Function MAXALLSHEETS(cell)
   Dim MaxVal As Double
   Dim Addr As String
   Dim Wksht As Object
   Application.Volatile
   Addr = cell.Range("A1").Address
   MaxVal = -9.9E+307
   For Each Wksht In cell.Parent.Parent.Worksheets
      If Wksht.Name = cell.Parent.Name And _
         Addr = Application.Caller.Address Then

    Contents   

Function SHEETOFFSET(offset, Ref)

    Contents   

Public Function WorksheetIndex(x As Worksheet) As Integer

    Contents   

Public Function MYSUM(ParamArray n() As Variant) As Variant

    Contents   

Public Function RANDOMINTEGERS()
   Dim FuncRange As Range
   Dim V() As Variant, ValArray() As Variant
   Dim CellCount As Double
   Dim i As Integer, j As Integer
   Dim r As Integer, c As Integer
   Dim Temp1 As Variant, Temp2 As Variant
   Dim RCount As Integer, CCount As Integer
   Randomize

    Contents   

Function SHEETNAME() As String

    Contents   

Function WORKBOOKNAME() As String

    Contents   

Function APPNAME() As String

    Contents   

Function EXCELVERSION() As Variant

    Contents   

You can create your own headers and footers:
       ActiveSheet.Pagesetup.Leftfooter = "my left footer"

       ActiveSheet.Pagesetup.Centerfooter = ActiveWorkbook.Name
       ActiveSheet.Pagesetup.Rightfooter = ActiveSheet.Name

       ActiveSheet.Pagesetup.Leftheader = "my left footer"

       ActiveSheet.Pagesetup.Centerheader = ActiveWorkbook.Name
       ActiveSheet.Pagesetup.Rightheader = ActiveSheet.Name

    Contents   

Public Sub SaveAll()

    Contents   

    Contents   

Using Excel's built-in Functions

    Contents   

List all Named Ranges in a New WorkSheet of the WorkBook
Public Sub ShowNames()

    Contents   

FIND A MATCHING ELEMENT IN A LIST
Public Sub ChercherClient()
   Dim searchme As String
   Dim searchedrow As Double

    Contents   

Compute the VAT

    Contents   

Check if a given Sheet Exists
Public Function SheetExists(shtname As String) As Integer
   SheetExists = 0
   tint2 = ActiveWorkbook.Worksheets.Count
   For ptr2 = 1 To tint2
      If Worksheets(ptr2).Name = shtname Then
         SheetExists = 1
         Exit Function
      End If
   Next ptr2
End Function

    Contents   

  CONTACTS !

 by    Affordable Solutions