LIVEEXCEL - Excelling Ideas!!

Shortcut Key to add the numbers in a contiguous range of cells

2008-11-25T03:54:00.001+05:30

Excel's Sum function is one of the most favorite function of many users. Rather than linking to each cell in your formula, Sum function is the easiest way to add the numbers. It can be done in more shortest way by just pressing shortcut keys instead of typing formula.

On your Keyboard press shortcut key 'ALT' + '=' as shown in the figure below. Formula will be applied automatically to the range of cells above your formula cell. Press Enter to see the result thereafter.

Convert your Excel worksheet to PDF format

2008-09-18T11:55:00.014+05:30

After the launch of Office 2007, Microsoft has released a free add-on long back that allows its users to Export their Word document or Excel worksheet into PDF format file. After installing this free add-on you can save your Excel file as PDF or XPS file.

Here I today give you the code which will convert your Excel worksheet as PDF file...

To enable this feature you should first download the free add-on from here. After installing try with the code below...


Sub Convert_Excel_2_PDF()
'
'   AutoMax VBA - Excelling Ideas!!
'   http://automax-vba.blogspot.com
'
'   Convert Excel worksheet to PDF
'   Author: Ilyas Kazi

    Dim sFilePathnName As String

    With ActiveWorkbook
    '   Current Excel file Location and File name to Save as PDF File:
        sFilePathnName = .Path & Application.PathSeparator & Left(.Name, Len(.Name) - 4) & "pdf"

    '   Set your Location and File name to Save as PDF File:
        'sFilePathnName = "C:\MyFolder\MyFile.pdf"
    
    '   Conversion Code...
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=sFilePathnName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    End With
    
    MsgBox "File converted successfully", vbInformation, "AutoMax"
End Sub

Please note the above code or feature is available only for Excel 2007 users.

2009 Calendars to download in Excel and Word

2008-08-12T14:20:00.021+05:30

Are you...?

planning your meetings for year 2009
planning your traveling dates for year 2009
or planning your new revolution for year 2009

If yes, then you need Year 2009 Calendars template in Excel or Word. Here I have added four templates (2 in Excel and 2 in Word). Simple design - No need of any macro or VBA code here.

2009 Calendar in Excel format

Version Supports: Excel 2003 or Later
Details: One Sheet, Landscape, Mon-Sun

2009 Calendar in Excel format

Version Supports: Excel 2003 or Later
Details: Multiple Work Sheets, Mon-Sun

2009 Calendar in Word format

Version Supports: Word 2003 or Later
Details: Multiple Pages, Mon-Sun

2009 Calendar in Word format

Version Supports: Word 2003 or Later
Details: Blue Design, Multiple Pages, Mon-Sun

Download all the above Calendars here

Is your Excel workbook has hidden worksheets?

2008-08-11T19:11:00.009+05:30

Are you curious to know whether you received Excel file has any hidden worksheets? There may be some important data or secrets hidden in hidden Excel worksheet.

Follow the below easy steps in Excel 2003 or Excel 2007...

In Excel 2003 follow the steps to unhide sheets:

Goto 'Format' menu in your target excel workbook
Open 'Sheet' sub-menu
Click 'Unhide...' *
You will be provided a list box containing all the sheet names which are set to hidden
Select your Sheet name from the list and click ok.

In Excel 2007 follow the steps to unhide sheets:

Right-Click any sheet in your target excel workbook
Click 'Unhide...' *
You will be provided a list box containing all the sheet names which are set to hidden
Select your Sheet name from the list and click ok.

* You cannot click on 'Unhide' if this name is disable due to not found any hidden worksheets.

Function to check whether Excel file exist

2008-07-22T17:23:00.009+05:30

The below function will let you know whether your file exist in the given location...

Function FileExists(sFilePath As String) As Boolean
    If Len(Dir(sFilePath)) = 0 Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

Try this code below to test above function...

Sub Test_IsFileExists()
    MsgBox FileExists("C:\Book1.xls")
End Sub

Function to know whether any Excel file is opened

2008-07-21T17:49:00.012+05:30

To know whether any Excel workbook is kept opened through VBA, see the below function...

Function IsAnyWbkOpen() As Boolean
Dim wb As Workbook
IsAnyWbkOpen = False

For Each wb In Workbooks
  If Windows(wb.Name).Visible = True Then     '...is not hidden*
      IsAnyWbkOpen = True
      Exit Function
  End If
Next wb

End Function

Type the below procedure to test the above function...

Sub Test_IsAnyWorkbookOpened()
MsgBox IsAnyWbkOpen

End Sub

*Note: Remove the If..Loop from the above code if you want to know any hidden file is also kept opened.

How to run Macro on specific Time

2008-07-21T15:38:00.011+05:30

Do you want to run your existing Macro/VBA code on specific time? If yes, then below example code demonstrates for the same...

This example runs 'MyMacro' at 3 P.M.

Sub RunThisMacroOnTime()
    Application.OnTime Now + TimeValue("15:00:00"), "MyMacro"
'MyMacro.. is your existing macro code procedure

End Sub

This example runs 'MyMacro' 5 seconds from now.

Sub RunThisMacroOnTime()
    Application.OnTime Now + TimeValue("00:00:05"), "MyMacro"
'MyMacro.. is your existing macro code procedure

End Sub

Your existing Macro/VBA code...

Sub MyMacro()
'Your code here...
End Sub

Function to know whether Excel Sheet is AutoFiltered

2008-07-21T14:24:00.007+05:30

While copying whole sheet data to another sheet you may want to know first whether the require Excel sheet is AutoFiltered. There are some more similar tasks for which you may want to know the same. Here is the quick solution with the below function...

Function FilteredRowsCount(ByVal WkSh As Worksheet)
    
    If WkSh.AutoFilterMode = False Then
        FilteredRowsCount = 0
    Else
        'Application.ScreenUpdating = True
        FilteredRowsCount = WkSh.AutoFilter.Range. _
        Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    End If
    
End Function

Type the below procedure to test the above function...

Sub Test_IsSheetAutoFilter()
    If FilteredRowsCount("Sheet1") > 0 then 'Type sheet name
        MsgBox "True"
    Else
        MsgBox "False"
    End If

End Sub

Function to return Column name for its Column number in Excel

2008-07-21T11:22:00.009+05:30

Developers find easy using Column name instead of Column number in VBA projects. However to return the column name dynamically is the trick. See the below small function on how to return column name for its column number...

Please copy the below function and paste in your module (Excel VBE)

Function GetXLCol(ColNumber As Long) As String
    
On Error Resume Next
    GetXLCol = Application.Substitute(Application.ConvertFormula("R1C" & ColNumber, _
               xlR1C1, xlA1, 4), "1", "")  '...also works for chart or invisible workbooks

End Function

Type the below procedure to test the above function...

Sub Test_ColumnName()
MsgBox GetXLCol(cStr(ActiveCell.Column))

End Sub

Keep any Excel cell active and run the above code to know is column name.

The above function is tested in Excel 97, Excel 2000, Excel 2003, and Excel 2007.

Function to check if Folder exist through Excel VBA

2008-07-19T12:28:00.006+05:30

With the function below you will be able to know whether a Folder exist through Excel VBA.

Function FolderExist(FolderLocation As String) As Boolean
    Dim CurrentFolderLen
  
    On Error GoTo BadPath
    CurrentFolderLen = Len(VBA.Dir(PathName:=FolderLocation, Attributes:=vbDirectory))
        
BadPath:
    If Err.Number <> 0 then CurrentFolderLen = 0
    If CurrentFolderLen = 0 Then FolderExist = False Else FolderExist = True
    
End Function

Type the below procedure to test the above function...

Sub Test_FolderExist()
MsgBox FolderExist("C:\MyFolder")

End Sub

Function to check if Excel file is opened

2008-07-19T12:06:00.010+05:30

Below function will help you to know whether the target Excel file is opened. Copy the below function and its procedure in your Excel VBE.

Function IsWorkbookOpen(wbName As String) As Boolean
   Dim wb As Workbook
  
   For Each wb In Application.Workbooks
       If UCase(wb.Name) = UCase(wbName) Then
           IsWorkbookOpen = True
           Exit Function
       End If
   Next wb
  
   IsWorkbookOpen = False
End Function

To test above function type your file name along-with its extension as given below in the procedure...

Sub Test_IsWorkbookOpenInXL2003()
   MsgBox IsWorkbookOpen("MyFile.xls")

End Sub

If your file is created in Excel 2007 then you should type with following extension...

Sub Test_IsWorkbookOpenInXL2003()
   MsgBox IsWorkbookOpen("MyFile.xlsx")

End Sub

Create Directory or Sub-Directories using Excel VBA

2008-07-18T17:04:00.009+05:30

See the following example on how to create directory and sub-directories through Excel VBA:

Sub MakeDirectories()
'   Declare variable for creating folders...
 Dim Dir1 As String, Dir2 As String, Dir3 As String

'   Assign directories location and its name...
 Dir1 = "C:\MainFolder"
 Dir2 = Dir1 & "\SubFolder1"
 Dir3 = Dir1 & "\SubFolder2"

'   Create folders...
 On Error Resume Next
     MkDir (Dir1)
     MkDir (Dir2)
     MkDir (Dir3)    
 On Error GoTo 0

End Sub

Turn off displaying Excel's warning messages using VBA

2008-07-18T16:13:00.013+05:30

Do not want Excel to display warning messages such as to confirm for file saving before closing or for any other process?

You should type the following line of macro code in your Excel VBE to turn off:

Application.DisplayAlerts = False

To enable the warning messages again you should type following line of macro code:

Application.DisplayAlerts = True

Count length of Cell value in Excel

2008-07-18T15:34:00.007+05:30

You can count the length of value typed in any Excel Cell.

See Example:

Type any text in Cell-A1
Type the formula in Cell-B1 =LEN(A1) and press Enter

See Screenshot | Count number of length in Excel Cell:

Convert your Excel Cell text to Lower case with formula

2008-07-18T11:44:00.010+05:30

You can change the case of your Excel Cell text to Lower case with formula itself.

See Example:

Type any text in Cell-A1
Type the formula in Cell-B1 =LOWER(A1) and press Enter

See Screenshot | Convert XL Cell text to Lower case:

Convert your Excel Cell text to Upper case with formula

2008-07-18T11:25:00.014+05:30

You can change the case of your Excel Cell text to Upper case with formula itself.

See Example:

Type any text in Cell-A1
Type the formula in Cell-B1 =UPPER(A1) and press Enter

See Screenshot | Convert XL Cell text to Upper case: