Introduction:
When creating routines, it is commonly helpful to check if a file or directory (folder) exists before attempting to perform some action. Searching the internet will yield several examples of doing either, but this is a routine that I wrote in order to have one routine to test either.

Macro Purpose:

1. Check if the supplied path to a file or directory (folder) exists.

Examples of where this function shines:

1. When you need to check if a file OR directory exists before you attempt to so something with it (such as load a file).

Introduction:
This is one of my favourite functions to use in my projects. While VB's standard GetOpenFilename works great if you are in the correct directory, this saves my users time as I can tell it exactly where I want the dialog box to start looking... a very handy thing if you are opening files from different paths.

Macro Purpose:

1. Function to ask the user for a file name, but opening the window at a specific directory.

Examples of where this function shines:

1. When you need to give the users options to open files from a variety of paths.

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.)
   

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: