Monday, 8 December 2014

Get Public IP, Local IP & MAC Address Using VBA


Introduction



As the title implies, today we will learn how to retrieve the local and the public IP address, as well as the MAC address of a computer using VBA. First of all, the definition of IP address according to Wikipedia is the following: “An Internet Protocol address (IP address) is a numerical label assigned to each device (e.g., computer, printer) participating in a computer network that uses the Internet Protocol for communication. An IP address serves two principal functions: host or network interface identification and location addressing. Its role has been characterized as follows: A name indicates what we seek. An address indicates where it is. A route indicates how to get there”.

Public or external IP VS local or private IP
A public/external IP address is any valid address, or number, that can be accessed over the Internet.  Internet standards groups, such as the Network Information Center (NIC) or the Internet Assigned Numbers Authority (IANA), are the organizations responsible for registering IP ranges and assigning them to organizations, such as Internet Service Providers (ISPs).

On the other hand, a local/private IP address is any number or address assigned to a device on a private TCP/IP Local Area Network that is accessible only within the Local Area Network.  For a resource inside the Local Area Network to be accessible over the Internet, a device within the Local Area Network must be connected to the Internet with a public IP address, and the networking must be appropriately configured. 

MAC address (from Wikipedia)
“A media access control address (MAC address) is a unique identifier assigned to network interfaces for communications on the physical network segment. MAC addresses are used as a network address for most IEEE 802 network technologies, including Ethernet and WiFi. Logically, MAC addresses are used in the media access control protocol sublayer of the OSI reference model.

MAC addresses are most often assigned by the manufacturer of a network interface controller (NIC) and are stored in its hardware, such as the card's read-only memory or some other firmware mechanism. If assigned by the manufacturer, a MAC address usually encodes the manufacturer's registered identification number and may be referred to as the burned-in address (BIA). It may also be known as an Ethernet hardware address (EHA), hardware address or physical address. This can be contrasted to a programmed address, where the host device issues commands to the NIC to use an arbitrary address”.

Simplistic definitions
When I am thinking of IP or MAC address, one simple term comes always to my mind: the national identity number – ID number. MAC address is the hardware’s ID number (usually the ID number of the network adapter), whereas IP address is the ID number of the computer over the local or the global network. As the ID number is used by the governments of many countries as a means of tracking their citizens, similarly the MAC and IP address are used over a local or global network (internet) in order to track down different computers or devices.



Manual solution



The short video below demonstrates two “manual” ways of retrieving the local IP and MAC address of your computer, as well as a single way to retrieve your public/external IP:
  1. Network connection details (local IP and MAC address).
  2. Command prompt – ipconfig and getmac (local IP and MAC address).
  3. Internet (public IP).

Moreover, the video also presents the results from the VBA functions that are given below.



VBA code



Unfortunately there is no way to get programmatically the public/external IP of a computer without communicating to another computer over the internet. Thus, the GetMyPublicIP function sends a request to http://myip.dnsomatic.com and returns the response text. The GetMyLocalIP function on the other hand, uses WMI in order to get the IP addresses from the network adapters that have the property IPEnabled equal to true; then it returns the first non-empty IP. Finally, the GetMyMACAddress follows a similar approach with GetMyLocalIP function and returns the MAC address of the first adapter that has a non-empty IP.

Option Explicit
    
'----------------------------------------------------------------------------
'This module contains 3 functions for determing the public IP, the local IP
'and the MAC address of the computer that runs those functions.

'Written By:    Christos Samaras
'Date:          22/11/2014
'E-mail:        xristos.samaras@gmail.com
'Site:          http://www.myengineeringworld.net
'----------------------------------------------------------------------------
        
Function GetMyPublicIP() As String

    Dim HttpRequest As Object
    
    On Error Resume Next
    'Create the XMLHttpRequest object.
    Set HttpRequest = CreateObject("MSXML2.XMLHTTP")

    'Check if the object was created.
    If Err.Number <> 0 Then
        'Return error message.
        GetMyPublicIP = "Could not create the XMLHttpRequest object!"
        'Release the object and exit.
        Set HttpRequest = Nothing
        Exit Function
    End If
    On Error GoTo 0
    
    'Create the request - no special parameters required.
    HttpRequest.Open "GET", "http://myip.dnsomatic.com", False
    
    'Send the request to the site.
    HttpRequest.Send
        
    'Return the result of the request (the IP string).
    GetMyPublicIP = HttpRequest.ResponseText

End Function

Function GetMyLocalIP() As String

    'Declaring the necessary variables.
    Dim strComputer     As String
    Dim objWMIService   As Object
    Dim colItems        As Object
    Dim objItem         As Object
    Dim myIPAddress     As String
    
    'Set the computer.
    strComputer = "."
    
    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    
    'A select query is used to get a collection of IP addresses from the network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
    
    'Loop through all the objects of the collection and return the first non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myIPAddress = Trim(objItem.IPAddress(0))
        Exit For
    Next
    
    'Return the IP string.
    GetMyLocalIP = myIPAddress

End Function

Function GetMyMACAddress() As String

    'Declaring the necessary variables.
    Dim strComputer     As String
    Dim objWMIService   As Object
    Dim colItems        As Object
    Dim objItem         As Object
    Dim myMACAddress    As String
    
    'Set the computer.
    strComputer = "."
    
    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    
    'A select query is used to get a collection of network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
    
    'Loop through all the collection of adapters and return the MAC address of the first adapter that has a non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myMACAddress = objItem.MACAddress
        Exit For
    Next
    
    'Return the IP string.
    GetMyMACAddress = myMACAddress

End Function

Note: in the functions I have not included a lot of lines for error handling since I just wanted to show the way that these tasks can be tackled via VBA. If you need to include these functions to a larger application consider adding error handling according to your project needs.



Downloads



Download

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

Wednesday, 19 November 2014

Small Hydropower Plants – Useful Resources (Files & Links)


Introduction



After long time - three and half years (!) – I decided to return to the subject of small hydropower plants. This time I tried to collect some useful resources that have helped me in the past during the elaboration of several hydroelectric studies. The files and the links that are given below will help you learn more about the potential and the advantages of small hydropower plants. Moreover, they might be a good starting point for a more thorough investigation of the subject.



Contents



The zip file that you will find in the downloads section that follows contains the following PDF files:

  1. Blue Energy For A Green Europe
  2. Brochure On Environmental Integration Of Small Hydropower Plants
  3. Checkist On Small Hydropower
  4. Innovative Aspects Of Small Hydro Development For Rural Development In Latin America
  5. Proposal For A European Strategy Of Research, Development And Demonstration For Renewable Energy From SHP
  6. Report On Statistics - Overview Of The Last Decade 1990-2001
  7. Reserve Flow - Effects Of Additional Parameters On Depleted Stretch
  8. Reserve Flow - Short Critical Review Of Methods Of Calculation
  9. SHERPA - Small Hydropower Argument Sheets
  10. Small Hydropower - General Framework For Legislation And Authorization Procedures In The EU
  11. Small Hydropower Engineering - A Wide Area Of Innovations For Green Power Generation
  12. Small Hydropower Fact Sheets
  13. Small Hydropower Roadmap
  14. Small Hydropower Situation In New European Member States And Candidate Countries
  15. State Of The Art Of Small Hydropower In EU - 25
  16. Statistical Releases From The Stream Map Project

The files can be also found at ESHA’s site. I just gather them to a single folder and I created a small index file for easy navigation. Note that the “guide on how to develop a small hydropower plant” is NOT included in the zip file, but it can be downloaded from here.



More resources



Apart from ESHA, more information about small hydropower plants can be found in the following links:
If you have any other useful resource - file or link - to recommend, feel free to leave a comment and I will update this list as soon as possible.



Downloads



Download

The zip file contains the 16 PDF files that were listed above, plus an index (Excel) file.



Read also



How To Develop A Small Hydropower Site
Τα Απαιτούμενα Βήματα Για Την Μελέτη Ενός Υδροηλεκτρικού Έργου

Tuesday, 11 November 2014

CodeEval 2 – Mth To Last Element


About CodeEval post series



CodeEval is a series of posts which are 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 them is similar despite the language you might use.



Mth to last element – challenge description



Write a program which determines the Mth to the last element in a list.

Input sample

The first argument is a path to a file. The file contains the series of space delimited characters followed by an integer. The integer represents an index in the list (1-based), one per line. Example:

a b c d 4
e f g h 2

Output sample

Print to stdout (usually Windows Console) the Mth element from the end of the list, one per line. If the index is larger than the number of elements in the list, ignore that input. Example:

a
g

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



Solution



Despite the medium level of difficulty, I would say that this challenge was probably an easy one! Below you will find a working solution:

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

/*
---------------------------------------------------------------------
The code below solves the CodeEval challenge -  Mth to last element.

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

namespace MthToLastElement
{
    class Program
    {
        static void Main(string[] args)
        {
            using (StreamReader reader = File.OpenText(args[0]))            
            while (!reader.EndOfStream)
            {
                string line = reader.ReadLine();
                if (line != null)
                {
                    List<string> myList = line.Split(' ').ToList();
                    int mth = int.Parse(myList[myList.Count - 1]);
                    if (myList.Count > mth)
                    {                        
                        myList.Reverse();
                        Console.WriteLine(myList[mth]);
                    }
                }
            }                
            Console.ReadLine();
        }
    }
}



Points



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

CodeEval 2 – Mth To Last Element - Score

Friday, 31 October 2014

Send AutoCAD Commands From Excel & VBA


Introduction



About three weeks ago I received an email request from Mohammed. He asked me if it is possible to send AutoCAD commands directly form Excel. To cut a long story short, the answer is yes and the proposed solution is based on the SendCommand method of AutoCAD, which sends a command string from a VB or VBA application to the document to be processed. If the drawing specified isn’t active, it will be made active. This method processes any AutoCAD command-line function, including LISP expressions. So, based on AutoCAD VBA help, the structure of SendCommand method is given below:

object.SendCommand(Command)

Object: (AutoCAD) Document. The object this method applies to.
Command: String; input-only. The command to send to the document.

Note that the sample workbook that you will find in the Downloads section works only with valid AutoCAD commands (obviously). Moreover, unlike the typical AutoCAD behavior, you must first select an object and then apply any modifications to it. So, for example, if you want to move a polyline, you must first send the select command and then the move; the opposite will NOT work!



VBA code



A large portion of the code is used to initialize the AutoCAD object, as well as the active/new drawing. The code is consisted of two loops; the first one concatenates the contents of every column into a single string, whereas the second one sends the concatenated string to AutoCAD via SendCommand method. One tricky part here was the usage of carriage-return character (vbCr) on Select and Select All (AI_SELALL) commands, which allows the objects to remain selected after the loop moves to the next command (which will make the object editing). However, the latter is not required for the newest version of AutoCAD (2015 - version 20).

Option Explicit

'Declaring the API Sleep subroutine.
#If VBA7 And Win64 Then
    'For 64 bit Excel.
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    'For 32 bit Excel.
    Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

Sub SendAutoCADCommands()

    '------------------------------------------------------------------------------------------------------------------------------
    'The macro sends the commands that exist in the sheet named "Send AutoCAD Commands" to the opened or to a new AutoCAD drawing.
    'It uses the AutoCAD SendCommand method to send the user commands. Note that it works only for VALID AutoCAD commands!
    'Moreover, unlike the typical AutoCAD behavior, you must first select an object and then apply any modifications to it.
    'So, if you want to move an object, first send the select command and then the move; the opposite will NOT work!!!
    
    'The code uses late binding, so no reference to external AutoCAD (type) library is required.
    'It goes without saying that AutoCAD, as well as VBA in AutoCAD, must be installed at your computer before running this code.
    
    'Written By:    Christos Samaras
    'Date:          15/10/2014
    'Last Update:   15/12/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 acadCmd     As String
    Dim sht         As Worksheet
    Dim LastRow     As Long
    Dim LastColumn  As Integer
    Dim i           As Long
    Dim j           As Integer
    
    'Set the sheet name that contains the commands.
    Set sht = ThisWorkbook.Sheets("Send AutoCAD Commands")
    
    'Activate the Send AutoCAD Commands sheet and find the last row.
    With sht
        .Activate
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
        
    'Check if there is at least one command to send.
    If LastRow < 13 Then
        MsgBox "There are no commands to send!", vbCritical, "No Commands Error"
        sht.Range("C13").Select
        Exit Sub
    End If
    
    'Check if AutoCAD application is open. If it 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
    
    'Maximize AutoCAD window.
    acadApp.WindowState = 3 '3 = acMax  in early binding
    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
        
    With sht
    
        'Loop through all the rows of the sheet that contain commands.
        For i = 13 To LastRow
            
            'Find the last column.
            LastColumn = .Cells(i, .Columns.Count).End(xlToLeft).Column
            
            'Check if there is at least on command in each row.
            If LastColumn > 2 Then
                
                'Create a string that incorporates all the commands that exist in each row.
                acadCmd = ""
                For j = 3 To LastColumn
                    If Not IsEmpty(.Cells(i, j).Value) Then
                        acadCmd = acadCmd & .Cells(i, j).Value & vbCr
                    End If
                Next j
                 
                'Check AutoCAD version.
                If Val(acadApp.Version) < 20 Then
                    'Prior to AutoCAD 2015, in Select and Select All commands (AI_SELALL) the carriage-return
                    'character 'vbCr' is used, since another command should be applied in the selected items.
                    'In all other commands the Enter character 'Chr$(27)' is used in order to denote that the command finished.
                    If InStr(1, acadCmd, "SELECT", vbTextCompare) > 0 Or InStr(1, acadCmd, "AI_SELALL", vbTextCompare) Then
                       acadDoc.SendCommand acadCmd & vbCr
                    Else
                       acadDoc.SendCommand acadCmd & Chr$(27)
                    End If
                Else
                    'In the newest version of AutoCAD (2015) the carriage-return
                    'character 'vbCr' is applied in all commands.
                    acadDoc.SendCommand acadCmd & vbCr
                End If
            
            End If
            
            'Pause a few milliseconds  before proceed to the next command. The next line is probably optional.
            'However, I suggest to not remove it in order to give AutoCAD the necessary time to execute the command.
            Sleep 20
            
        Next i
        
    End With
    
    'Inform the user about the process.
    MsgBox "The user commands were successfully sent to AutoCAD!", vbInformation, "Done"
      
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.



Warnings and suggestions



  • Please bear in mind that the particular code was NOT tested for every AutoCAD command that can be executed via command line. However, tests for the most “popular” ones shown that it works without problem (see the demonstration video). You just have to write the command sequence in Excel very carefully.
  • When you select an object try to create a selection rectangle that will include that object. This means that the selection rectangle – its coordinates – should be larger than the object dimensions.
  • The coordinates should be given in the format x,y for 2D and x,y,z for 3D objects; i.e. 0,100 and 200,200,100. 
  • The "angular" coordinates should be given in the x<y for 2D and in x<y<z format for 3D; i.e. 0<45 and 100<45<90. 
  • Note that the cells in the sample workbook are formatted as text, in order to avoid problems when the user enters decimal coordinates and the decimal symbol in his/her Windows settings (in Region and Language option at Control Panel) is set to be the comma (",") and not the dot ("."). Tip: AutoCAD uses a dot/point for decimal separator as a standard for displaying and entering decimal numbers. However, Dimensions are the exception to this rule, but, you can set their decimal separator by using the DIMDSEP command.
  • Probably the best way to use the sample workbook is to try to replicate every step that you follow when you are using AutoCAD. So, try to not forget command arguments or leave commands unfinished. Needless to say that some experimentation is required in order to get the expected result, but I think it worth the time.


Demonstration video



The short video below demonstrates the result of the above VBA code (31 AutoCAD commands were used); the blog name will be drawn into a new AutoCAD document using lines, polylines as well as AutoCAD objects.


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. WPF delivers similar functionality with Windows Forms, but with more "style". Usually the application specifications will somehow drive you to the most appropriate platform, although this decision might be difficult due to the existence of many remarkable GUI toolkits (DevExpress, Telerik etc.), which work on both platforms.

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
Send AutoCAD Commands From Excel & VBA 

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 them 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