Monday, 23 February 2015

Human Comfort & HVAC System Operation


Introduction



The goal of the heating, ventilating, and air conditioning (HVAC) system is to create and maintain a comfortable environment within a building. Depending on geographic location and building construction, various types of interior climate control systems help ensure that interior spaces are maintained at comfortable levels year-round. With today’s energy conservation concerns, buildings are constructed to be much tighter, reducing the level of natural exchange between indoor and outdoor air. As a result, more and more buildings rely on mechanical conditioning and distribution systems for managing air.

A properly operated HVAC system finds the often delicate balance between optimizing occupant comfort while controlling operating costs. Comfort is an important issue for occupant satisfaction, which can directly affect concentration and productivity. At the same time, controlling these comfort and health parameters directly affects HVAC system operating costs in terms of energy, maintenance and equipment life. Below will be analyzed six of these parameters:

  1. Temperature and humidity management
  2. Air movement (drafts)
  3. Radiant heating/cooling effects
  4. Airborne chemicals
  5. Different indoor building conditions
  6. Building occupant personal preferences


1. Temperature and humidity management



Temperature And Humidity Management


The sensation of being hot or cold depends on both temperature and humidity. Temperature and humidity must be controlled and monitored together. For example, on a very cold dry day, the facility may need to operate warmer than a cold damp day. If humidity in the building is low in the summer, a warmer set point will achieve adequate comfort. Conversely, if your facility is operating at the maximum humidity of 60%, a cooler temperature set point should be set in order to achieve adequate comfort. In many cases the control of both temperature and humidity is called “enthalpy control”.

Taking into account the above the obvious question is: what exactly are the ideal temperature set points? Well, while there is no specific answer to this question, for temperature and humidity management, ASHRAE’s standards 55 and 62 indicate that controlling to 25 – 50% relative humidity is ideal, with 60% as the upper limit to stay within the human comfort zone. Temperature set points are established based on the amount of humidity present. For office buildings, if building pressure is maintained and fresh air make-up is limited during unoccupied periods, humidity will not exceed 55%. However, sometimes reheat is necessary for controlling humidity. In those cases, occupant comfort should be enhanced by using innovative means. For example, direct cool air can be used near a source of heat such as a television, copier, or other appliances. Few buildings actively humidify the air to prevent low humidity excursions because of equipment vulnerability to mineral and mold accumulation; equipment would require dedicated and routine maintenance.



2. Air movement and drafts



Air Movement And Drafts

Excessive air movement in buildings is probably rare, but it can be a source of discomfort and complaints. In addition, noisy air handlers and supply ducts may give the sense of excessive air movement that can lead to complaints of drafts and uncomfortable temperature. Excessive air movement is generally not desirable in the winter or summer months.

Air movement and radiant energy losses require detailed work on specific areas where problems are being experienced. For air movement issues, facility management may retrofit higher efficiency discharge diffusers with designs better suited for the location and that direct supply air away from occupants. Lower duct velocities may be achieved by providing lower temperature conditioned air in the cooling season and warmer air in winter; thus requiring lower air volumes to be supplied. Sometimes air duct supply volume is simply a tuning problem, where variable air volume terminal air boxes’ minimum air flow setting is not enough to prevent excessive supply air volumes during low demand periods for heating/cooling. Radiant heat transfer discomfort can be addressed by adjustable window shades and curtains. If these are impractical, thin films are available to apply to the window to reduce infrared energy transmission.



3. Radiant heating and cooling



Radiant Heating And Cooling

Radiant heating and cooling is a sensation that is common with building occupants who work near windows and where there are high ceilings. Radiation is a form of heat transfer that occurs due to infrared radiation from warmer bodies to cooler ones. Unlike air flow (convective heating), radiation can penetrate windows easily. When applying this form of heat transfer in a building, heat can be either gained or lost through windows. Thus, occupants near windows can feel a sensation of warmth if it is hot outside or a sensation of being chilly on very cold days. The orientation of windows also plays a role. Heating may be lost or gained based on if the window faces a sunny, cloudy or a deep blue clear sky (cooling). Typically, occupants on the north side of a building feel a cooling affect while occupants on the south and west side feel a heating affect. Discomfort will be greatest near ‘single pane’ windows, where radiant heating or cooling has the greatest effect.

As a short term solution, the radiant cooling or heating effects can be addressed by adjusting building temperature set points to compensate. Of course, the long term and appropriate permanent solution is to address radiant cooling and heating losses with high efficiency windows. Double pane windows with “low-e” coatings – a microscopically thin, virtually invisible, metal or metallic oxide layer on a window or skylight glazing surface for the purpose of reducing radiative heat flow – greatly reduce radiant heat transfer, as do drapes and blinds.

Radiant heating and cooling also relates to passive solar design, which uses windows, walls, and floors to absorb and distribute the sun's heat in the winter and reject solar heat in the summer without the use of mechanical systems. It can also maximize the use of sunlight for interior illumination. Buildings designed for passive solar incorporate large south-facing windows, long walls running east to west, and a thermal mass to absorb and slowly release the sun's heat. Passive solar designs also incorporate natural ventilation and roof overhangs to block the sun's strongest rays during the summer, but allow heat to penetrate in the winter.

Passive solar design techniques are applied most easily in new construction because they involve integral design elements of the building. However, existing buildings can be adapted or "retrofitted" such as installing double-pane windows, thermal floors, and a new HVAC system to passively store solar heat and make the facility more energy efficient.



4. Airborne chemicals



Airborne Chemicals

Airborne contaminants have become a major issue in the last 20 years. This is a consequence of sealing buildings tightly for energy efficiency, while having older HVAC systems with improper fresh air make-up settings, as well as poor building operation due to inadequate or missing system operations manuals. The following are contributing factors to poor indoor air quality and suggestions on how airborne contaminants can be greatly reduced.

Biological toxins can be produced from live colonies of mold thriving due to excessive humidity in the occupied space and duct work. Mold can also flourish in condensate drip pans that drain poorly. These conditions cannot be tolerated due to the potential severe health impact. To combat these conditions, regular humidity monitoring and drip pan inspections are needed. Automation systems will monitor humidity minute by minute and drip pan inspections by operations staff should be performed quarterly – when filters are changed.

Volatile Organic Compounds (VOC’s) are airborne chemicals emitted from many building products used in new construction, renovation and restoration projects. These chemicals can cause respiratory irritation and distress in vulnerable people such as children, elderly, and people with allergies or immunodeficient conditions. The sources of these chemicals can be furniture, high-VOC paints, coatings, particle board, caulking, adhesives, fillers, janitorial cleaners and waxes. Even natural gas or propane fired heating and cooking systems can be a source, when poorly vented. It is important to manage VOC levels and keep them as low as possible. Facility managers should maintain adequate ventilation, with fresh air make-up to keep VOC’s below 1 ppm (parts per million) and with no odors.

Inadequate ventilation can be a significant problem when a building lacks sufficient fresh air make-up, particularly in areas of high occupant density such as theaters, meeting rooms, and classrooms, or areas where the concentration of airborne contaminants may be higher due to the nature of the workspace environment. The ASHRAE standard for adequate ventilation is a minimum of 15 cfm of outdoor air per occupant (20 cfm/person in office spaces). Excessive carbon dioxide (CO2) is a good indicator of inadequate fresh air make-up and a useful tool for monitoring indoor air quality. Areas of high density should be equipped with CO2 sensors that control outside air intake to enable effective control. Most operators maintain set fresh air make-up systems to open at 900 ppm. Humidity is also a substantial problem in high density populated areas, thus using humidity controls for congregating areas is also a good strategy for increasing ventilation efficiency.



5. Difference of indoor conditions in the building



Difference Of Indoor Conditions In The Building

Comfort complaints may result from rapid changes in indoor environmental conditions and/or large variations from one area of the building to the next. These swings in indoor environments may be due to thermostats, humidity sensors, and CO2 sensors not calibrated or operating properly, as well as poor design and/or missing sensors. Sometimes necessary equipment is value-engineered (applying various techniques in order to provide the necessary function at the lowest overall cost) out of new construction and renovations. Improper placement of sensors and not installing enough zones to properly control a space are other causes of variable conditions within a space. Even when these issues are properly covered, unevenness in building conditions may still occur from occupant activity such as office partitions (cubicles) and operating desk comfort heaters, which can bias building sensors.



6. Building occupants



Building Occupants

Building managers need to be sensitive to the types of people occupying their buildings and their level of activity. An older workforce, people with disabilities and people working in sedimentary jobs generally require a warmer building environment in order to be comfortable. Younger workers, people who are a bit over weight and those in active jobs usually are more comfortable in cooler environments.



References



The post was based on material found on the following ASHRAE handbooks and code standards:

