Monday, 6 October 2014

Changing Excel Icon & Creating Workbook Shortcut With A Custom Icon


Introduction



A common question when developing custom Excel applications is: “How to change the default icon of the main Excel window?” Another common question is: “How to change the default icon of the custom application, so as to not look like an Excel workbook?” Both questions address the same need; to customize the user interface. If you have spent countless of hours in workbook designing and code development, you always want to add a personal touch on the user interface of your application.

In this post we will try to answer to both questions. For the first one, the answer is quite straightforward and involves two Windows API functions, the ExtractIcon and the SendMessage. The ExtractIcon function retrieves a handle to an icon from the specified executable file, DLL, or icon file. Here is used to retrieve an icon handle from a user-defined icon file (no .DLL or .EXE file). On the other hand, the SendMessage function is used to send a specific message to the main Excel window forcing it to change its icon.

For the second question, unfortunately, there is no straight answer; probably there is no answer at all! Neither with Windows APIs, nor with any other programming technique can you change the icon of a single workbook (without changing the icon of all workbooks). However, there is a workaround. If your workbook is on a specific location at your hard disc, you can create a shortcut at the computer’s desktop, which will have a custom icon. In this way you give to the application user the illusion of custom application icon. So, in the next section we will see how to achieve this programmatically via VBA – actually the Windows Script Host object does the “dirty work”.



VBA code



As the macros name imply the ChangeExelIcon changes the default Excel icon to a user-specified icon, while the RestoreExcelIcon does the opposite.

Option Explicit

'-------------------------------------------------------------------------------------
'This module contains two macros for changing the Excel Icon of the current workbook.
'The ChangeExelIcon macro changes the default Excel icon to an icon that is specified
'by the user, whereas the RestoreExcelIcon does the opposite.

'Written By:    Christos Samaras
'Date:          05/10/2014
'E-mail:        xristos.samaras@gmail.com
'Site:          http://www.myengineeringworld.net
'-------------------------------------------------------------------------------------

'Declaring the necessary API functions and constants.
#If VBA7 And Win64 Then
    
    'For 64 bit Excel.
    Private Declare PtrSafe Function ExtractIconA Lib "shell32.dll" _
                                    (ByVal hInst As LongPtr, _
                                    ByVal lpszExeFileName As String, _
                                    ByVal nIconIndex As Long) As LongPtr
                                    
    Private Declare PtrSafe Function SendMessageA Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                    ByVal wMsg As Long, _
                                    ByVal wParam As LongPtr, _
                                    ByVal lParam As LongPtr) As LongPtr

    Private Const ICON_SMALL    As LongPtr = 0&
    Private Const ICON_BIG      As LongPtr = 1&
    Dim hwndIcon                As LongPtr

#Else

    'For 32 bit Excel.
    Private Declare Function ExtractIconA Lib "shell32.dll" _
                            (ByVal hInst As Long, _
                            ByVal lpszExeFileName As String, _
                            ByVal nIconIndex As Long) As Long
                            
    Private Declare Function SendMessageA Lib "user32" _
                            (ByVal hwnd As Long, _
                            ByVal wMsg As Long, _
                            ByVal wParam As Long, _
                            ByVal lParam As Long) As Long
                            
    Private Const ICON_SMALL    As Long = 0&
    Private Const ICON_BIG      As Long = 1&
    Dim hwndIcon                As Long

#End If

Private Const WM_SETICON    As Long = &H80
    
Sub ChangeExelIcon()
 
    'Get the icon handle.
    'You can use the full path of the icon file, i.e.:
    'hwndIcon = ExtractIconA(0, "C:\Users\Christos\Desktop\My Icon.ico", 0)
    'Or the icon path relative to the current workbook, i.e.:
    hwndIcon = ExtractIconA(0, ThisWorkbook.Path & "\My Icon.ico", 0)
     
    'Check if the icon handle is valid.
    If hwndIcon <> 0 Then
    
        'Change the icon.
        'For a big icon (32 x 32 pixels), use this line:
        'SendMessageA Application.HWnd, WM_SETICON, ICON_BIG, hwndIcon
        'For a small one (16 x 16 pixels), use the next line (typical case):
        SendMessageA Application.hwnd, WM_SETICON, ICON_SMALL, hwndIcon
        
        'Inform the user.
        MsgBox "Excel icon was changed successfully!", vbInformation, "Done"
    
    End If
    
End Sub
 
Sub RestoreExcelIcon()

    'Get the icon handle of Excel application.
    hwndIcon = ExtractIconA(0, Application.Path & "\Excel.exe", 0)
    
    'Check if the icon handle is valid.
    If hwndIcon <> 0 Then
    
        'Restore the original Excel icon.
        SendMessageA Application.hwnd, WM_SETICON, ICON_SMALL, hwndIcon

        'Inform the user.
        MsgBox "Excel icon was restored successfully!", vbInformation, "Done"
    
    End If
    
End Sub

Similarly, the CreateWorkbookShortcut macro creates a workbook shortcut at the computer's Desktop, whereas the DeleteWorkbookShortcut deletes that shortcut.

Option Explicit

'-------------------------------------------------------------------------------------
'This module contains a macro - CreateWorkbookShortcut - for creating a shortcut of
'the current workbook at the computer's Desktop; the shortcut has a custom icon.
'The other macro - DeleteWorkbookShortcut - deletes that shortcut from the Desktop.

'Written By:    Christos Samaras
'Date:          05/10/2014
'E-mail:        xristos.samaras@gmail.com
'Site:          http://www.myengineeringworld.net
'-------------------------------------------------------------------------------------

Sub CreateWorkbookShortcut()

    'Declaring the necessary variables.
    Dim WSHShell    As Object
    Dim Shortcut    As Object
    
    On Error GoTo ErrorHandler:

    'Create the Windows Script Host object.
    Set WSHShell = CreateObject("WScript.Shell")
    
    'Create the shortcut object.
    Set Shortcut = WSHShell.CreateShortcut(MyDesktop & "\" & WbookNameNoExtension & ".lnk")
    
    'Adjust the shortcut.
    With Shortcut
        .TargetPath = ThisWorkbook.FullName
        .IconLocation = ThisWorkbook.Path & "\My Icon.ico"
        .Save
    End With
    
    'Inform the user about the shortcut creation.
    MsgBox "The """ & WbookNameNoExtension & """ file has now a shortcut at your Desktop." & vbNewLine & _
            "Note that the shortcut has a custom icon!", vbInformation, "Done"
            
ErrorHandler:
    'Check for errors.
    If Err.Number <> 0 Then
        MsgBox Err.Description & "!", vbCritical, "Error Number " & Err.Number
    End If
    
    'Release the objects.
    Set Shortcut = Nothing
    Set WSHShell = Nothing
    
End Sub

Sub DeleteWorkbookShortcut()
    
    'If the shortcut file exists, then delete it.
    If FileExists(MyDesktop & "\" & WbookNameNoExtension & ".lnk") = True Then
        Kill MyDesktop & "\" & WbookNameNoExtension & ".lnk"
        MsgBox "The shortcut has been successfully removed from your Desktop!", vbInformation, "Done"
    Else
        MsgBox "Shortcut couldn't be found at your Desktop!", vbCritical, "Failed"
    End If

End Sub

Function MyDesktop() As String

    'Returns the Desktop location.
    MyDesktop = Environ("USERPROFILE") & "\Desktop"

End Function

Function WbookNameNoExtension() As String
    
    'Returns the workbook name without its extension.
    WbookNameNoExtension = Left(ActiveWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))

End Function

Function FileExists(strFilePath As String) As Boolean

    'Checks if a file exists.
    On Error Resume Next
    If Not Dir(strFilePath, vbDirectory) = vbNullString Then FileExists = True
    On Error GoTo 0
   
End Function

Note that on MyDesktop function we have used the Windows environment variables, an easy way to retrieve main Windows paths via VBA. Please check this post for a complete list of all the available environment variables.



Downloads



Download

The zip file contains an Excel workbook along with a guitar icon in order to test the above macros. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

Wednesday, 27 August 2014

WPF VS Windows Forms


Introduction



I have to admit that in the last few months my interest about programming “changed”. This doesn’t imply that I was bored learning new programming techniques; actually the opposite was true. The “change” means a shift towards a new programming language. As some of you might suspect from the introduction of CodeEval post series, the new programming language that I am talking about is C#.

I can “hear” you asking: Does this decision implies no more VBA-related posts? Well, the answer is NO! I am using Office every day and I have developed several custom functions/subs (some of which have not been published in this blog), so I see no reason to leave behind VBA development – at least with respect to Office applications. However, it is quite possible that the “programming side” of this blog will be enriched with some posts about C#.

Sorry for this “irrelevant” introduction, but I think I had to explain to you why a post like this exists in this blog. So, in this post I have summarized the key differences between Windows Presentation Foundation (WPF) – which provides developers with a unified programming model for building rich Windows smart client user experiences that incorporate UI, media, and documents – and the “old/classic” Windows Forms.



Differences



1. Windows Forms are built on User32 (older: User) and GDI+ (older: GDI) APIs, whereas WPFs are built on DirectX API:
  • DirectX can be used for games (motion) whereas User32 and GDI+ not. Why? Because User32 and GDI+ renders via CPU, whereas DirectX renders via GPU (video card).
  • 3D graphics, motion, transparency of windows etc. are some of characteristics that can be built with WPF, contrary to the old gray-scale windows of Windows Forms.
  • WPF provides a more compelling user interface.
  • WPF and GPU hardware acceleration: on newer windows Vista/7/8 more can be done than on XP.
  • WPF checks the machine’s hardware and software drivers that will be utilized in order to achieve the optimal performance.
  • Old computer/less powerful system? No problem! WPF can use the CPU instead. Moreover, WPF has the ability to downgrade the motion, transparency etc. in order to run in older computers (check RenderCapability.Tier for example).

2. WPF uses XAML:
  • XAML is built on the top of WPF API.
  • XAML is an XML type of language and is familiar paradigm for graphic designers.
  • XAML tools: Expression Blend (from 2012, part of VS). It generates XAML and can open a VS project. Actually a project can be opened simultaneously on VS and Expression Blend.
  • On WPF both the graphic designer (front end) and the classic C#/VB.NET developer (back end) can work separately, but efficiently.
  • With the Expression Design you can create Assets, which then can be orchestrated (motion, etc.) in Expression Blend, and, finally, handle the various events in Visual Studio.

3. WPF provides new navigational project type:
  • Navigate back and forth, similar to a browser (difficult to be done in Windows Forms).

4. Layout of user interface elements:
  • Windows Form controls have issues regarding a) dynamic loading, b) localization (i.e. different versions for different languages) and c) their ability to be adjustable for people with disabilities (i.e. magnification of the button font).
  • WPF’s default layout is flow; WPF can reshape, resize and reposition controls relative to other controls (similar to HTML).
  • So, WPF provides a new way of thinking about control layout for Windows developers.

5. WPF can use both audio and video:
  • In Windows Forms the implementation of audio/video was somehow an afterthought, whereas on WPF complete implementation of audio and video is feasible.

6. Windows Forms have no real notion of animation:
  • In Windows Forms you could use C++ and GDI+ or DirectX directly, or user a timer control etc. The outcome is that with Windows Forms, animation is quite difficult and clunky.
  • Animations in WPF feature Storyboards, like time lines.

7. Themes and skins:
  • In Windows Forms, the colors and in general, the ability to create custom themes for your forms/buttons etc. is quite difficult.
  • With WPF you can scheme an application with the use of styles and templates.

8. Other differences:
  • Video resolution independence.
  • A better control and usage of text styles.
  • New application deployment options.
  • The ability to create documents.
  • Better printing.
  • From WPF you can create Silverlight applications.

9. Finally, WPF:
  • Requires a lot more time to master (it’s a large API).
  • Needs more computing resources than Windows Forms, so in old PCs there might be issues.
  • A professional graphic designer (for an aesthetically pleasant user interface) is probably required.
  • Users might not be ready to accept a different user interface (i.e. with different colors etc.).
  • There is less third party support (i.e. controls) than Win Forms.
  • Cannot run on Windows 98.


Conclusion



As a general conclusion, there is no tremendous difference in respect of functionality. In reality, the WPF delivers the same functionality with Windows Forms, but with more style…

Thursday, 31 July 2014

Insert Blocks In AutoCAD Using Excel & VBA


Introduction



In the comments section of the previous post about AutoCAD and Excel interaction, a blog reader (Tim) asked me if it is possible to insert blocks in AutoCAD from Excel. I gave him some insight and I think that it's time to present the complete solution, which involves AutoCAD’s InsertBlock method. The code presented today is not new; it was written actually some months ago (in April). Anyway, according to AutoCAD VBA help, the structure of InsertBlock method is the following:

RetVal = object.InsertBlock(InsertionPoint, BlockName, Xscale, Yscale, ZScale, RotationAngle)

RetVal: Block Reference object. The placed block as a Block Reference object.
Object: ModelSpace Collection, PaperSpace Collection, Block – the objects this method applies to.
InsertionPoint: Variant (three-element array of doubles); input-only. The 3D WCS coordinates specifying the location in the drawing to insert the block.
BlockName: String; input-only. The name of the AutoCAD drawing file or the name of the block to insert. If it is a file name, include the .dwg extension and any path information necessary for AutoCAD to find the file.
Xscale: Double; input-only (optional). The default value is 1 (must be >0).
Yscale: Double; input-only (optional). The default value is 1 (must be >0).
Zscale: Double; input-only (optional). The default value is 1 (must be >0).
RotationAngle: Double; input-only (optional). The rotation angle relative to the WCS X-axis, expressed in radians. The default value is 0.

The sample workbook that you will find in the Downloads section below requires two main and two optional parameters: the coordinates of the insertion point (in X, Y, Z) and the block name (or its full path, including the .dwg extension). Optional parameters are the scale factors (in X, Y and Z axes) as well as the rotation angle. Then, by clicking the “Insert Blocks" button the blocks are inserted either in the active drawing (if AutoCAD is already lunched), or in a newly created drawing.



VBA code



The code is actually a loop; most of the code is used to initialize the AutoCAD object, as well as the active/new drawing. Note that the InsertBlock method here is used with a small variation: the RotationAngle must be given in degrees. The conversion – degrees to radians – is being done internally.

Option Explicit

'A custom type that holds the scale factors of the block.
Private Type ScaleFactor
    X As Double
    Y As Double
    Z As Double
End Type 

Sub InsertBlocks()

    '--------------------------------------------------------------------------------------------------------------------------
    'Inserts blocks in AutoCAD using data - insertion point, block name/full path, scale factors, rotation angle - from Excel.
    'Note that the block name or the block path must already exists, otherwise nothing will be inserted.
    'The code uses late binding, so no reference to external AutoCAD (type) library is required.
    'It goes without saying that AutoCAD must be installed at your computer before running this code.
    
    'Written by:    Christos Samaras
    'Date:          21/04/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------------------------------------------------------------------
        
    'Declaring the necessary variables.
    Dim acadApp                 As Object
    Dim acadDoc                 As Object
    Dim acadBlock               As Object
    Dim LastRow                 As Long
    Dim i                       As Long
    Dim InsertionPoint(0 To 2)  As Double
    Dim BlockName               As String
    Dim BlockScale              As ScaleFactor
    Dim RotationAngle           As Double
    
    'Activate the coordinates sheet and find the last row.
    With Sheets("Coordinates")
        .Activate
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
        
    'Check if there are coordinates for at least one circle.
    If LastRow < 2 Then
        MsgBox "There are no coordinates for the insertion point!", vbCritical, "Insertion Point Error"
        Exit Sub
    End If
    
    'Check if AutoCAD application is open. If is not opened create a new instance and make it visible.
    On Error Resume Next
    Set acadApp = GetObject(, "AutoCAD.Application")
    If acadApp Is Nothing Then
        Set acadApp = CreateObject("AutoCAD.Application")
        acadApp.Visible = True
    End If
    
    'Check (again) if there is an AutoCAD object.
    If acadApp Is Nothing Then
        MsgBox "Sorry, it was impossible to start AutoCAD!", vbCritical, "AutoCAD Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'If there is no active drawing create a new one.
    On Error Resume Next
    Set acadDoc = acadApp.ActiveDocument
    If acadDoc Is Nothing Then
        Set acadDoc = acadApp.Documents.Add
    End If
    On Error GoTo 0

    'Check if the active space is paper space and change it to model space.
    If acadDoc.ActiveSpace = 0 Then '0 = acPaperSpace in early binding
        acadDoc.ActiveSpace = 1     '1 = acModelSpace in early binding
    End If
    
    On Error Resume Next
    'Loop through all the rows and add the corresponding blocks in AutoCAD.
    With Sheets("Coordinates")
        For i = 2 To LastRow
            'Set the block name.
            BlockName = .Range("D" & i).Value
            'If the block name is not empty, insert the block.
            If BlockName <> vbNullString Then
                'Set the insertion point.
                InsertionPoint(0) = .Range("A" & i).Value
                InsertionPoint(1) = .Range("B" & i).Value
                InsertionPoint(2) = .Range("C" & i).Value
                'Initialize the optional parameters.
                BlockScale.X = 1
                BlockScale.Y = 1
                BlockScale.Z = 1
                RotationAngle = 0
                'Set the optional parameters (if there are values on the corresponding ranges).
                If .Range("E" & i).Value <> vbNullString Then BlockScale.X = .Range("E" & i).Value
                If .Range("F" & i).Value <> vbNullString Then BlockScale.Y = .Range("F" & i).Value
                If .Range("G" & i).Value <> vbNullString Then BlockScale.Z = .Range("G" & i).Value
                If .Range("H" & i).Value <> vbNullString Then RotationAngle = .Range("H" & i).Value
                'Add the block using the sheet data (insertion point, block name, scale factors and rotation angle).
                'The 0.0174532925 is to convert degrees into radians.
                Set acadBlock = acadDoc.ModelSpace.InsertBlock(InsertionPoint, BlockName, _
                                BlockScale.X, BlockScale.Y, BlockScale.Z, RotationAngle * 0.0174532925)
            End If
        Next i
    End With
    
    'Zoom in to the drawing area.
    acadApp.ZoomExtents

    'Release the objects.
    Set acadBlock = Nothing
    Set acadDoc = Nothing
    Set acadApp = Nothing
    
    'Inform the user about the process.
    MsgBox "The blocks were successfully inserted in AutoCAD!", vbInformation, "Finished"

End Sub

One thing that should be highlighted is that when inserting a block from a .dwg file, the results might not be as expected. The reason is that by default, AutoCAD uses the coordinate (0, 0, 0) as the base point when inserting a drawing file. You can change the default coordinate by following the next 4 steps:
  1. Open the dwg file you want to use as a block. 
  2. Type INSBASE at the command line. 
  3. Specify with your mouse a new point that you want to use as a base point. 
  4. Save the file. 
That's all; AutoCAD will use the new base point the next time you insert the drawing.

Note that if you have AutoCAD 2010 or a newer version, you will have to download and install the VBA module, otherwise the code will probably fail. 
All links were copied from the Autodesk's website.



Downloads



Download

The zip file contains an Excel workbook along with 3 sample AutoCAD drawings that can be used as blocks. In these sample drawings the INSBASE command has been used to change the default base point (as described above). The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.



Read also



Counting Existing Blocks In AutoCAD (VBA Add-In)
Drawing Points In AutoCAD Using Excel & VBA
Drawing Circles In AutoCAD Using Excel & VBA
Add Text In AutoCAD Using Excel & VBA
Excel Range To AutoCAD Table Add-In

Thursday, 24 July 2014

CodeEval 1 - Predict The Number


About CodeEval post series



Starting from this post I would like to establish another series of posts which will be different than the typical engineering/Excel/VBA posts that are being published in this blog. The purpose of this series is to demonstrate possible solutions on various CodeEval programming challenges. Each solution has already been submitted and accepted as valid on CodeEval platform, so if you try to submit the presented solution as it is, you will probably get a “not unique solution” result. The solutions will be presented in C# language, but the logic/algorithm behind it is similar despite the language you might use.



What is CodeEval?



CodeEval is a platform used by developers to showcase their skills. Developers can participate in app building competitions and win cash/prizes. They can also solve programming challenges as a way to impress employers with their technical skills. Employers can use CodeEval as a way to enhance their brand by launching competitions/programming challenges and as a means to get introduced to the best developers.



Predict the number – challenge description



Sequence 011212201220200112 ... constructed as follows: first is 0, then repeated the following action: already written part is attributed to the right with replacement 0 to 1, 1 to 2, 2 to 0. E.g.

0 -> 01 -> 0112 -> 01121220 -> ...

Create an algorithm which determines what number is on the N-th position in the sequence.

Input sample

Your program should accept as its first argument a path to a filename. Each line in this file contains an integer N such as 0 <= N <= 3000000000. Example:

0
5
101
25684

Output sample

Print out the number which is on the N-th position in the sequence. Example:

0
2
1
0

The particular challenge has a quite low success rate (59.8% - 23/7/2014) and its level of difficulty is medium. More info you can find here.



Solution



For this challenge I tried at least 3 different solutions but, all of them failed due to the time limitation (the code you submit must complete the given test case without errors and within a certain amount of time – a few ms). Finally, after spending some time I discovered the required pattern and the working solution is given below:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

/*
---------------------------------------------------------------------
The code below solves the CodeEval challenge - Predict the number.

Written by:    Christos Samaras
Date:          28/06/2014
e-mail:        xristos.samaras@gmail.com
site:          http://www.myengineeringworld.net
---------------------------------------------------------------------
*/

namespace PredictTheNumber
{
    class Program
    {
        static void Main(string[] args)
        {            
            using (StreamReader reader = File.OpenText(args[0]))
            while (!reader.EndOfStream)
            {
                string line = reader.ReadLine();
                if (line != null)
                {
                    long numberToFind = long.Parse(line);
                    int cnt = 0;
                    bool done = false;

                    if (numberToFind > 0)
                    {
                        for (int i = 0; i < 33; i++)
                        {
                            if (numberToFind < (long)Math.Pow(2, i))
                            {
                                long result = 0;
                                long previous = 0;

                                for (int j = i - 1; j > -1; j--)
                                {
                                    result = numberToFind - previous - (long)Math.Pow(2, j);
                                    if (result == 0)
                                    {
                                        cnt++;
                                        done = true;
                                        break;
                                    }
                                    if (result == 1)
                                    {
                                        cnt += 2;
                                        done = true;
                                        break;
                                    }
                                    if (result > 0)
                                    {
                                        cnt++;
                                        previous += (long)Math.Pow(2, j);
                                    }
                                    if (j == 0)
                                    {
                                        done = true;
                                        break;
                                    }
                                }
                            }
                            if (done)
                                break;
                        }
                        Console.WriteLine((cnt % 3).ToString());
                    }
                    else                    
                    Console.WriteLine("0");            
                }
            }
            Console.ReadLine();
        }
    }
}



Points



Here is the proof that the solution works and the points given by the CodeEval platform.

CodeEval 1 - Predict The Number - Score

Sunday, 20 July 2014

Get Sheets Size Add-In


Introduction



Almost 5 months have passed since the last free add-in. So, today we will see how we can find the largest sheet (in size) from a given workbook. I am sure that some of you may wonder why on earth this information will be useful to someone.  Well, the answer is that in a multi-sheet workbook there are times – usually after some sort of file corruption – where one of the sheets becomes extraordinary large in size (i.e. from a few KBs it might become a few MBs). If you had for example 10 or 20 sheets in your workbook, how you would identify the one that causes the size problem? This add-in might give you the answer.



Add-in description



Get Sheets Size - Ribbon Button

After installing the add-in (see the video below for instructions), in the Home tab of the Ribbon a new button – “Get Sheets Size” – will be shown. If you click this button, the add-in will perform the following actions:
  1. The active workbook will be saved before the actual procedure starts. If for some reason the workbook was not saved before, the add-in will stop and a message box will pop up. In other words, the add-in works with workbooks that have been saved at least once.
  2. After saving the workbook, the add-in exports each sheet into a temporary file. By using the File System Object (FSO), the add-in gets the temporary file size and then the file is deleted.
  3. When the size of every sheet is known, the add-in creates a new single-sheet workbook in which it writes down all the information regarding the individual sheet and the entire workbook. Moreover, it points out the largest sheet in size (in yellow color), as well as the hidden sheets.
Get Sheets Size - Sample Results

Note: Bear in mind that in quite large workbooks the add-in might runs even for a few minutes before the report is completed. 



Demonstration video



The short video below demonstrates the installation and use of the add-in. A multi-sheet workbook is used as a test case.




Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Sunday, 29 June 2014

Quick Weekend Tips


About the Quick Weekend Tips (QWT) post series



QWT is a series of posts that are different from the “typical” posts that are being published in this blog. As the name implies the posts are published during the weekends (Saturday or Sunday) and their subject is quick/short tips about various applications (Excel, Word, Power Point, AutoCAD etc.). These quick tips are accompanied with step by step instructions, as well as with a lot of pictures.



QWT 5: Copy The Full Path Of A File/Folder To The Windows Clipboard



According to Wikipedia: “A full path or absolute path is a path that points to the same location on one file system regardless of the present working directory or combined paths. It is usually written in reference to a root directory”. There are several instances in which the complete path of a file or folder is required. 
  • Folder path example: “C:\Users\Christos\Downloads”
  • File path example: “C:\Users\Christos\Desktop\An Introduction To Pressure Vessels.pdf”
Below you will find two ways to get the full path of a file or folder; the second one is much easier and can be applied to get the full path of multiple folders/files at once.



Using the Security tab of Properties window



Step 1: Right click on the file/folder you want to copy its path.
Step 2: From the right-click shortcut menu that pops up select the Properties option.

Copy The Full Path Of A File-Folder To The Windows Clipboard 1

Step 3: On the Properties window that pops up select the Security tab.
Step 4: Just next to the Object name label select the full path of your file/folder. The selection works similar to the selection of a word on a MS Word. Click just before the first character of the path and then drag the mouse until the entire path is selected.
Step 5: Press CTRL + C to copy the path.
Step 6: Exit from the Properties window by either pressing the Cancel or even the OK button.

Copy The Full Path Of A File-Folder To The Windows Clipboard 2

Optional step: Only for validation purposes, open Notepad or Word and press CTRL + V to paste the path. If you have completed the previous steps successfully the full folder/file path will be shown similar to the picture below:

Copy The Full Path Of A File-Folder To The Windows Clipboard 3



Using the Shift key and Copy as path option



The second method is much simpler and involves only two steps:
Step 1: Hold on the SHIFT key of your keyboard and right click on the file/folder you want to copy its path.
Step 2: From the right-click shortcut menu that pops up select the Copy as path option and the path will be copied to the clipboard!

Copy The Full Path Of A File-Folder To The Windows Clipboard 4

Optional step: Again, just for validation, open Notepad or Word and press CTRL + V to paste the path. Note, that, contrary to the first method, the path appears inside quotation marks – “”.

Copy The Full Path Of A File-Folder To The Windows Clipboard 5



Bonus section: multiple folders/files



Apart from its simplicity, the second method has a significant advantage over the first method: it works also with multiple folders or files. So, if you select multiple files, as shown in the picture below:

Copy The Full Path Of A File-Folder To The Windows Clipboard 6

And repeat the second method – press the SHIFT key on keyboard and select the Copy as path option from the right-click shortcut menu – the results on Notepad would look like this:

Copy The Full Path Of A File-Folder To The Windows Clipboard 7



Limitation



Note that both methods work on Windows Vista and newer Windows versions (7, 8 and 8.1), so none of the above can be applied on Windows XP for example.

Thursday, 26 June 2014

Calculating The Area Of A Simple Polygon Using The Shoelace Algorithm


The basics



It has been quite a while since the last post about mathematical algorithms, so today we will learn how to apply the shoelace algorithm in order to calculate the area of a simple polygon. First of all, what is the definition of “simple polygon”?

According to Wikipedia: ”In geometry a simple polygon is defined as a flat shape consisting of straight, non-intersecting line segments or "sides" that are joined pair-wise to form a closed path. If the sides intersect then the polygon is not simple. The qualifier "simple" is frequently omitted, with the above definition then being understood to define a polygon in general. The definition given above ensures the following properties:
  • A polygon encloses a region (called its interior) which always has a measurable area.
  • The line segments that make-up a polygon (called sides or edges) meet only at their endpoints, called vertices or less formally "corners".
  • Exactly two edges meet at each vertex.
  • The number of edges always equals the number of vertices.
Two edges meeting at a corner are usually required to form an angle that is not straight (180°); otherwise, the collinear line segments will be considered parts of a single side”.

On the other hand, “the shoelace formula, or shoelace algorithm, is a mathematical algorithm to determine the area of a simple polygon whose vertices are described by ordered pairs in the plane. The user cross-multiplies corresponding coordinates to find the area encompassing the polygon, and subtracts it from the surrounding polygon to find the area of the polygon within. It is called the shoelace formula because of the constant cross-multiplying for the coordinates making up the polygon, like tying shoelaces. It is also sometimes called the shoelace method. It is also known as Gauss's area formula, after Carl Friedrich Gauss. It has applications in surveying and forestry, among other areas. It is also called the surveyor's formula”.

The formula can be represented by the expression:

Shoelace Formula

Where:
A: the area of the polygon,
n: the number of sides of the polygon, and
(xi, yi), i = 1, 2,..., n: the vertices (or "corners") of the polygon.

If you need to apply the shoelace algorithm in Excel, below you will find four different methods! You can choose the one that best fit to you needs.



Using the shoelace algorithm in Excel



1st method: Spreadsheet calculations

Spreadsheet Calculations

If the points (x, y) of the polygon are known, we can apply the previous equation using simple arithmetical calculations. In this example (taken from Wikipedia) we have 5 points, so, first we calculate the xi*yi+1 (i.e. first point: 3 * 11 = 33 etc.) and then the xi+1*yi (i.e. first point: 5 * 4 = 20 etc.)  for each point. Note that for convenience purposes we repeated the coordinates of the first point at the end of the table. Next, we sum the xi*yi+1 (i.e. 207) and the xi+1*yi (i.e. 267). Finally we take the absolute value of the difference divided by 2 (i.e. ABS(207 – 267)/2 = 60/2) and the result is the desired polygon area (i.e. 30).


2nd method: SUMPRODUCT formula

With this method you avoid the intermediate calculations and by using twice the function you get the result. However, the level of difficulty is a little bit higher than the first method (especially if you are new to Excel). The method involves the SUMPRODUCT function, the syntax of which is given below:

SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function multiplies the corresponding components in the given arrays, and returns the sum of these products. Array1, array2... are the ranges of cells or arrays that you wish to multiply. All arrays must have the same number of rows and columns and you must enter at least 2 arrays (you can have up to 30 arrays).

The difficult part is the array/range definition. If n polygon points (x, y) are known, the function can be written:

SUMPRODUCT Function

Note that the coordinates of the nth point and the first point must be the same (see the table above), in other words the polygon should be “closed”. In the sample workbook the SUMPRODUCT function is used with the following ranges:

=0.5*ABS(SUMPRODUCT(C4:C8;D5:D9)-SUMPRODUCT(D4:D8;C5:C9))

In reality we apply the same function as in the first method, but instead of single cells, we have multiple cells/arrays. Without doubt, the second method is much more straightforward than the first one.


3rd method: Array formula

This method is actually a variation of the second one, but instead of SUMPRODUCT functions we use an array formula. In this example, the following formula is used:

=0.5*ABS(SUM(C4:C8*D5:D9-D4:D8*C5:C9))

Note that after typing an array formula you must press CTRL + SHIFT + ENTER in order to get the correct result; notice the curly braces “{}” that will enclose your formula.  More info about array formulas you can find here.


4th method: Custom VBA function

Open an existing or create a new Excel workbook, switch to the VBA editor (ALT + F11), go to the menu Insert and select Module. In the newly created module (Module1) add the following lines of code.

Option Explicit

Function PolygonArea(Xs As Variant, Ys As Variant) As Variant
    
    '-----------------------------------------------------------------------
    'Calculates the area of a simple polygon using the shoelace algorithm.
    'Xs and Ys are the known ordered pairs (x,y) - points of the polygon.
    
    'Written by:    Christos Samaras
    'Date:          25/06/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim i       As Integer
    Dim Area    As Double
    
    'Check if the X values are range.
    If Not TypeName(Xs) = "Range" Then
        PolygonArea = "Xs range is not valid"
        Exit Function
    End If
    
    'Check if the Y values are range.
    If Not TypeName(Ys) = "Range" Then
        PolygonArea = "Ys range is not valid"
        Exit Function
    End If
    
    'Check if the number of X values are equal to the number of Y values.
    If Xs.Rows.Count <> Ys.Rows.Count Then
        PolygonArea = "Number of Xs <> Number of Ys"
        Exit Function
    End If
    
    'Check if there are at least 3 points available (i.e. the polygon is at least triangle).
    If Xs.Rows.Count < 3 Then
        PolygonArea = "You need at least 3 points"
        Exit Function
    End If
    
    'Check if the coordinates of the last points are equal to the coordinates of the first point.
    'In other words, check if the polygon is closed and then apply the shoelace algorithm.
    If Xs(Xs.Rows.Count) = Xs(1) And Ys(Ys.Rows.Count) = Ys(1) Then
        'Polygon is closed (last point = first point).
        For i = 1 To Xs.Rows.Count - 1
            Area = Area + (Xs(i + 1) + Xs(i)) * (Ys(i + 1) - Ys(i))
        Next i
    Else
        'The polygon is not considered closed.
        For i = 1 To Xs.Rows.Count - 1
            Area = Area + (Xs(i + 1) + Xs(i)) * (Ys(i + 1) - Ys(i))
        Next i
        'Use the coordinates of the first point to "close" the polygon.
        Area = Area + (Xs(1) + Xs(Xs.Rows.Count)) * (Ys(1) - Ys(Ys.Rows.Count))
    End If
    
    'Finally, calculate the polygon area.
    PolygonArea = Abs(Area / 2)

End Function

The VBA function is quite simple in use. The user just enters two ranges that contain the X and Y coordinates of the polygon points/vertices and the code automatically calculates the enclosed area. Moreover, the function incorporates some basic error handling, so, if the input ranges have an error, the function will display an error message to the user.



Limitations of shoelace algorithm



Non-Simple Polygons

As mentioned earlier, the shoelace algorithm works ONLY with a SIMPLE POLYGON. Consequently, if the polygon crosses or overlaps itself the algorithm will fail. Thus, if your polygon is similar to any of the polygons shown in the above picture do NOT apply any of the presented methods of shoelace algorithms. The results will be INACCURATE.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.



Read also



Numerical Integration In Excel Using The Trapezoidal Rule
Numerical Integration In Excel Using The Composite Simpson's Rul

Thursday, 12 June 2014

Geocoding Using VBA & Google API



Introduction



According to Wikipedia: “Geocoding is the process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or ZIP codes (postal codes). With geographic coordinates the features can be mapped and entered into Geographic Information Systems, or the coordinates can be embedded into media such as digital photographs via geotagging”.

Almost 10 months have passed since my last posts about Google APIs (Trip Distance and Elevation functions accordingly), so, today we will see how to extract the latitude and longitude of a given address using the Google Geocoding API. The developed VBA function can be used directly from Excel, as long as the user provides a valid address. The so-called GetCoordinates function sends a Geocoding API request to the corresponding Google server and, then, uses the Geocoding response to read the appropriate XML nodes in order to extract the required information (latitude, longitude).

Based on GetCoordinates other two functions were derived: GetLatidue and GetLongitude. As their names imply, they return (as a number) the latitude and the longitude correspondingly of the given address, in case the user needs only one of the two returned parameters of the GetCoordinates function.



VBA code



Below you will find the VBA code of the GetCoordinates, GetLatidue and GetLongitude functions. Have in mind that the use of the Google Geocoding API is subject to a limit of 2500 requests per day, so be careful not to exceed this limit.

Option Explicit

Function GetCoordinates(Address As String) As String
    
    '-----------------------------------------------------------------------------------------------------
    'This function returns the latitude and longitude of a given address using the Google Geocoding API.
    'The function uses the "simplest" form of Google Geocoding API (sending only the address parameter),
    'so, optional parameters such as bounds, key, language, region and components are NOT used.
    'In case of multiple results (for example two cities sharing the same name), the function
    'returns the FIRST OCCURRENCE, so be careful in the input address (tip: use the city name and the
    'postal code if they are available).
    
    'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500
    'requests per day, so be careful not to exceed this limit.
    'For more info check: https://developers.google.com/maps/documentation/geocoding
    
    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    
    'Written by:    Christos Samaras
    'Date:          12/06/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim LatitudeNode    As IXMLDOMNode
    Dim LongitudeNode   As IXMLDOMNode
            
    On Error GoTo errorHandler
    
    'Create the request based on Google Geocoding API. Parameters (from Google page):
    '- Address: The address that you want to geocode.
    '- Sensor: Indicates whether your application used a sensor to determine the user's location.
    'This parameter is no longer required.
    Request.Open "GET", "http://maps.googleapis.com/maps/api/geocode/xml?" _
    & "&address=" & Address & "&sensor=false", False
            
    'Send the request to the Google server.
    Request.send
    
    'Read the results from the request.
    Results.LoadXML Request.responseText
    
    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")
    
    'Based on the status node result, proceed accordingly.
    Select Case UCase(StatusNode.Text)
    
        Case "OK"   'The API request was successful. At least one geocode was returned.
            
            'Get the latitdue and longitude node values of the first geocode.
            Set LatitudeNode = Results.SelectSingleNode("//result/geometry/location/lat")
            Set LongitudeNode = Results.SelectSingleNode("//result/geometry/location/lng")
            
            'Return the coordinates as string (latitude, longitude).
            GetCoordinates = LatitudeNode.Text & ", " & LongitudeNode.Text
        
        Case "ZERO_RESULTS"   'The geocode was successful but returned no results.
            GetCoordinates = "The address probably not exists"
            
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
            GetCoordinates = "Requestor has exceeded the server limit"
            
        Case "REQUEST_DENIED"   'The API did not complete the request.
            GetCoordinates = "Server denied the request"
            
        Case "INVALID_REQUEST"  'The API request is empty or is malformed.
            GetCoordinates = "Request was empty or malformed"
        
        Case "UNKNOWN_ERROR"    'Indicates that the request could not be processed due to a server error.
            GetCoordinates = "Unknown error"
        
        Case Else   'Just in case...
            GetCoordinates = "Error"
        
    End Select
        
    'In case of error, release the objects.
errorHandler:
    Set StatusNode = Nothing
    Set LatitudeNode = Nothing
    Set LongitudeNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    
End Function

'--------------------------------------------------------------------------
'The next two functions using the GetCoordinates function in order to get
'the latitude and the longitude correspondingly of a given address.
'--------------------------------------------------------------------------

Function GetLatidue(Address As String) As Double

    Dim Coordinates As String
    
    'Get the coordinates for the given address.
    Coordinates = GetCoordinates(Address)
    
    'Return the latitude as number (double).
    If Coordinates <> "" Then
        GetLatidue = CDbl(Left(Coordinates, WorksheetFunction.Find(",", Coordinates) - 1))
    End If

End Function

Function GetLongitude(Address As String) As Double

    Dim Coordinates As String
    
    'Get the coordinates for the given address.
    Coordinates = GetCoordinates(Address)
    
    'Return the longitude as number (double).
    If Coordinates <> "" Then
        GetLongitude = CDbl(Right(Coordinates, WorksheetFunction.Find(",", Coordinates) - 1))
    End If
    
End Function

NOTE: The GetCoordinates uses the "simplest" form of Google Geocoding API (sending only the address parameter), so, optional parameters such as bounds, key, language, region and components are NOT used. In case of multiple results (for example two cities sharing the same name), the function returns the first occurrence, so be careful in the input address you use. Tip: use the city name and the postal code if they are available. Since I have no intention to copy the entire Google page, anyone who is interested to learn how the Google Geocoding API works, he/she can visit the corresponding page.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.



Read also



Custom Trip Distance Function (VBA & Google Directions API)
Custom Elevation Function (VBA & Google API)