All posts by AnalystCave

Tom. Excel / VBA / C# enthusiast and hobbist. Collecting and sharing my knowledge and experience with beginner/advanced analysts and VBA developers. My posts are written with one thing in mind: teaching analysts how to do things properly.
vba ftp

VBA Download / Upload File using VBA FTP

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Today we will learn how to download and upload files using a VBA FTP script. FTP is a great way to keep your Excel files connected to your file servers, back up your files and worksheets automatically or simply download/upload document without any additional manual hassle.

FTP is the abbreviation for File Transfer Protocol and is a standard network protocol used to transfer computer files between a client and server on a computer network.

For more read WIKI here.

VBA FTP Download Files

Let us start with learning how to Download Files from FTP using VBA FTP. The below code defines the procedure FtpDownload which does just that:

Syntax

strRemoteFile
A string path to the file on the remote FTP drive which you want to download e.g. “//home/user/text file.txt”

strLocalFile
A string path to the file on the local drive which you want to save the remote file to e.g. “C:\text file.txt”

strHost
A string with the FTP server name e.g. “192.168.0.100” or “myserver.example.com”.

lngPort
A number specifying the FTP port. 21 by default.

strUser
A string with the FTP user name.

strPass
A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

VBA FTP Upload Files

Now let us move to learning how to Upload Files from your local drive to FTP using VBA FTP. The below code defines the procedure FtpUpload which does just that:

Syntax

strLocalFile
A string path to the file on the local drive which you want to upload e.g. “C:\text file.txt”
strRemoteFile
A string path with the name of the upload file on the remote drive to e.g. “//home/user/text file.txt”

strHost
A string with the FTP server name e.g. “192.168.0.100” or “myserver.example.com”.

lngPort
A number specifying the FTP port. 21 by default.

strUser
A string with the FTP user name.

strPass
A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

Summary and alternatives

Downloading and Uploading files to an FTP server is a very useful task. Although there are several things to remember…

Do not keep your password directly in your Workbook. Your password should be stored in a secure way and there is always a chance you might share your Excel Workbook without deleting the embedded password. A good tip is to save the password in a separate text file on your local drive to which no one else has access. See Reading Files in VBA to do this.

Do you use FTP for file versioning? Use SVN or a dedicated solution instead! Although saving and versioning files to FTP is one way to go don’t rely on custom basic solution to long known problems. Setup SVN on your server of use a dedicated service like OneDrive, DropBox of Google Drive.

Do you want to download files from web servers / pages not from FTP? See this post on Downloading Files using VBA.

excel character map

Excel Character Codes and using CHAR / CODE functions

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Often we look to certain symbols in Excel that are hard to locate on the keyboard. Or the opposite – we look to find the Excel character codes for certain characters. In such cases we must usually revert to using the CHAR Excel function or the CODE Excel function. Let us explore these functions and how to use them both in Excel and VBA today.

Excel Character Codes

First let us start with exploring the Excel Character codes.

What is a Character Code? Each character is encoded as a number within a specific character map e.g. in ANSI or UTF8 standard

The table below represent the full default encoding in Excel:

On the right side is the Excel character (CHAR) and on the left you have the numeric representation (CODE) of the character.

Converting Characters to Codes (Excel CODE function)

The Excel CODE function allows you to easily obtain the numeric code for the provided character (1 character string).

Syntax

The syntax for the CODE function in VBA is:

CODE( string )

Parameters

string
A single character string.

Example usage

Excel

Below example of using the Excel CODE function:
excel code function

VBA

Below VBA equivalent of the Excel CODE Funtion – the Asc function

Converting Codes to Characters (Excel CHAR function)

The Excel CHAR function allows you to easily obtain the character represented by the numeric code.

Syntax

The syntax for the CHAR function in Excel is:

CHAR( number)

Parameters

number
A number from 0-255.

Example usage

Excel

Below example of using the Excel CHAR function:
excel char function

VBA

Below VBA equivalent of the Excel CHAR Funtion – the Chr function:

vba vlookup

VBA VLOOKUP – Using VLOOKUP in VBA

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The VLOOKUP function is probably one of the most dreaded functions by beginner Excel users and also one of the most appreciated by the more advanced. VLOOKUP is used to lookup a row in a certain table, based on a value and return a corresponding value in a certain column with that row.