ASHRAE - Fundamentals of HVAC Systems
ASHRAE - HVAC Systems And Equipment
ASHRAE 55 - Temperature Standards
ASHRAE 62 - Ventilation
ASHRAE 90.1 – Energy Efficiency in Commercial Buildings



Read also



8 Key Factors That Affect The Selection Of A HVAC System
Control Loops Used In HVAC Applications

Wednesday, 21 January 2015

Website Log-In Automation With VBA


Introduction



In the last few months I saw a lot of people struggling to automate the log-in procedure to various websites using VBA. To be honest, web-related tasks can be considered as advanced VBA topics, since in many cases require basic knowledge of HTML language. In this post I will try to provide some insights about how to automate the log-in procedure via VBA. More precisely, I will analyze the concept behind a reusable macro that I developed. The macro creates a new instance of Internet Explorer, navigates to the desired webpage, enters the username and password values in the corresponding text boxes of the webpage, and, finally, presses the sign-in button in order to complete the log-in procedure.



VBA code


A. Code analysis

Almost half of the code of “WebsiteLogIn” macro is used to start a new instance of Internet Explorer and navigate to the requested URL. Since late binding is used, it is necessary to use the CreateObject method and ensure that the object was created. The ShowWindow API is used to maximize the Internet Explorer window. Before the main procedure starts, the IsURLValid function is used to find if the target URL exists (see more about IsURLValid below). If yes, the procedure continues, otherwise an error message pops up.

The other half code is dominated by the usage of getElementById method, which returns a reference to an HTML element by using its ID. So, let’s say a few HTML things: When an HTML document is loaded into a web browser, it becomes a document object. The document object is the root node of the HTML document and the "owner" of all other nodes (element nodes, text nodes, attribute nodes, and comment nodes). The document object provides properties and methods to access all node objects (using JavaScript for example). One of the available document object methods, which is used in this example, is the getElementById method.

Finding Element ID Inside The HTML Code

The getElementById method is used to find 3 elements on the target webpage: the username and password text boxes, as well as the sign-in button. This information is user-input, along with the URL of the webpage, the username and the password for logging-in. But, how to find the element IDs so as to use this macro? Fortunately, with nowadays web browsers this is not a difficult task; you just have to follow the next 7 steps:

  1. Open your favorite web browser and navigate to the website you want to automate the logging-in procedure.
  2. Right click on the username/password text box or on the sign-in button.
  3. On the context menu that pops up, select the “Inspect Element” option.
  4. At the bottom of the webpage a new window will appear containing the HTML code of the webpage.
  5. In the highlighted line(s) of the HTML code try to find the id property.
  6. The desired element ID will be inside the quotation marks ("").
  7. Repeat this procedure (steps 2 to 6) for all three elements (username text box, password text box and sign-in button).

The getElementById method was preferred in the particular case instead of other document object methods, such as getElementsByName, getElementsByTagName and getElementsByClassName, because the majority of web developers almost always assign unique ID values to the HTML elements of the webpages that they design. Returning to the "WebsiteLogIn" macro, if the getElementById finds the user-input ID of the element, it will assign a value if the element is a (username/password) text box, or it will invoke the click method if the element is a (sign-in) button. In any case, if the element ID is not found within the DOM (Document Object Model) of the webpage an error message will pop up, informing the user about the failure.

B. WebsiteLogIn macro

Option Explicit

'Declaring the necessary ShowWindow API function and the constant to maximize Internet Explorer window.
#If VBA7 And Win64 Then
    
    'For 64 bit Excel.
    Public Declare PtrSafe Function ShowWindow Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                    ByVal nCmdShow As Long) As Long
                                    
#Else

    'For 32 bit Excel.
    Public Declare Function ShowWindow Lib "user32" _
                            (ByVal hwnd As Long, _
                            ByVal nCmdShow As Long) As Long
                            
#End If

Public Const SW_MAXIMIZE = 3

Sub WebsiteLogIn(URL As String, UNElementID As String, UserName As String, _
                PWElementID As String, Password As String, SIElementID As String)
    
    '--------------------------------------------------------------------------------------------------------------------------
    'This macro can be used in order to log-in to a website automatically. It requires 6 parameters, which are analyzed below.
    'The macro creates a new instance of Internet Explorer, navigates to the desired site, enters the username and password
    'values in the corresponding text boxes and presses the sign-in button in order to log-in to the site.
    'The code uses late binding, so no reference to external library is required.
    
    'Required parameters:
    'URL: The website URL you want to log-in. Example: https://login.yahoo.com/?.src=ym&done=https%3a//mail.yahoo.com
    'UNElementID: The element ID of the text box, in which you write the UserName. Example: login-username
    'UserName: The username that is used for log-in. Example: MyUserName
    'PWElementID: The element ID of the text box, in which you write the Password. Example: login-passwd
    'Password: The password that is used for log-in. Example: MyPassword
    'SIElementID: The element ID of the button that you press in order to log-in. Example: login-signin
    
    'NOTE: in order to specify the values of UNElementID, PWElementID and SIElementID parameters, navigate with your browser
    'to the target page, select the username/password text boxes or the sign-in button, right click on it with the mouse and
    'press the "Inspect Element" from the pop-up menu. In the new window that will appear at the bottom of the page find in
    'the highlighted line of the HTML code the property id= and the ID of the element will be inside the quotation marks ("").
    
    'Written By:    Christos Samaras
    'Date:          18/01/2015
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim IE              As Object
    Dim IEPage          As Object
    Dim IEPageElement   As Object
    
    'Check if the requested URL is valid.
    If IsURLValid(URL) = False Then
        MsgBox "Sorry, the URL you provided is not valid!", vbCritical, "URL Error"
        Exit Sub
    End If
    
    'Create a new Internet Explorer instance, make it visible and maximize its window.
    On Error Resume Next
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    ShowWindow IE.hwnd, SW_MAXIMIZE
    
    'Check if the ojbect was created.
    If Err.Number <> 0 Then
        MsgBox "Sorry, it was impossible to start Internet Explorer!", vbCritical, "Internet Explorer Error"
        Exit Sub
    End If
    
    'Navigate to the requested URL.
    IE.navigate URL
    
    'Wait until the web page is fully loaded.
    Do Until IE.readyState = 4 'READYSTATE_COMPLETE in early binding
        DoEvents
    Loop
    
    'Get the document of the URL.
    Set IEPage = IE.document
    
    'Find the UserName text box using the element ID.
    Set IEPageElement = IEPage.getElementById(UNElementID)
    If Not IEPageElement Is Nothing Then
        'Pass the UserName value to the corresponding text box.
        IEPageElement.Value = UserName
        Set IEPageElement = Nothing
    Else
        'The element ID was not found, inform the user.
        MsgBox "Coould not find the '" & UNElementID & "' element ID on the page!", vbCritical, "Element ID Error"
        Exit Sub
    End If
    
    'Find the Password text box using the element ID.
    Set IEPageElement = IEPage.getElementById(PWElementID)
    If Not IEPageElement Is Nothing Then
        'Pass the Password value to the corresponding text box.
        IEPageElement.Value = Password
        Set IEPageElement = Nothing
    Else
        'The element ID was not found, inform the user.
        MsgBox "Coould not find the '" & PWElementID & "' element ID on the page!", vbCritical, "Element ID Error"
        Exit Sub
    End If
    
    'Find the Sign-In button using the element ID.
    Set IEPageElement = IEPage.getElementById(SIElementID)
    If Not IEPageElement Is Nothing Then
        'Click the Sign-In button to enter the site.
        IEPageElement.Click
    Else
        'The element ID was not found, inform the user.
        MsgBox "Coould not find the '" & SIElementID & "' element ID on the page!", vbCritical, "Element ID Error"
        Exit Sub
    End If
    
    'Release the objects.
    Set IEPageElement = Nothing
    Set IEPage = Nothing
    Set IE = Nothing

End Sub

C. IsURLValid function

IsURLValid Function Used In Worksheet

The code for the IsURLValid function follows. Note that this function could be also used as a built-in function. If you have various websites or individual webpages and you need to check if their URLs are valid you can use this function. In the workbook that you will find on the Downloads section I have implemented this technique in order to add the function’s description, so as to look more like a built-in function.

Function IsURLValid(URL As String) As Boolean
    
    '--------------------------------------------------------------------
    'Checks if a URL is valid; returns True if exists and False if not.
    
    'Written By:    Christos Samaras
    'Date:          18/01/2015
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim Request As Object
    Dim Result  As String

    On Error Resume Next
    
    'Create the WinHttpRequest object and check if the object was created.
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    If Err.Number <> 0 Then
        MsgBox "Could not create the WinHttpRequest object!", vbCritical, "WinHttpRequest Error"
        Exit Function
    End If
    
    'Create the request using the input URL.
    Request.Open "GET", URL, False
    
    'Send the request to the server.
    Request.send
    
    'Read the request status.
    Result = Request.StatusText
            
    'Check if the request status is "OK" - the URL exists.
    If InStr(1, Result, "OK", vbTextCompare) > 0 Then IsURLValid = True
    
    'Release the WinHttpRequest object.
    Set Request = Nothing

End Function

D. Sample macros

Finally, there are 3 sample macros that use the “WebsiteLogIn” macro in order to log-in to Gmail, Yahoo mail and Facebook. In all macros (“GmailLogIn”, “YahooMailLogIn” and “OtherLogIn”) I used the 7-step procedure that was described above in order to get the correct element IDs from the corresponding webpages.

Option Explicit

Sub GmailLogIn()
    
    'Sample usage of WebsiteLogIn macro for logging-in to Gmail.
    
    WebsiteLogIn "https://accounts.google.com/ServiceLogin?service=mail&continue=https://mail.google.com/mail", _
                    "Email", Sheets("Log-In").Range("C6").Value, _
                    "Passwd", Sheets("Log-In").Range("C8").Value, "signIn"
            
End Sub

Sub YahooMailLogIn()
    
    'Sample usage of WebsiteLogIn macro for logging-in to Yahoo email.
    'Just compare the element IDs of the username and password text boxes, as well as
    'the element ID of the sign-in button with the corresponding values for the Gmail case.
    
    WebsiteLogIn "https://login.yahoo.com/?.src=ym&done=https%3a//mail.yahoo.com", _
                    "login-username", Sheets("Log-In").Range("C13").Value, _
                    "login-passwd", Sheets("Log-In").Range("C15").Value, "login-signin"
            
End Sub

Sub OtherLogIn()
    
    'Sample usage of WebsiteLogIn macro for logging-in to any page, as long as the necessary
    'parameters have been specified (in the Log-In sheet we use Facebook as an example).
    
    With Sheets("Log-In")
    
        If .Range("G20").Value = False Then
            MsgBox "Sorry, but the URL you entered doesn't exist!", vbCritical, "Invalid URL Error"
            Exit Sub
        End If
        
        WebsiteLogIn .Range("C20").Value, .Range("C22").Value, .Range("C24").Value, _
                        .Range("C26").Value, .Range("C28").Value, .Range("C30").Value
                        
    End With
            
End Sub



How to use the sample workbook



The “Gmail” and “Yahoo” headings of the workbook I think that are self-explanatory. If you have an account to these email providers simply enter your username and password and press the corresponding log-in button. In the “Other” heading however, the things are a little bit more complicated:

  • Start by entering the URL of the website you need to log-in. The IsURLValid function at the adjacent cell will return TRUE if the URL you entered exists (the cell next to the URL will become either blue, or red – if the URL doesn’t exist).
  • Next, following the 7-step procedure that was described in the VBA code section, find the element IDs of the username and password text boxes, as well as the ID of the sign-in button. Enter these ID values in the corresponding cells of the worksheet.
  • Finally, enter your username and password and press the Other Log-In button.

With the latter procedure you can quickly test any website you wish to log-in. In the sample workbook for example I have included the IDs of the Facebook log-in webpage, just to demonstrate that the same approach can be used almost in every webpage that requires log-in.

Asterisks Format In The Cells

Finally, if you are wondered how the asterisks appear on the cells that contain usernames and passwords just right click on any of them and on the context menu that will appear select Format Cells → go to the Number tab → select the Custom category → look at the format type that is applied, which is this: ;;;**. Nice trick, don’t you think?



Demonstration video



The short demonstrates the result of the "WebsiteLogIn" macro, as well  as shows the 7-step procedure that was described above.




Downloads



Download

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

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



  • Note that the use of SendCommand is NOT a panacea! It’s probably just a hack that sometimes has to be used because Autodesk hasn't given a complete VBA object model. As Autodesk suggests, "you should never use this method to issue a command for which there is an ActiveX method available"; to add text for example use the AddText method.
  • This method is generally synchronous. However, if the command sent with this method requires any user interaction (such as picking a point on the screen) then this method will continue as soon as the user input begins (i.e. MTEXT, TEXT etc.).
  • Please bear in mind that the particular code was NOT tested for every AutoCAD command that can be executed via command line and probably will NOT work for every command. However, tests for some of the most “popular” ones showed that it works without problem (check the demonstration video for example). 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.