Introduction:
This macro can be used to count how many files exist in a folder and subfolders. In addition, if passed the file extension, it can also be used to count only files of a specific type. (For example "xls" files.) If you are looking for a macro that counts files within a specific folder only (ignoring subfolders), please see this entry.

Macro Purpose:

1. Count all files in a folder and subfolder
2. Count all files of a specific type in a folder and subfolders

Examples of where this function shines:

Introduction

How many times have you built a table of data, and put totals on it. You ship it out the door, and another user comes along and inserts a new row right before the totals row? If you have seen this in play, you'll know that the new row is actually inserted between the end of the data range and the totals row, meaning that your sum formula no longer picks up the entire range of data!

Fortunately, the issue with missing rows in totals can easily be fixed so that a user can insert a row immediately above your totals without missing the last line(s). Here's how:

Create a named range

What are dates?

This may seem like a strange thing to ask but, as far as Excel is concerned, dates are numbers. By storing them as such, it gives us the ability to add or subtract days to/from a date, as well as get the difference between two dates. If dates were stored as text, this would not be possible. Storing dates as numbers also allows us to construct far more complicated formulas, based on results that we may want to know.

The first thing that we need to know is how to recognize if a date has been entered into a cell as a number, (which will be interpreted by Excel as a date,) or text, which won't. Probably the quickest is to look at the alignment of the cells.

Introduction:
This macro can be used to count how many files exist in a folder and, if passed the file extension, can also be used to count only files of a specific type. (For example "xls" files.) I've actually published two articles along this vein at [url="http://www.vbaexpress.com"]VBA Expresss[/url], but this one uses the File System Object (some know it as the File Scripting Object, or just FSO,) to do it. The biggest reason for the re-write is that I needed to use this in an Excel 2007 instance, which does not support the FileSearch method.

If you are looking for a version to count the files in a folder AND subfolders, then look here.

Formula Purpose:

1. This function takes a series of letters and returns the average of them, i.e., the average of A, B, C is B.

Examples of where this function shines:

1. The only application that I can see for this formula is for averaging grades. If you know another, please click here and send me an email to let me know!

Formula Weakness(es):

1. It only works on a single contiguous range on a worksheet, not multiple ranges.
2. Must remember to enter as an array formula.
3. Only operates on the first character in each cell.

Macro Purpose:

1. Function to show the formula in another cell as text.

Examples of where this function shines:

1. In Excel's View options (Tools|Options|View), you can check a box to show all formulas on the worksheet as formulas, rather than their resulting values. The problem is that this is worksheet level, and sometimes that may not be desireable. This function allows you to show the formulas on the worksheet if you desire.
2. As an example of a practical use for this function, I have used it throughout this site to show what formulas I use in my examples.

Macro Weakness(es):

1. None identified at this time.

Versions Tested:
This function has been tested with Excel 97, and Excel 2003, and should also work with Excel 2000 and Excel 2002 (XP) without any modifications.

VBA Code Required:

1. Place the following code in a standard module of the workbook you wish to use it in.
   

   Function showformula(rng As Range)
   'Author          : Ken Puls (www.excelguru.ca)
   'Function Purpose: To show a range's formula in a cell
       If rng.HasArray = True Then
           showformula = "{" & rng.Formula & "}"
       Else
           showformula = rng.Formula
       End If
   End Function
   

How to use the code:

1. Enter the formula in a worksheet cell in the format =SHOWFORMULA(Range)
2. Click on the formulas in the example below to see how the results were generated.
   (The actual formula used will change in the Formula Bar.)
   

This page is dedicated to explaining how to use what I believe are the five most valuable formulas for working with text in Excel. They are useful on their own many times, but can become immensly powerful when nested (combined) with other formulas later as well. Mastering these five formulas will open up the door to many things that you may have never thought possible. All of these formulas can be used by putting actual text in the "text" area, but their true power is unlocked when using them on cell references as the data can then be dynamic.

For all of the following discussions, the syntax of the formulas will be displayed with all required portions in bold, and optional portions within square brackets and in regular type.

Macro Purpose:

1. To round a Sum formula to two decimal places

Examples of where this function shines:

1. When you are feeling really lazy, and don't want to type =Round(Sum(A1:A3),2), you can just type =Sumr(A1:A3)

Macro Weakness(es):

1. Will calculate slower than the nested Round and Sum combination. Performance degridation most likely won't be noticed if it is only used a few times, but if hundreds of formulas are used, it may slow the calculation down noticeably.
2. Only accepts range arguments, not values.

Function Purpose:

This function can be used to retrieve one of three types of usernames, and can be used in a worksheet cell or in VBA. The usernames that it can return are:

1. Microsoft Office username (default) as defined in Tools|Options|General
2. Microsoft Windows logon name
3. Computer name (as defined in your system properties)

Examples of where this function shines:

1. When designing templates for use by multiple users
   
   1. Automate the task of entering the user's name(s) on the worksheet!
   
   
2. When building macros
   
   1. Have your code execute a certain way depending upon the logged in user, and/or the machine that they are logged in on!

Function Weakness(es):

1. The Microsoft Office username must be entered correctly (defined in Tools|Options|General) or it will return incorrect results
2. Running in an environment where people log in sharing a Windows profile doesn't work well, as the computer can't identify who the user really is
3. Must use the Application.Volatile keywords to force recalculation, or the function won't recalculate when the template is opened (unless force-coded to do so.)

Versions Tested:

This function has been tested with Excel 97, and Excel 2003, and should also work with Excel 2000 and Excel 2002 (XP) without any modifications

VBA Code Required:

The following should be copied into a standard code module:

Option Explicit

Function GetName(Optional NameType As String) As String
     'Author          : Ken Puls (www.excelguru.ca)
     'Function purpose: To return the following names:
     'Defaults to MS Office username if no parameter entered

     'Formula should be entered as =GetName([param])

     'For Name of Type           Enter Text    OR    Enter #
     'MS Office User Name        "Office"             1 (or leave blank)
     'Windows User Name          "Windows"            2
     'Computer Name              "Computer"           3

     'Force application to recalculate when necessary. If this
     'function is only called from other VBA procedures, this
     'section can be eliminated. (Req'd for cell use)
     Application.Volatile

     'Set value to Office if no parameter entered
     If Len(NameType) = 0 Then NameType = "OFFICE"

     'Identify parameter, assign result to GetName, and return
     'error if invalid
     Select Case UCase(NameType)
          Case Is = "OFFICE", "1"
               GetName = Application.UserName
               Exit Function
          Case Is = "WINDOWS", "2"
               GetName = Environ("UserName")
               Exit Function
          Case Is = "COMPUTER", "3"
               GetName = Environ("ComputerName")
               Exit Function
          Case Else
               GetName = CVErr(xlErrValue)
     End Select

End Function

Referencing The Formula:

1. The formula can be referenced in worksheet cells as shown in the example below:

Microsoft Excel - GetName demo.xls ___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C4E4C5E5C6E6C7E7 =
	A	B	C	D	E
1		Parameter Supplied
2		Using Words		Using #'s
3	Name	Formula is:	Results Are:	Formula is:	Results Are:
4	Microsoft Office	=GetName("office")	Ken Puls	=GetName(1)	Ken Puls
5	Windows	=GetName("windows")	Ken Puls	=GetName(2)	Ken Puls
6	Computer	=GetName("computer")	KEN	=GetName(3)	KEN
7	No parameter given	=GetName("")	Ken Puls	=GetName()	Ken Puls
Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box