VLOOKUP is easily used in Excel. Today, however, we will explore how to do lookup operations in VBA (VBA VLOOKUP) or how to VLOOKUP in VBA if you prefer.

VBA VLOOKUP WorksheetFunction

First let us explore the simple approach of using the VBA VLOOKUP WorksheetFunction.

For this example table:
vba vlookup table
A simple VLOOKUP operation for say to lookup Roberts age might look like this:
VLOOKUP in Visual Basic equivalent
What about the same in Visual Basic for Applications (VBA)? The below is the equivalent to the VLOOKUP operation in Excel. We can use the WorksheetFunction object to run a VLOOKUP operation as such:

The result:
vba vlookup result

WorksheetFunction Vlookup function

Syntax

The syntax for the Vlookup function in VBA is:

Parameters

Lookup_value
The lookup value – the value which is searched for within the first column of the provided Table_array parameter

Table_array
A VBA Range variable. A table of at least 2 columns or more. The first column is the lookup column.

Col_index_num
The number of the column in the Table_array table VBA Range from which the matching value must be returned.

Range_lookup
An optional parameter. A logical parameter (True or False) that specifies whether you want the VLookup method to find an exact match or an approximate match. True – approximate match, False – exact match.

VBA VLOOKUP Dictionary

Another approach is to use the VBA Dictionary Object. For the same table as above we can use the following code:

What happens above? First we loop through all the rows and create a VBA Dictionary object with a Key-Value pair of all rows. The Dictionary contains only the lookup and matching columns. Next we lookup our desired Key and return the matching Value.

The result:
vba vlookup result
Right. So if both ways provide the same result what is the benefit of building a Dictionary upfront? Well, imagine wanting to lookup multiple values within your VBA code. In the first approach you would have to reintroduce the entire arguments of the Vlookup function. In the second you can run additional simple calls:

The Dictionary approach will in this case prove MUCH FASTER. This is because the Dictionary is built once and then just reused to lookup a certain key as opposed to doing the entire lookup operation on the entire table as a Vlookup operation would do.

If you want to learn more on the most effective way to lookup values in Excel instead, read my article on VLOOKUP vs INDEX MATCH vs SQL vs VBA.

excel vba camera tool

Excel Camera Tool – create an Image snapshot in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Did you know you can use Excel to Image snapshots of your Excel spreadsheets ? Sure you can. What is more you can use this feature directly from VBA to achieve some impressive feats. Today we will learn how to use the Excel Camera Tool!

Excel Camera Tool

excel camera tool
Using the Excel Camera Tool
Let us start with exploring how to use the built-in Excel Camera Tool. The Excel Camera Tool is a button that let’s you create an image snapshot of any region of your Excel spreadsheet.

To use the Excel Camera Tool all you need to do is:

  1. Select an Excel Range

  2. Click on the Camera Tool Icon

  3. Click on any place in your Excel Worksheet

Add Excel Camera Tool Icon

Adding the Excel Camera Tool to your Quick Access Toolbar is easy. Just follow the steps below:

Select the Customize Quick Access Toolbar icon

Click on the arrow show the Quick Access Toolbar menu.
add excel camera tool
Next click More commands.

Browser Commands not in the toolbar

From the Choose commands from: menu select Commands not in the toolbar.
add excel camera tool
Now you should see an item called Camera in the list of Commands.

Add the Camera Tool

Now select Camera and hit the Add button to add the Camera tool to your Quick Access Toolbar.
add excel camera tool
Now you should see the Excel Camera Tool Icon in your Quick Access Toolbar:
excel camera tool.

Use Excel Camera Tool in VBA

The Camera Tool is very useful in your everyday work when you need to copy part of your Workbook as an Image i.e. send an image snapshot of an Excel Worksheet or Chart. Fortunately there is also an easy way to use it in VBA:

An example of how the macro works below:
excel camera vba
Nice huh? Well this is the beginning of things you can do. There are lot of cool ideas you can use this feature to:

  • Send image snapshot of Excel Worksheet via Email
  • Save Excel Workbook as static image – replace each Worksheet with a static Image to protect your formulas and calculations
excel count formula

Excel Count Cells with Text and Formula – Excel Stats

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

In Excel we often like to Count Things. Sometimes those things are Cells with Text, Formulas or Formatting. Other times we want to Count Blank or Non-Blank Cells… and so on. Today I will teach you All About Counting Things in Excel. Excel Count Functions are an obvious option to go with, but there are also wonders you can do with just 1 line of VBA Code.

If want to see a Cool Summary of all your Excel Workbook Statistics scroll down to the last section.

Excel Count Functions

First let us start with Basic Excel Count Functions:

Excel COUNT function

Counts Number of Cells with Numbers

COUNT Function

Excel COUNTA function

Counts Number of Non-Blank Cells

COUNTA Function

Excel COUNTBLANK function

Counts Number of Blank Cells

COUNTBLANK Function

Excel COUNTIF function

Counts Number of Cells that Fulfill an If Condition

COUNTIF Function

Excel DCOUNT function

Counts Number of Cells that Fulfill MULTIPLE Specified Conditions

DCOUNT Excel Function

VBA Count Functions

VBA Count Cells in Range

To simply count the number of VBA Cells in an Excel Range use the Count Range property.

To learn more about the VBA Range read my VBA Range Tutorial

VBA Count Cells with Numbers

To Count Cells with Numbers (equivalent to Excel COUNT Function) use the WorksheetFunctions.Count function:

VBA Count Non-Blank Cells

To Count Non-Blank Cells (equivalent to Excel COUNTA Function) use the WorksheetFunctions.CountA function:

VBA Count Blank Cells

To Count Blank Cells (equivalent to Excel COUNTBLANK Function) use the WorksheetFunctions.CountBlank function or the SpecialCells Range property:
CountA:

SpecialCells:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count If Cells

To Count If Cells (equivalent to Excel COUNTIF Function) on certain conditions use the WorksheetFunctions.CountIf function:

VBA Count Cells with Formulas

To Count Cells with Formulas use the SpecialCells Range property:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count Cells with Constants (Non-Formulas & Non-Blank)

To Count Cells with Constants use the SpecialCells Range property:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count Visible / Invisible Cells

To Count Cells with Constants use the SpecialCells Range property:
Count Visible Cells:

Count Invisible Cells:

VBA Count Other Types of Cells

The SpecialCells Range property allows you to find a subset of certain types of cells. Here are all the available types:

Constant Description
xlCellTypeAllFormatConditions Any type of Cells
xlCellTypeAllValidation Cells with Validation Criteria
xlCellTypeBlanks Blank Cells
xlCellTypeComments Cells with Comments
xlCellTypeConstants Cells with Constants (Non-Formula & Non-Blank)
xlCellTypeFormulas Cells with Formulas (beginning with a =)
xlCellTypeLastCell Get Last Cell in the UsedRange
xlCellTypeSameFormatConditions Cells with Common Format Conditions
xlCellTypeSameValidation Cells with Common Validation Criteria
xlCellTypeVisible All Visible Cells

Excel Function Usage Statistics

I saved best for last. What if you wanted to do a statistic on the Excel Functions used in your Excel Workbook? Excel doesn’t provide you with a neat statistics windows like Word for words and sentences. Fortunately, I developed a neat VBA Macro that Counts every Function used in all Excel Formulas and presents a neat report as a result.

The Code

The Code below will generate Excel Function Usage Statistics to a new Worksheet.

How to use it? Simply run the CreateStats Sub procedure:

Or go to the DEVELOPER Ribbon Tab, select Macros and Run the CreateStats procedure on the list.

An Example

Example Excel WorkbookSay we have an example Excel Workbook with various Formulas. The CreateStats procedure will produce the following statistics:

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

An Excel UserForm

Want a neat UserForm report like the one below?

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

Instead of using the CreateStats function you can alternatively use my CreateStatsUserForm function (using the StatsForm – see Download section below), to instead admire a neat statistics UserForm without having to manage any additional ad-hoc Worksheets.

Download Excel Stats

You can download the Excel Functions Usage Statistics Modules below: