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)

Monday, 26 May 2014

Using Windows Environment Variables In VBA


Introduction



According to Wikipedia: “Environment variables are a set of dynamic named values that can affect the way running processes will behave on a computer. They are part of the operating environment in which a process runs. For example, a running process can query the value of the TEMP environment variable to discover a suitable location to store temporary files, or the HOME or USERPROFILE variable to find the directory structure owned by the user running the process. They were introduced in their modern form in 1979 with Version 7 Unix, so are included in all Unix operating system flavors and variants from that point onward including Linux and OS X. From PC DOS 2.0 in 1982, all succeeding Microsoft operating systems including Microsoft Windows, and OS/2 also have included them as a feature, although with somewhat different syntax, usage and standard variable names”.

In this post we will focus on Windows environment variables and how we can use them from VBA in order to determine various useful information regarding the operating system (OS). Moreover, we will see how to combine them and write our custom VBA functions.



Available environment variables



Below is a list of various environment variables along with their description:
  • ALLUSERSPROFILE: Lists the location of the All Users Profile.
  • APPDATA: Lists the location where applications store data by default.
  • CD: Lists the current directory string.
  • CLIENTNAME: List the client's NETBIOS name when connected to terminal server session.
  • CMDCMDLINE: Lists the command line used to start the current cmd.exe.
  • CMDEXTVERSION: Lists the version number of the current Command Processor Extensions.
  • COMMONPROGRAMFILES: Lists the path to the Common Files folder.
  • COMPUTERNAME: Lists the name of the computer.
  • COMSPEC: Lists the path to the command shell executable.
  • DATE: Lists the current date.
  • ERRORLEVEL: Lists the error code of the most recently used command.
  • HOMEDRIVE: Lists the drive letter is connected to the user's home directory.
  • HOMEPATH: Lists the full path of the user's home directory.
  • HOMESHARE: Lists the network path to the user's shared home directory.
  • LOGONSEVER: Lists the name of the domain controller that validated the current logon session.
  • NUMBER_OF_PROCESSORS: Lists the number of processors installed on the computer.
  • OS: Lists the name of the operating system. (Windows XP and Windows 2000 list the operating system as Windows_NT.)
  • PATH: Lists the search path for executable files.
  • PATHEXT: Lists the file extensions that the operating system considers to be executable.
  • PROCESSOR_ARCHITECTURE: Lists the processor's chip architecture.
  • PROCESSOR_IDENTFIER: Lists the description of the processor.
  • PROCESSOR_LEVEL: Lists the model number of the computer's processor.
  • PROCESSOR_REVISION: Lists the revision number of the processor.
  • PROGRAMFILES: Lists the path to the Program Files folder.
  • PROMPT: Lists the command-prompt settings for the current interpreter.
  • RANDOM: Lists a random decimal number between 0 and 32767.
  • SESSIONNAME: Lists the connection and session names when connected to terminal server session.
  • SYSTEMDRIVE: Lists the drive containing the Windows root directory.
  • SYSTEMROOT: Lists the location of the Windows root directory.
  • TEMP and TMP: List default temporary directories for applications that are available to users who are currently logged on.
  • TIME: Lists the current time.
  • USERDOMAIN: Lists the name of the domain that contains the user's account.
  • USERNAME: Lists the name of the user currently logged on.
  • USERPROFILE: Lists the location of the profile for the current user.
  • WINDIR: Lists the location of the OS directory.


VBA code



Eight custom VBA functions were developed based on the environment variables listed above. The EnvironmentResult is the most generic one; it returns a string based on a (valid) environment variable used as an input. The three versions of Is64bitOS function demonstrate three different ways of determining if an OS is 32 or 64bit, by using three different environment variables. The UserFolder is an alternative to USERPROFILE variable. Finally, the My+ functions return the path of “Desktop”, “My Documents” and “My Pictures” folders correspondingly.

Option Explicit

'-------------------------------------------------------------
'This module contains some custom functions that demonstrate
'the usage of Windows environment variables in VBA.

'Written by:    Christos Samaras
'Date:          26/05/2014
'e-mail:        xristos.samaras@gmail.com
'site:          http://www.myengineeringworld.net
'-------------------------------------------------------------
    
Function EnvironmentResult(EnvironmentVariable As String) As String
    
    'Returns a string based on the (valid) environment variable that was used as an input.
    
    EnvironmentResult = Environ(EnvironmentVariable)
    
End Function

Function Is64bitOS() As Boolean
    
    'Returns TRUE if the operating system (OS) is 64 bit.
    
    Is64bitOS = Len(Environ("PROGRAMFILES(X86)")) > 0
    
End Function

Function Is64bitOS2() As Boolean
    
    'Returns TRUE if the operating system (OS) is 64 bit (alternative).
    
    Is64bitOS2 = Len(Environ("PROGRAMW6432")) > 0
    
End Function

Function Is64bitOS3() As Boolean
    
    'Returns TRUE if the operating system (OS) is 64 bit (2nd alternative).

    Is64bitOS3 = Len(Environ("COMMONPROGRAMFILES(x86)")) > 0
    
End Function

Function UserFolder() As String

    'Returns the default user folder.
    
    UserFolder = Environ("HOMEDRIVE") & Environ("HOMEPATH")

End Function

Function MyDesktop() As String

    'Returns the Desktop location.

    MyDesktop = Environ("USERPROFILE") & "\Desktop"

End Function

Function MyDocuments() As String

    'Returns the path of My Documents folder.

    MyDocuments = Environ("USERPROFILE") & "\My Documents"

End Function

Function MyPictures() As String

    'Returns the path of My Pictures folder.

    MyPictures = Environ("USERPROFILE") & "\My Pictures"
    
    'For Windows XP:
    'MyPictures = MyDocuments & "\My Pictures"

End Function

All functions are quite straightforward and are based on Environ VBA function, which returns a string associated with an operating system environment variable. As you would probably imagine, it is quite easy to develop your own custom VBA functions based on Windows environment variables.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it, and, after opening it, press CTRL + ALT + F9 in order to recalculate all the formulas in the workbook.

Wednesday, 14 May 2014

Drawing Points In AutoCAD Using Excel & VBA


Introduction



During the previous weekend a blog reader (Denis) sent me an email asking me if it is possible to draw points in AutoCAD from Excel. The answer is quite straightforward and is YES. The proposed solution involves the AddPoint method:

RetVal = object.AddPoint(Point)

Where:
RetVal: Point object. The newly created Point object.
Object: ModelSpace Collection, PaperSpace Collection, Block – the objects this method applies to.
Point: Variant (three-element array of doubles); input-only. The coordinates of the point to be created.

The sample workbook that you will find in the Downloads section below requires three main user inputs: the coordinates of the point (in X, Y, Z), the point type (select from a dropdown list) and the point size. Then, by clicking the “Add Point(s)" button the points are created either in the active drawing (if AutoCAD is already lunched), or in a newly created drawing. The picture below summarizes the available point types (you will find the picture also in the sample workbook).

AutoCAD Point Types

Note: I would like to clarify that all the VBA codes presented in this blog does NOT work with AutoCAD LT. AutoCAD LT does NOT support VBA. Use a full AutoCAD version instead.



VBA code



The code is actually a loop; most of the code is used for initializing the AutoCAD object, as well as the active/new drawing.

Option Explicit

Sub DrawPoints()

    '--------------------------------------------------------------------------------------------------
    'Draws points in AutoCAD using data - point coordinates, type and size - from Excel.
    '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:          14/05/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 acadCircle      As Object
    Dim LastRow         As Long
    Dim i               As Long
    Dim Point(0 To 2)   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 point.
    If LastRow < 2 Then
        MsgBox "There are no coordinates to draw a point!", vbCritical, "Point Coordinates Error"
        Exit Sub
    End If
    
    'Check if AutoCAD application is open.
    On Error Resume Next
    Set acadApp = GetObject(, "AutoCAD.Application")
    
    'If AutoCAD is not opened create a new instance and make it visible.
    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
         
    'Loop through all the coordinates and draw the corresponding point(s).
    With Sheets("Coordinates")
    
        'Set the point type.
        acadDoc.SetVariable "PDMODE", .Range("E1").Value
        
        'Set the point size.
        acadDoc.SetVariable "PDSIZE", .Range("G1").Value
                    
        'Loop through all the coordinates.
        For i = 2 To LastRow
            
            'Set the point coordinates.
            Point(0) = .Range("A" & i).Value
            Point(1) = .Range("B" & i).Value
            Point(2) = .Range("C" & i).Value

            'Draw the point.
            acadDoc.ModelSpace.addpoint (Point)
            
        Next i
        
    End With
    
    'Zoom in to the drawing area.
    acadApp.ZoomExtents
    
    'Release the objects.
    Set acadCircle = Nothing
    Set acadDoc = Nothing
    Set acadApp = Nothing
    
    'Inform the user about the process.
    MsgBox "The point(s) was/were successfully drawn in AutoCAD!", vbInformation, "Finished"

End Sub

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.



Demonstration video



The short video below demonstrates the result of the above VBA code; a "happy face" consisted of 10 points will be drawn in a new AutoCAD drawing.


Monday, 5 May 2014

PDF Search Through VBA


Introduction



The motive behind this post came from an email question that I received from a blog reader during the previous weekend. Jason wrote: I am trying to create a link from Excel to search in a PDF file”. So, in this post I will try to give an answer to this question. In general, there two possible solutions in this problem (OK, maybe there are other solutions that I am not aware of), both of which having their own advantages and disadvantages.

The FindText method

Syntax: object.FindText(text to find, case sensitive, whole words only, beginning)

Description: The FindText method returns true if the text was found or false if it was not. If the return value is true, it finds the specified text (the first instance), scrolls so that it is visible and highlights it.

Here are the 4 arguments of this method:
Text to find: The text that is to be found.
Case sensitive: If true, the search is case-sensitive. If false, it is case-insensitive.
Whole words only: If true, the search matches only whole words. If false, it matches partial words.
Beginning: If true, the search begins on the first page of the document. If false, it begins on the current page.

Pros: Useful when searching a text phrase in the PDF document (more than one word).
Cons: In some cases it doesn't work (doesn't highlight the text). Although it might be an easy and fast method, unfortunately is not 100% reliable.

The “JSO approach”

Unlike FindText, the JSO approach doesn’t use a “native method”, but, in reality, is two loops one inside the other. The idea is to loop through all the words of the entire PDF document and compare each word with the text we are searching for. If the comparison is true, the word is highlighted, otherwise the next word is proceeded. The name of this solution comes from the Java Script Object (JSO) that performs all the hard work.

Pros: Useful when searching a SINGLE WORD in the PDF document (not phrase). It’s a quite reliable method.
Cons: In case you search two words for example in the PDF it doesn't find anything. In large PDFs it might be considerably slow.

Unfortunately, there is no straight solution to this problem, but only a sort of compromise. You will either go with the unreliable FindText method or with the slow JSO approach (only if you are searching a single word). Needless to say that the VBA code that you will find below works ONLY with Adobe Professional. If you try to use it with the Adobe Reader you will get an error.



VBA code



The FindTextInPDF macro uses the FindText method to find a text phrase inside a PDF document.

Option Explicit

Sub FindTextInPDF()
      
    '----------------------------------------------------------------------------------------
    'This macro can be used to find a specific TEXT (more than one word) in a PDF document.
    'The macro opens the PDF, finds the specified text (the first instance), scrolls so
    'that it is visible and highlights it.
    'The macro uses the FindText method (see the code below for more info).
    
    'Note that in some cases it doesn't work (doesn't highlight the text), so in those
    'cases prefer the SearchTextInPDF macro, if you have only ONE WORD to find!

    'The code uses late binding, so no reference to external library is required.
    'However, the code works ONLY with Adobe Professional, so don't try to use it with
    'Adobe Reader because you will get an "ActiveX component can't create object" error.
    
    'Written by:    Christos Samaras
    'Date:          04/05/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '----------------------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim TextToFind  As String
    Dim PDFPath     As String
    Dim App         As Object
    Dim AVDoc       As Object
               
    'Specify the text you wawnt to search.
    'TextToFind = "Christos Samaras"
    'Using a range:
    TextToFind = ThisWorkbook.Sheets("PDF Search").Range("C5").Value
           
    'Specify the path of the sample PDF form.
    'Full path example:
    'PDFPath = "C:\Users\Christos\Desktop\How Software Companies Die.pdf"
    'Using workbook path:
    'PDFPath = ThisWorkbook.Path & "\" & "How Software Companies Die.pdf"
    'Using a range:
    PDFPath = ThisWorkbook.Sheets("PDF Search").Range("C7").Value
   
    'Check if the file exists.
    If Dir(PDFPath) = "" Then
        MsgBox "Cannot find the PDF file!" & vbCrLf & "Check the PDF path and retry.", _
                vbCritical, "File Path Error"
        Exit Sub
    End If
   
    'Check if the input file is a PDF file.
    If LCase(Right(PDFPath, 3)) <> "pdf" Then
        MsgBox "The input file is not a PDF file!", vbCritical, "File Type Error"
        Exit Sub
    End If
    
    On Error Resume Next
    
    'Initialize Acrobat by creating the App object.
    Set App = CreateObject("AcroExch.App")
    
    'Check if the object was created. In case of error release the object and exit.
    If Err.Number <> 0 Then
        MsgBox "Could not create the Adobe Application object!", vbCritical, "Object Error"
        Set App = Nothing
        Exit Sub
    End If
    
    'Create the AVDoc object.
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    
    'Check if the object was created. In case of error release the objects and exit.
    If Err.Number <> 0 Then
        MsgBox "Could not create the AVDoc object!", vbCritical, "Object Error"
        Set AVDoc = Nothing
        Set App = Nothing
        Exit Sub
    End If
    
    On Error GoTo 0
    
    'Open the PDF file.
    If AVDoc.Open(PDFPath, "") = True Then
        
        'Open successful, bring the PDF document to the front.
        AVDoc.BringToFront
        
        'Use the FindText method in order to find and highlight the desired text.
        'The FindText method returns true if the text was found or false if it was not.
        'Here are the 4 arguments of the FindText methd:
        'Text to find:          The text that is to be found (in this example the TextToFind variable).
        'Case sensitive:        If true, the search is case-sensitive. If false, it is case-insensitive (in this example is True).
        'Whole words only:      If true, the search matches only whole words. If false, it matches partial words (in this example is True).
        'Search from 1st page:  If true, the search begins on the first page of the document. If false, it begins on the current page (in this example is False).
        If AVDoc.FindText(TextToFind, True, True, False) = False Then

            'Text was not found, close the PDF file without saving the changes.
            AVDoc.Close True
            
            'Close the Acrobat application.
            App.Exit
               
            'Release the objects.
            Set AVDoc = Nothing
            Set App = Nothing
            
            'Inform the user.
            MsgBox "The text '" & TextToFind & "' could not be found in the PDF file!", vbInformation, "Search Error"
            
        End If
        
    Else
        
        'Unable to open the PDF file, close the Acrobat application.
        App.Exit

        'Release the objects.
        Set AVDoc = Nothing
        Set App = Nothing
        
        'Inform the user.
        MsgBox "Could not open the PDF file!", vbCritical, "File error"
        
    End If
    
End Sub

And here is the code for the second macro SearchWordInPDF, which uses the JSO approach.

Option Explicit

Sub SearchWordInPDF()
      
    '----------------------------------------------------------------------------------------
    'This macro can be used to find a specific WORD in a PDF document (one word ONLY -> in
    'case you search two words for example it doesn't find anything, just opens the file).
    'The macro opens the PDF, finds the first appearance of the specified word, scrolls
    'so that it is visible and highlights it.

    'The code uses late binding, so no reference to external library is required.
    'However, the code works ONLY with Adobe Professional, so don't try to use it with
    'Adobe Reader because you will get an "ActiveX component can't create object" error.
    
    'Written by:    Christos Samaras
    'Date:          04/05/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim WordToFind  As String
    Dim PDFPath     As String
    Dim App         As Object
    Dim AVDoc       As Object
    Dim PDDoc       As Object
    Dim JSO         As Object
    Dim i           As Long
    Dim j           As Long
    Dim Word        As Variant
    Dim Result      As Integer

    'Specify the text you want to search.
    'WordToFind = "Engineering"
    'Using a range:
    WordToFind = ThisWorkbook.Sheets("PDF Search").Range("C12").Value
    
    'Specify the path of the sample PDF form.
    'Full path example:
    'PDFPath = "C:\Users\Christos\Desktop\How Software Companies Die.pdf"
    'Using workbook path:
    'PDFPath = ThisWorkbook.Path & "\" & "How Software Companies Die.pdf"
    'Using a range:
    PDFPath = ThisWorkbook.Sheets("PDF Search").Range("C14").Value
    
    'Check if the file exists.
    If Dir(PDFPath) = "" Then
        MsgBox "Cannot find the PDF file!" & vbCrLf & "Check the PDF path and retry.", _
                vbCritical, "File Path Error"
        Exit Sub
    End If
   
    'Check if the input file is a PDF file.
    If LCase(Right(PDFPath, 3)) <> "pdf" Then
        MsgBox "The input file is not a PDF file!", vbCritical, "File Type Error"
        Exit Sub
    End If
    
    On Error Resume Next
    
    'Initialize Acrobat by creating the App object.
    Set App = CreateObject("AcroExch.App")
    
    'Check if the object was created. In case of error release the objects and exit.
    If Err.Number <> 0 Then
        MsgBox "Could not create the Adobe Application object!", vbCritical, "Object Error"
        Set App = Nothing
        Exit Sub
    End If
    
    'Create the AVDoc object.
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    
    'Check if the object was created. In case of error release the objects and exit.
    If Err.Number <> 0 Then
        MsgBox "Could not create the AVDoc object!", vbCritical, "Object Error"
        Set AVDoc = Nothing
        Set App = Nothing
        Exit Sub
    End If
    
    On Error GoTo 0
    
    'Open the PDF file.
    If AVDoc.Open(PDFPath, "") = True Then
        
        'Open successful, bring the PDF document to the front.
        AVDoc.BringToFront
        
        'Set the PDDoc object.
        Set PDDoc = AVDoc.GetPDDoc
        
        'Set the JS Object - Java Script Object.
        Set JSO = PDDoc.GetJSObject
        
        'Search for the word.
        If Not JSO Is Nothing Then
        
            'Loop through all the pages of the PDF.
            For i = 0 To JSO.numPages - 1
            
                'Loop through all the words of each page.
                For j = 0 To JSO.getPageNumWords(i) - 1
                    
                    'Get a single word.
                    Word = JSO.getPageNthWord(i, j)
                    
                    'If the word is string...
                    If VarType(Word) = vbString Then
                        
                        'Compare the word with the text to be found.
                        Result = StrComp(Word, WordToFind, vbTextCompare)
                        
                        'If both strings are the same.
                        If Result = 0 Then
                            'Select the word and exit.
                            Call JSO.selectPageNthWord(i, j)
                            Exit Sub
                        End If
                        
                    End If
                    
                Next j
                
            Next i
            
            'Word was not found, close the PDF file without saving the changes.
            AVDoc.Close True
            
            'Close the Acrobat application.
            App.Exit
               
            'Release the objects.
            Set JSO = Nothing
            Set PDDoc = Nothing
            Set AVDoc = Nothing
            Set App = Nothing
            
            'Inform the user.
            MsgBox "The word '" & WordToFind & "' could not be found in the PDF file!", vbInformation, "Search Error"
            
        End If
        
    Else
                
        'Unable to open the PDF file, close the Acrobat application.
        App.Exit

        'Release the objects.
        Set AVDoc = Nothing
        Set App = Nothing
        
        'Inform the user.
        MsgBox "Could not open the PDF file!", vbCritical, "File error"
        
    End If
    
End Sub

Both macros were tested using a PDF file that was created based on this article.



Downloads



Download

The zip file contains an Excel file and a sample PDF file. The Excel file can be opened with Excel 2007 or newer. Please enable macros before using it.