Sending EMail With VBA

It is not difficult to add the ability to send email from your application. If all you want to do is send the workbook, with just a subject but no content, you can use ThisWorkbook.SendMail. However, if you want to include text in the body of the message or include additional files as attachments, you need some VBA code. The page describes a function called SendEmail that wraps up the details in a nice, VBA-friendly function.

Free Excel Tool - XLLoop

About XLLoop

XLLoop is an open source framework for implementing Excel user-defined functions (UDFs) on a centralised server (a function server).

Why is this useful?

Functions can be written in any language (e.g. Java, Javascript, Ruby, Python, Erlang etc..)

Functions can be added quickly and dynamically without users having to restart Excel.

Get A Specific Element In The Text String With VBA

Often, data is stored in a single worksheet cell consisting of several values, separated by some delimiter. For example, a cell may contain Abby,Barb,Carrie,Dawn, a list of names separated by commas. How should we get a specific element in the text string?

How to Create A Linkable Hot Image

You might have noticed that some Web sites feature an hot image map to help navigate the site. An image map is a single image, but it’s sensitive to the location at which you click it on hot area. This tip demonstrates how to create a linkable hot image.

Free Excel Tool - VB Template Engine

MiniTemplator is a mini VB Template Engine. MiniTemplator is a compact template engine for HTML files. It’s available in Java, PHP and Visual Basic (VB/VBA). It uses a simple syntax for template variables and blocks, supports nested blocks and is implemented as a single code module. [download id=”2″] The project comes from [http://sourceforge.net/projects/minitemplator/].

How To Highlight Entire Current Row And Column In Excel

How can I use VBA or conditional formatting to highlight the entire current row and column of the current cell?

In your sheets worksheets selection change event, you can use something like this:

How To Get The Data Type Of A Cell In Excel

In some situations you may need to determine the type of data in a cell. Excel provides a number of built-in functions that can help. These include ISTEXT, ISLOGICAL, and ISERROR. In addition, VBA includes functions such as IsEmpty, IsDate, and IsNumeric.

How to Determine The Type Of A Particular Drive Using VBA

This article contains a VBA function that uses the Windows GetDriveType API function to determine the type of a particular drive.

Private Declare Function GetDriveType Lib "kernel32" _
  Alias "GetDriveTypeA" (ByVal nDrive As String) As Long

Function DriveType(DriveLetter As String) As String
'  Returns a string that describes the type of drive of DriveLetter
   DriveLetter = Left(DriveLetter, 1) & ":\"
   Select Case GetDriveType(DriveLetter)
      Case 0: DriveType = "Unknown"
      Case 1: DriveType = "Non-existent"
      Case 2: DriveType = "Removable drive"
      Case 3: DriveType = "Fixed drive"
      Case 4: DriveType = "Network drive"
      Case 5: DriveType = "CD-ROM drive"
      Case 6: DriveType = "RAM disk"
      Case Else: DriveType = "Unknown drive type"
  End Select
End Function

How To Get The Computer Name Or Logged-in User Name Using VBA

This article uses two Windows API functions to return the name of the computer, and the name of the user who is currently logged in. These functions can be used in a worksheet formula, or called from a VBA procedure.

Making A Single Worksheet Function Act Like Multiple Functions

This article describes a technique that may be helpful in some situations – making a single worksheet function act like multiple functions. For example, the VBA listing below is for a custom function called StatFunction. It takes two arguments: the range (rng), and the operation (op). Depending on the value of op, the function will return any of the following: AVERAGE, COUNT, MAX, MEDIAN, MIN, MODE, STDEV, SUM, or VAR.

Getting Coefficients Of Chart Trendline

When you add a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This article describes how to create formulas that generate the trendline coefficients. You can then use these formulas to calculate predicted y values for give values of x.

A Simple VBA Function For Testing A FileName Is Valid

Sometimes we save a file, but it returns “Illegal Windows filename characters” error.

Maybe we need a robust and simple way to remove illegal path and file characters from a simple string.

How check if given string is legal (allowed) file name under Windows?

How To Create DSN For ODBC Using VBA

This article describes a VBA function that will Create DSN For ODBC. The VBA function use API function SQLConfigDataSource to do this. Then you can create DSN for ODBC in your code.

The VBA code is bellow.

How To Create A Splash Screen For An Excel Workbook

This article describes how to create a splash screen for a Excel workbook. A splash screen appears when the workbook is opened and disappears automatically after a specific time has elapsed.

Setting it up

Follow these instructions to create a splash screen for your project.

Track Who Opens A Workbook And At What Time

This article describes how to create a usage log that keeps track who opens a workbook, and at what time.

If a workbook’s ThisWorkbook code module contains a procedure named Workbook_Open, that procedure is executed whenever the workbook is opened. Add the following procedure to the ThisWorkbook code module.

Free Excel Tool - JSON Parser For VBA

vba-json is a JSON parser for VBA. vba-json’s feature is as follows : parse JSON and create Dictionary/Collection serialize Dictionary/Collection as JSON Download: [download id=”1″] The project is host on http://code.google.com/p/vba-json/

Select All Unlocked Cells Using VBA in Excel

As you probably know, when a worksheet is protected, only the unlocked cells can be changed. You may want to apply different formatting to the unlocked cells, or simply ensure that the correct cells are indeed unlocked.

Oddly, there is no direct way to quickly identify which cells are locked or unlocked. When the sheet is protected, you can use the Tab key to move among the unlocked cells, but it you would like to select them all you’ll need a macro.

How to Get Temp File Name Using VBA

This page describes a VBA function that will return a Unique Temporary File Name. The VBA function use API to do this. You can also specify prefix or file extensions to include in the Temporary File Name.

VBA Function To Count Word Frequencies

This article describes a VBA function that analyzes a list of text, and counts the frequency of each word. The function works with any text. It assumes that the data begins in cell A1 of the active sheet, and the first blank cell in the column signals the end of the text.

Two Functions For Testing A Specified Word Is Contained In A Text String

Here’s a VBA function that might be useful in some situations.

The IsWordInString functions returns True if a specified word is contained in a text string.

You might think that this function is just a variation on Excel’s FIND function or VBA’s Instr function. There’s a subtle difference. The IsWordInString function looks for a complete word — not text that might be part of a different word.

Using VBA to Send a text file to a server via FTP in Excel

I have an Excel worksheet where the user enters certain data, which I want to store in a text file and upload to a server using FTP. Does anyone know of a solution for this problem?

The following VBA code is one solution for this problem.

Setting a reference to the VBA Extensibility library by code

When your coding the VBE you have to set a reference to the VBA Extensibility library. You can add the reference programmatically with code like:

Using COUNTIF function to compare two columns in Excel

I have an excel document with columns A and B. Row A has 2595 values. Row B has 411 values.

I’m trying to figure out how I can compare these two columns by marking column C with the number of times we find a value(s) from column A.

Use keyboard shortcut to Zoom in or out on Excel Workbook

For most purposes, Excel allows you to issue commands and perform functions by using either the mouse or the keyboard. Unfortunately, Excel does not provide “equal access” for all commands. For instance, it is relatively easy to zoom in or out using the mouse, but there is no easy way to do it using the keyboard (other than using the keyboard to traverse the menus and select a zoom setting).

Choose the best type of chart in Excel

One factor to consider as you want to choose the best type of chart in Excel is the relative strengths and weaknesses of each type of graph. One could, of course, write an entire book on this subject. However, May you find it useful to examine the strengths and weaknesses described in the paragraphs that follow to choose a graphic.

How to set default zoom size for specific Workbooks when opened

If you work with workbooks first worked on by your colleagues, you may be frustrated by the zoom factor applied to those workbooks by those others. For instance, if your colleague (Wanda) has a huge monitor, it wouldn’t be uncommon for her to reduce the zoom factor on Excel to 75% or even 60%. The purpose, of course, is so she isn’t overpowered by things that look very large at the full zoom factor.

VBA Get Object's Parent Workbook

How can I get the parent Workbook of an Object? Here give the source code to solve the problem.The VBA function name is GetObjectParentWorkbook. Function GetObjectParentWorkbook(aObject As Object) As Workbook Dim o As Object On Error GoTo ErrorHandle If aObject Is Nothing Then GoTo ErrorExit Set o = aObject.Parent If TypeOf aObject Is Workbook Then Set GetObjectParentWorkbook = aObject GoTo ErrorExit End If Do Until (TypeOf o Is Workbook) Or

VBA Get Object's Parent Sheet

Where is the parent sheet of an object in VBA? The useful VBA function GetObjectParentSheet will help you to do this. Function GetObjectParentSheet(aObject As Object) As Object Dim op As Object On Error Resume Next If aObject Is Nothing Then GoTo ErrorExit Set op = aObject.Parent If op Is Nothing Then GoTo ErrorExit If TypeOf op Is Workbook Then Set GetObjectParentSheet = aObject GoTo ErrorExit End If Do Until (TypeOf

Get Range from address string

Sometimes we want to get Range from address string in VBA, here give a VBA function RangeFromAddress, the code is following.

Note: The RangeFromAddress’ reference:

VBA Get Object’s Parent Sheet

VBA Get Object’s Parent Workbook