VBA: Application Open FileName List all Excel File types

When you are showing a open dialog box to the user to select an excel file, by default the filter would be *.* which will show all files including excel files. and .xls and .xlsx will show only 97-2003 and 2007 excel files and failed to show .xlsb and .xlsm files. The below code (.xls*) will show all types of excel files.

VBA: Array - Return common values from two arrays

The below function will return the common values that are exist in two different arrays. This can be achieved by nested-looping concept, but this is an effective way :).IsInArray is an another function. Dont forget to include it.
aa

VBA: Distinct Array items - Remove duplicates

If you have an array with repeated items and want distinct items, then copy the below source code. IsInArray is an another function. Dont forget to include it.

VBA: IsInArray - Find a value in array items

The below script will be used to get to know whether a particular item exists in a array or not. Instead of looping this would be faster.

VBA: Cell Data Validation list to an array

The below function will be used to get list of Cell data validation and store into an array variable. This code is needed when checking the cell value as whether it is picked from the validation drop-down or the validation rule overridden by copy-paste the cell value. (Excel has this flaw, whatever the data validation you define, simply that can be overriden by clipboard data Ctrl+C Ctrl+P)

VBA Excel : Sheet Named range into Array

The below code will be used to access the list of named-range values and pass to an array variable.

VBA - Extract notes section from PPT

The below code will be used to extract the notes section from Powerpoint slides to excel sheets range. We have seen a similar example of copy-pasting data from excel to powerpoint. This is a reverse operation of extracting powerpoint notes to excel.

Note: Please include Reference Libraries Microsoft PowerPoint XX.0 Object Library & Microsoft Scripting Runtime

VBA - Unzip files using VBA

The below code will Unzip a *.zip file to a specific folder. We have already seen couple of examples like 'Accessing system special folders' and 'Creating shortcut of an system application', This code also in the same series of Unzipping the files. Quite useful if you are designing a complete protect and want to install in client machine.

VBA - Protect Sheet from User / Allow macro

Generally, if you want your vba code to run on a protected sheet, you wrap your code in unprotect/protect statements in every procedure or function. You also have to remember to protect the sheet in case it is unprotected. Here is the attached file that clearly explains how you can avoid this.

The following code in the Workbook_Open procedure/event protects all the sheets from the user but allows the macro code to work on the sheets.

VBA - Export to Powerpoint

The below code will be used to copy paste a excel range (named range) to a powerpoint slide, this code has an ability to add new slides (at the End/Beginning) if you run a loop for 'N' number of slides.

Customization Options:
> Add new slide at the end / beginning
> Copy paste the excel chart as Image / Html
> Enable / Disable Chart link to the excel
> Zoom / Align the Powerpoint slides from Excel

VBA - Download files from sharepoint

The below VBA code will be used to download files from a sharepoint folder at one-click. I have got this code through surfing, this is quite usefull for the people who involves their day to day job with sharepoint files.

You can now do the same kind to upload files in sharepoint, will try my best to post a code for Uploading files to sharepoint.

Append string to a existing notepad (.txt) file in sharepoint

The below code will be used to append string to an existing text file in a sharepoint folder, you can do the same function with an existing folder in your computer/ shared folder.

e.g. When you want to create an user's log to a workbook in a separate file, you can use this code.

VBA : List Special Folders

The following code displays path of all special folders in Windows on the worksheet. Copy the following code in a general module and run(F5).

e.g.

Special Folder	Path
Desktop	C:\Users\Public\Desktop
Start Menu	C:\ProgramData\Microsoft\Windows\Start Menu
Programs	C:\ProgramData\Microsoft\Windows\Start Menu\Programs
Startup	C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup
Desktop	C:\Users\Kannan\Desktop
Roaming	C:\Users\Kannan\AppData\Roaming

VBA Excel: Create shortcut of an Application

The below example create an Shortcut of Notepad.exe in the desktop. This will be useful when you are doing a end-to-end product package, create shortcut of your application in the desktop.

VBA Excel: Table Intelligent Sort

This is really an interesting post, will be quit useful in dashboards, scorecards and wherever high UI is needed. Like many statistical applications, data of multicolumn, if user want to sort by many categories the below code will be the best suit.

Tips: If you link the data range with the chart data, you can see real magic. Sooner i will post some free codes under 'CHART' category for chart animations and many :)



Worksheet Snapshot:

VBA Excel: Track Cell Change

We have seen  Cell Change Tracking example to track when the change has been done and who did the change, the below example is bit advanced. It will show which cell has been modified by whom and what was the previous value before the modification.

VBA Excel: Userform > Set Parent

The below code will be used to set the UserForm UF as a child of (1) the Application, (2) the Excel ActiveWindow, or (3) no parent. Returns TRUE if successful

VBA Excel: Userform > Opaque / Transparent

The below code will be used to set the Opaque / Transparency Level of the userform. Usually the VBA Userform doesn't have this feature, but through API functions we can do this.

See Declaration Library post to understand and get the API function declarations.

VBA Excel: Userform > Resize Property

The below code will be used to make the Userform resizable . Usually the VBA Userform doesn't have this feature, but through API functions we can do this.

See Declaration Library post to understand and get the API function declarations.

VBA Excel: Userform > Title bar

The below code will be used to customize the Title Bar in the userform. Usually the VBA Userform doesn't have this feature, but through API functions we can play with the Title Bar in the userform.

See Declaration Library post to understand and get the API function declarations.

VBA Excel: Userform > Close Button

The below code will be used to customize the Close button in the userform. Usually the VBA Userform doesn't have this feature, but through API functions we can play with the Close button in the userform.

See Declaration Library post to understand and get the API function declarations.

VBA Excel: Userform > Minimize Button

The below code will be used to customize the Minimize button in the userform. Usually the VBA Userform doesn't have this feature, but through API functions we can enable the minimize button in the userform.

See Declaration Library post to understand and get the API function declarations.

VBA Excel: Userform > Maximize Button

The below code will be used to customize the Maximize button in the userform. Usually the VBA Userform doesn't have this feature, but through API functions we can enable the maximize button in the userform.

See Declaration Library post to understand and get the API function declarations.

VBA Excel: Userform Customization - Declaration Library

In the upcoming tutorials I am going to post 'free source codes' to Customize the VBA Userform, To do the below customization we need to do the declaration. The below code is common for all the below operations.

* Enabling/disabling Close [X] button
* Enabling/disabling Minimize [-]& Maximize button
* Show/hide Userform 'Title' bar
* Set Userform Opaque / Transparency
* Enable/Disable Userform's Resizable property




This is the sample exercise form snapshot and the code entered in the Userform-Module.

Userform Code: