Sunday, 20 September 2015

Road Transport Emissions Evolution In Urban Areas; The Case Of Thessaloniki, Greece


First of all, it seems that in previous months I couldn’t fulfill my own promises regarding this blog. I have an extremely heavy workload the last few months and it seems that until I present my thesis, I will not be able to update this blog regularly. So, dear blog readers please be patient!

Anyway, in the paragraphs that follow you will find a text version of my latest poster, which was presented on the 14th International Conference on Environmental Science and Technology. The conference took place in Rhodes (Greece) during the first week of September. The poster is actually a summary of the corresponding paper. If you are interested about road transport emissions you might find it interesting! 



  • The current study examines the evolution of road transport emissions during the years 2012 – 2014 in Thessaloniki, the second largest city of Greece. The study focuses on the calculation at micro/link level of CO, NOx and VOC emissions.
  • The developed methodology was based on the combination of PTV VISUM and COPERT Micro models, along with measured traffic data from inductive loop detectors, cameras and radars installed at 37 locations across the city.
  • Results indicated the local pollution hot-spots, as well as the local and seasonal variation of pollutants. In general, there is a substantial drop on average annual emissions between 2012 and 2013, whereas a constant trend is observed between 2013 and 2014.


  • According to the latest census (2011), Thessaloniki is the second largest city in Greece, both in population and area.
  • Based on the latest transportation study conducted between 2010 and 2013, the average daily private vehicle traffic on the main roads of the city reaches 1.300.000 vehicle-trips, while the morning peak corresponds to 14% of the daily total. 
  • The main streets in the central area of the city serve daily volumes of through traffic that reach 45% of traffic volumes recorded at peak periods (morning travel to work and afternoon travel back from work to home).

Materials & Methods

Flow Chart Of The Methodology

Flow chart of the methodology.

Traffic Modelling

  • The transportation model for Thessaloniki has been developed with the PTV VISUM software. The network consists of 137.938 directed links, 47.838 nodes and 339 traffic analysis zones connected to physical nodes of the road network via 3.508 connectors. 
  • The demand side is comprised by 24 hourly Origin-Destination (OD) matrices and the travel demand for a typical weekday is within the range of 1.298.745 vehicle trips. 
  • The obtained OD matrices are corrected using the hourly volume data measured by inductive loop detectors, cameras and radars installed at 37 locations across the city. The OD matrix correction is performed with a fuzzy-set based matrix correction procedure.

Emissions Modelling

  • The emissions calculations were performed with COPERT Micro, a specially developed version of COPERT 4 for urban areas. It is a bottom-up model, thus, it can calculate the emissions from a single traffic link up to an entire city, focusing primarily on hot exhaust emissions. 
  • The equations below summarize the main methodology that is applied by COPERT Micro:
            - EFi(V)= fEF (V) : emission factor as a function of average vehicle speed
            - Emissionsi, j = Lj × Nj × Pvehcile category, j × EFi(V) : emissions due to a single traffic link
            - Emissionsi, area = ∑Emissionsi, j : emissions from the entire area

Results & Conclusions


Daily CO Hot Emissions In Thessaloniki For Years 2012 & 2014

Daily CO hot emissions (kg) on a 100 x 100 (500 x 500 m2) grid for a typical weekday of summer 2012 (left) and 2014 (right) in metropolitan area of Thessaloniki.

Seasonal Hourly NOx Hot Emissions In Thessaloniki For 2012

Seasonal Hourly NOx Hot Emissions In Thessaloniki For 2014

Hourly NOx hot emissions (kg) for a typical weekday of each season of 2012 (up) and 2014 (down) in metropolitan area of Thessaloniki.

Seasonal Daily VOC Hot Emissions In Thessaloniki For Years 2012 To 2014

Average Daily VOC Hot Emissions In Thessaloniki Per Year (2012 - 2014)

Daily VOC hot emissions (t) for a typical weekday of each season (up) and each year (down) of years 2012 – 2014 in metropolitan area of Thessaloniki.


  • The results at link level revealed the local pollution hot-spots and the high-emission links, which usually lie along the main urban highways of the city. 
  • The seasonal variation of pollutants is mainly caused by the different transport activity pattern in each season. 
  • There is a significant drop on average annual emissions between 2012 and 2013, while a constant trend is observed between 2013 and 2014. This could be allocated to the reduced transport activity throughout the city, mainly affected by the economic crisis.

Thursday, 30 April 2015

CodeEval 3 – Reverse And Add

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 to 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.

Reverse And Add – challenge description

Choose a number, reverse its digits and add it to the original. If the sum is not a palindrome (which means, it is not the same number from left to right and right to left), repeat this procedure.


195 (initial number) + 591 (reverse of initial number) = 786

786 + 687 = 1473

1473 + 3741 = 5214

5214 + 4125 = 9339 (palindrome)

In this particular case, the palindrome 9339 appeared after the 4th addition. This method leads to palindromes in a few step for almost all of the integers. But there are interesting exceptions. 196 is the first number for which no palindrome has been found. It is not proven though, that there is no such a palindrome.

Input sample

Your program should accept as its first argument a path to a file name. Each line in this file is one test case. Each test case will contain an integer n < 10,000. Assume each test case will always have an answer and that it is computable with less than 100 iterations (additions).

Output sample

For each line of input, generate a line of output which is the number of iterations (additions) to compute the palindrome and the resulting palindrome. (they should be on one line and separated by a single space character). Example:

4 9339

The particular challenge has a relatively low success rate (66.6% - 30/04/2015), and its level of difficulty is medium. More info you can find here.


Apart from the main code, the solution incorporates two helping methods for reversing the input number ("ReverseNumber") and for checking if a number is a palindrome ("IsPalindrome").

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

The code below solves the CodeEval challenge -  Reverse And Add.

Written by:    Christos Samaras
Date:          02/07/2014

namespace ReverseAndAdd
    class Program
        static void Main(string[] args)
                using (StreamReader reader = File.OpenText(args[0]))            
                while (!reader.EndOfStream)
                    string line = reader.ReadLine();
                    if (line != null)
                        int cnt = 0;

                        while (IsPalindrome(line) == false)
                            int reversedNumber = int.Parse(ReverseNumber(line));
                            line = (int.Parse(line) + reversedNumber).ToString();
                        Console.WriteLine(cnt + " " + line);
        public static string ReverseNumber(string number)
            char[] charArray = number.ToCharArray();
            return new string(charArray);

        public static bool IsPalindrome(string num)
            if (num == ReverseNumber(num))
                return true;
                return false;


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

CodeEval 3 – Reverse And Add - Score

Sunday, 19 April 2015

Automatically Adjust Secondary Y Axis Scale Through VBA


Some days ago, while I was checking the discussions on an Excel-related group on LinkedIn, I bumped into an interesting question about charts. The question was how to automatically set the secondary Y axis scale via code, so that both primary and secondary axes share the same number of gridlines”.

I thought this question was interesting for two reasons: first, it’s quite common to include a secondary Y axis on a chart, just to display another series that have different scale than the first one. In COPERT Micro tool for example, I have many charts that show emissions (primary Y) and number of vehicles (secondary Y) as a function of hour of the day (X axis). Second, the question reminded me a similar need that I had some years ago. So, I found the code that I had written then (2012) and I made some minor changes in order to make it more generic, and, here it is!

The manual way

By the way, it’s not hard to adjust the scale of secondary Y axis manually. Just follow the instructions that follow (for Excel 2013).

Step 1: Set a data series to be plotted on secondary axis:
  1. Select a series on the chart by right clicking on it.
  2. On the pop up menu select Format Data Series.
  3. On the Format Data Series menu that will appear select the Series Options tab.
  4. Select the plot on secondary axis radio button.

Format Data Series Menu Plot Series On Secondary Axis

Step 2: Adjust the scale of the secondary Y axis:
  1. Select the secondary Y axis by right clicking on it.
  2. On the pop up menu select Format Axis.
  3. On the Format Axis menu that will appear select the Axis Options tab.
  4. Finally, set the desired values on Minimum/Maximum Bounds and on Major Unit text boxes.

Format Secondary Y Axis Set Secondary Y Axis Scale

In the last step you might need to make some (repeating) tests on the values that you will enter in the 3 text boxes (especially in the Major Unit text box) in order to achieve that both primary and secondary axes will share the same number of gridlines.

VBA code

And here is the “easy way”; no need to test the values on the text boxes, no worries about how the axes will look uniformly. It’s a short macro that can be customized according to your needs.

Option Explicit

Sub AdjustSecondaryYAxisScale()
    'Automatically adjusts the scale of the secondary Y axis, so that both
    'primary and secondary Y axes share the same number of (major) gridlines.
    'Written By:    Christos Samaras
    'Date:          14/09/2012
    'Last Update:   18/04/2015
    'Declaring the necessary variables.
    Dim ch      As Chart
    Dim Ymin    As Double
    Dim Ymax    As Double
    Dim Yscale  As Double
    Dim Ylines  As Integer
    Dim sYmin   As Double
    Dim sYmax   As Double
    Dim sYscale As Double
    'Set the ch variable to a specific chart on sheet 1 (in this example).
    'If you need to set the ch variable to active chart you can use the next line:
    'Set ch = ActiveChart
    Set ch = Sheet1.ChartObjects(1).Chart
    'A quick test if the ch variable is not empty.
    On Error Resume Next
    If ch Is Nothing Then
        MsgBox "The chart wasn't set!", vbCritical, "Empty Chart"
        Exit Sub
    End If
    On Error GoTo 0
    'Set the minimum/maximum bound and the major unit to Auto for both primary and secondary axes.
    'For the primary Y axis this is NOT always necessary, so the corresponding 3 lines can be deleted/commented.
    With ch
        .Axes(xlValue).MinimumScaleIsAuto = True
        .Axes(xlValue).MaximumScaleIsAuto = True
        .Axes(xlValue).MajorUnitIsAuto = True
        .Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MajorUnitIsAuto = True
    End With
    'Get the mininmum bound of the primary Y axis.
    Ymin = ch.Axes(xlValue).MinimumScale
    'Get the maximum bound of the primary Y axis.
    Ymax = ch.Axes(xlValue).MaximumScale
    'Get the major unit of the primary Y axis.
    Yscale = ch.Axes(xlValue).MajorUnit
    'Calculate the number of major gridlines.
    Ylines = Round((Ymax - Ymin) / Yscale)
    'Get the mininmum bound of the secondary Y axis.
    sYmin = ch.Axes(xlValue, xlSecondary).MinimumScale
    'Get the maximum bound of the secondary Y axis.
    sYmax = ch.Axes(xlValue, xlSecondary).MaximumScale
    'Note that you can easily set the minimum/maximum bound of the secondary Y axis to any value you like.
    'In other words, you can make your secondary Y axis look exactly as you want.
    'For example if you uncomment the next 2 lines, the first gridline of the secondary Y axis will start at 0 and
    'the last one will end at the value of 30. The number of gridlines will be automatically calculated/adjusted.
    'sYmin = 0
    'sYmax = 30
    'Calculate the new major unit of the secondary Y axis.
    sYscale = Round((sYmax - sYmin) / Ylines)
    'Calculate the new maximum bound of the secondary Y axis.
    sYmax = sYmin + Ylines * sYscale
    'Set the minimum/maximum bound and the major unit of the secondary Y axis to their new values.
    With ch.Axes(xlValue, xlSecondary)
        .MinimumScale = sYmin
        .MaximumScale = sYmax
        .MajorUnit = sYscale
    End With
    'Release the chart object.
    Set ch = Nothing
End Sub

In the workbook that you will find in the downloads section below you can try the above code by experimenting on a chart that I created using random numbers. Just see how nice the scale of the secondary Y axis "follows" the scale of the primary Y axis.



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

Monday, 23 March 2015

Email Engineering, Comments Policy, Blog Related Stuff & My Free Time


I know that the title might look strange to most of you, nevertheless, in this post I will try to write about a few things that were spinning around my head for a long time, but, until now, I couldn’t find the time to write about them. The topics that are analyzed below are:

  1. “Email engineering” and new email policy
  2. New comments policy
  3. Blog-related stuff
  4. My free time

So, as you might have already guessed, this post differs from the typical engineering/programming subjects that are being published in this blog. It’s a long and a little bit personal post, so, if you don’t want to read it all, you can only read the key points at each section. However, if you like this blog, I think that you should read the entire post; you might find it interesting.

1. “Email engineering” and new email policy

Email Engineering

The term “email engineering” is an inside joke that my colleagues and I use to express the time that someone spends with a view to send – usually quite long – emails. A typical dialog at my work would be like this:

-    Me: Hey, what’s going on? You look a little bit upset.
-    Colleague: Well, I spent all the morning with “email engineering, ” and now I have to speed-up to finish the presentation on time.

In other words, the term “email engineering” expresses the “unproductive” or “counter-intuitive” time that is wasted for sending (long) emails. Unfortunately, during the last summer, I realized that I do a lot of “email engineering” due to this blog. On January, I counted the number of emails that I received the last three years, and then I estimated the time that I spent to respond to these emails. The picture below shows the results.

Email Requests Per Year

So, only last year I received 229 email requests! That means that I wrote at least 300 emails since in many cases one email was not enough to solve the problem. By making the quite conservative assumption of 1 email request = 10 minutes, I ended up with a total time of about 38 hours (minimum). Wow! I was shocked when I saw this number! I spent an entire working week by just writing emails! That can’t be true!!!

The above helped me to realize that I have to change my email policy. Until now, I respond to every email that I receive, although my answer sometimes might take a few days/weeks to be sent. This will be tough to write, but, from now on I will no longer reply to all the email requests that I receive. I encourage you to visit the new FAQ page to read the requirements for getting a response from me. I like to receive feedback from all of you. However, from now on, if your emails don’t follow the rules, they will just be ignored.

Key points on “email engineering” and new email policy:
Fact 1: I enjoy to communicate with all of you.
Fact 2: Honestly, I wish I could solve all the engineering/programming problems that you send me.
Fact 3: 38+ hours for answering emails is way too much for me!
Result: New stricter email policy that will reduce the “email engineering” time.

2. New comments policy

Comments Policy

About two years ago, on the last major design update of this blog, I switched the commenting system from built-in Blogger comments to Disqus platform. With Disqus I didn’t follow any particular policy for comments; I used to approve every comment, except for spam ones. However, last summer, after having some indications of increasing “email engineering” time, I silently changed the comments policy. So, the comments for every new post will stay open for only 60 days AFTER the post-publication.

Why I did that? Well, I adopted this policy because I was receiving similar comments again and again. Some people don’t even read the entire post, and they just ask questions on the comments; issues that have been already answered in the post. Moreover, in the most popular posts, a lot of people keep asking questions that were answered a few comments below. I decided that this should stop!

Key points on new comments policy:
Fact 1: People don’t even read the posts and just asking questions.
Fact 2: Repeated comments especially in popular and old posts.
Result: Comments will be open for only 60 days after post-publication.

3. Blog-related stuff

Blog Related Stuff

a. Post frequency
It’s not a secret that from August 2014 and on I don’t write as frequently as I used to. The previous years (until August 2014) I usually published 3 – 4 posts per month. The last few months, however, the frequency has been dropped to 1 – 2 posts per month; September (2014) was the only month in the last – almost –4 years that I am running this blog, in which I didn’t write anything! Terrible!

In the upcoming months, I intend to restore the post frequency to 3 – 4 posts per month. Moreover, I will probably try to write longer posts, which will include more details about the subject; the previous post that I published is a good example of what I mean by the term “long post.”

b. Post subjects
In general, I always try to keep a balance between engineering and programming on this blog. I will continue this “subject splitting” approach, but, on “engineering side”, I will try to write more frequently (I slightly neglected to write about engineering issues at 2014), and, on “programming side”, I will probably try to move from VB 6.0 / VBA to other languages (C# is a good example). 

c. Updating old subjects/codes
This is a real pain in the neck for me! The blog is constantly on a never-ending updating process! This means that I continue to “renovate” older posts quite frequently. This is the reason why I would strongly recommend you to follow this blog on social media; I share there all the critical updates on older posts.

d. Blog interface
During the last three months I spent some time in order to enhance my HTML, CSS and JavaScript skills. I tried to implement some of the new skills that I learned to the blog interface (see for example how the navigation bar stays at the top of the page when you scroll towards the bottom). In the upcoming months, it’s quite possible that I will try to apply other interface enhancements too, to make the blog design more user-friendly.

e. Contests
I am thinking to organize some contests in the next months, but, I am not going to reveal more at the moment. Just stay tuned and be prepared for some surprises!

Key points on blog-related stuff:
Promise 1: Post frequency will be increased.
Promise 2: Distinction between engineering and programming subjects will remain the same; improvements on both will be implemented.
Promise 3: Continue updating older posts.
Promise 4: More user-friendly and less boring blog interface.
Promise 5: Contests will be organized.

4. My free time

Free Time

All the above issues share a common factor: my On time. Unfortunately, as the years are passing by, my obligations increase, and, consequently, I have less spare time available. In weekdays, I spend half of my day at the university (10 hours work + 1 hour for going back and forth to university). But, it’s not only the everyday work: the last 1.5 years I have undertaken several programming and engineering projects, on which I spend a significant amount of time (especially during the weekends). Moreover, by the end of this year, or at least by the first months of 2016, I plan to present my Ph.D. thesis, so this means that I will spend even more time at the university!

Having the above time constraints in mind, as well as other personal obligations in top priority, I have no other choice but to move the blog related stuff down to the list of my priorities. Sorry, but 38+ hours (= time spent on “email engineering” during 2014) is enough time for me to write about 5 – 10 new posts that will probably help much more people compared to the people in which I send the emails.

Another thing that I noticed is that “email engineering” doesn’t improve my programming skills. A lot of people keep asking for variations on existing code snippets. Well, I always try to publish detailed, well-commented code snippets, so that anyone who has some fundamental programming background can adapt to his/her own needs. However, I see that this is not always the case and I keep getting similar questions again and again. Sorry, but answering to similar issues is quite counter-intuitive for me! Last but not least, this blog is not a Q & A forum! I wish I had the time to answer all the questions that I receive, but, unfortunately, I don’t.

Key points in my free time:
Fact 1: My free time shrinks due to the increasing obligations that I have (university, other projects, Ph.D. thesis, etc.).
Fact 2: Increasing amount of emails, keep asking similar questions.
Result 1: Blog-related stuff went down on the list of my priorities.
Result 2: Stricter email and comments policy in order to keep this blog running regularly (publishing at least three posts per month).


In this long text, I tried to explain the reasons why I decided to apply stricter policies regarding email requests and comments. I wish I could help all of you, but, unfortunately, I can’t. The only thing that I can promise to all of you is that I will do my best in order to keep this blog running. Unfortunately, time flies and we can do nothing about it.

Tuesday, 10 March 2015

5 Common Inefficiencies That Affect HVAC System’s Efficiency


This (long) post initiated by the necessity to answer the following question: How much the HVAC system can affect the building’s overall efficiency? Well, the short answer is that for office buildings, the HVAC system contributes about 25 – 30% on the total building energy use! It is more for laboratories, since much more energy is demanded for fume hoods and lab equipment. Moreover, about 25% of an office building’s energy is used for lighting and the balance for plug loads (office equipment, desk comfort heaters, task lighting, etc.). However, the long answer is that some inefficiencies in the HVAC system can increase its contribution to the total building energy use to even higher values. Common – and relatively easy to correct – causes of HVAC inefficiencies include:

  1. Lack of adequate building pressure during air conditioning periods.
  2. Heating and/or air conditioning equipment running at the wrong times.
  3. Poor documentation of how to properly operate the building.
  4. Inefficient lighting demand management.
  5. Inefficient plug load demand management.

These 5 common inefficiencies are analyzed below. Emphasis is given – apart from the causes – on the possible workarounds. Most of the analysis focuses on office buildings, although the suggested solutions could be possibly applied to any type of building.

1. Building pressure

Building Pressure

A negative building pressure can pull excessive amounts of unconditioned air into the building, whereas an excessive positive pressure can push conditioned air out of the facility. Many designers are designing buildings that have slightly (about 25 Pa) positive pressure during the air conditioning season and about zero pressure during the heating season. Poor building pressure management can affect HVAC energy efficiency by even 40%. Typical causes of facility pressure problems include:

    ✗   Excessive building exhaust from HVAC system, uncontrolled bathroom exhausts, high ceiling atriums etc.
    ✗   Exhaust fans running when the HVAC system and associated fresh air makeup is operating in unoccupied mode. This also can cause excessive indoor humidity in humid climates.
    ✗   Clogged fresh air makeup filters.
    ✗   Fresh air makeup fans not working, dampers inoperable or on manual override.
    ✗   Door weather stripping in other than excellent operating condition.

Regarding humidity, it is suggested that for summer months – especially in quite humid climates – to slightly pressurize the facility in order to avoid humidity infiltration. In the cooler winter months, humidity is less likely to be a problem.

2. Equipment running at the wrong times

Equipment Running At The Wrong Times

Some common problems involving poor equipment time management include the following:

    ✗   The facility time clock or building automation system that sets the schedules for building equipment to accommodate occupied and unoccupied modes may be inappropriately set, or equipment may be on manual override or not connected to building controls. What should be checked:
    ✔   Chillers coming on too early in the morning or staying on too late in the evening.
    ✔   Building being air conditioned at night and on weekends and holidays.
    ✔   Fire system smoke control ventilation running.
    ✔   Lighting coming on too early in the morning and/or too late in the evening.
    ✔   Fresh air makeup on override.

    ✗   Variable speed fans set at manual override and at full or zero speed. In those cases fans operate either at full speed, or not at all. Action required:
    ✔   Returning the fans back to automatic enables the building automation system to control zone heating and cooling more effectively.

    ✗   Terminal air box fans set on manual. Similar to the above problem, many times, terminal air box fans may have been set on manual override for maintenance in the past and never returned back to automatic operation. Action required:
    ✔   Returning the fans back to “Auto”, so that the building automation system can better control the entire building.

    ✗   Building automation system’s original set points changed and being undocumented, causing perimeter terminal fan boxes and other equipment to run constantly. What should be checked:
    ✔   Tour the building to assess what is running during the unoccupied mode. Excessive lighting, ventilation fans, bathroom exhaust fans and desk task lights are all symptoms of building automation system scheduling problems.
    ✔   The facility should have all fans off in unoccupied mode, except maybe the fresh air make-up fan that is maintaining building pressure according to the building automation system set point. Fresh air make-up fans operating to maintain building pressure should not have top-run in excess of 40 percent speed. If a high operating speed is observed, either exhaust fans are running, or excessive leakage is occurring.

    ✗   Equipment (lighting included) may be connected to security light circuits and not being under control by the facility time clock or the building automation system. If major sections of the facility are founded lighted when the building automation system is calling for night lighting:
    ✔   Either a faulty switch, or lighting is not connected to a controlled circuit and could be the problem.

    ✗   Equipment (i.e. fresh air make-up heaters) running constantly. Many facilities use heaters in fresh air make-up ducting to warm incoming outside air above freezing in the winter. What should be checked:
    ✔   Look for faulty controls that may have these heaters running continuously.

    ✗   Cooling tower pan heater is “on” for the entire year. If the facility operates a chilled water air conditioning system, it will likely operate a cooling tower to discharge building heat. Cooling towers utilize an open tank or drip pan from which to circulate water. The drip pan almost always incorporates a high energy heater to prevent the water from freezing in winter. Action required:
    ✔   It is important to assure these heaters are not operating, except in freezing weather.

    ✗   Economizer systems are being bypassed. Buildings frequently operate either air economizers, or water side economizers (water chiller systems) to enable the air conditioning system to operate without the high horsepower compressor when outside conditions are cool. Since economizers only operate in early spring and late fall, these systems are at idle most of the year. Action required:
    ✔   Careful maintenance must be employed to assure economizers are in proper order and available to operate when the weather is appropriate for their operation. Approximately 5 to 20% of cooling costs can be saved through the effective use of economizers.

    ✗   Chilled water reset may be inoperative. Large buildings employing chilled water air conditioning systems frequently have a chilled water reset. This operation allows the building automation system to change the chilled water set point either up, or down from its normal temperature, and to optimize energy efficiency based on outside temperature, building humidity, outside humidity, and a host of other variables.
    ✔   If the building is equipped with the necessary systems, you certainly want these systems to be operational.

    ✗   Cooling tower operation may not be optimized. An array of operating variables may be controlled by your building automation system. For example, perhaps, the cooling tower fans operate in tandem, in sequence, or with variable speed drives. What should be checked:
    ✔   On very mild days, the cooling tower(s) may not need the fans operating at all to provide condenser cooling water at the proper temperature. If these options are not functional or are set to manual override, fans may be running needlessly and you may be wasting energy.

    ✗   Building sensors may not be calibrated or connected to controls. A large building may operate hundreds of sensors from which the building automation system makes decisions on how to efficiently meet operating set points. If the sensors are inoperable or disconnected, the building automation system cannot optimize operations.
    ✔   An easy solution is to simply review the building systems operating manual and audit the building automation system to see what data are being collected from sensors. Determine if the data makes sense.

3. HVAC documentation

HVAC Documentation

Ensure that you have the documentation of the system. Documentation is necessary to provide a permanent resource for operations and maintenance (O & M) personnel, for training, and to provide continuity in operations due to O & M staff changes. The facility needs two documents:

  1. Facility O & M manual: This manual covers various pieces of equipment and components of the facility, maintenance obligations and requirements, and some detail on the “system” common in this document. Ideally, the facilities manager should maintain a master of this document and then maintain an everyday copy with maintenance staff for daily use and for training.
  2. HVAC system’s manual: This document details the designers’ intent on the system and how to operate the building. It also details how the various pieces of equipment and systems interact with one another to function in the building.

If the facility lacks either of these documents, it may be prudent to obtain or establish them first. It will be impossible to operate the building effectively if documents are not on hand to clearly detail how the facility is to be operated, and how to make adjustments for the seasons and other transient conditions in the facility. Sometimes these documents are combined into one volume. In any case, some of the components of a good O & M manual include the following:

    ✔   Design intent and system limitations.
    ✔   Startup and shutdown procedures.
    ✔   Modes of control, set points and sequence of operations.
    ✔   Detail on building equipment and equipment maintenance, spares, replacement parts etc.
    ✔   Listing of contractors and manufacturer’s contact info.
    ✔   Technical details and instructions on the control system and interlock sequences.
    ✔   Equipment manual override and bypass strategies.
    ✔   Procedures to monitor, trend, troubleshoot and diagnose.
    ✔   Routine and preventative maintenance.
    ✔   Provisions and techniques for emergency shutdowns, interlocks, life safety info.
    ✔   Provisions for ongoing commissioning, functional testing, calibration and fault diagnosis.

4. Lighting demand

Lighting Level Measurement

Lighting adds a significant amount of heat into the building and must be removed in the summer by air conditioning. Thus, optimizing building lighting efficiency directly affects HVAC operation. Lighting energy management is designed to provide the appropriate amount of lighting at the time it is needed. The first step in lighting management is to acquire a light meter. An inexpensive light meter can be purchased at your local electronics store or and is quite cheap (about 15 €). Even experienced lighting engineers are reluctant to rely on their eyes to judge appropriate lighting levels.

Office areas in buildings are generally lit to about 500 – 550 lux from the ceiling with task lighting; this increases the light at the work surface to about 750 lux. Cafeterias are also lit to 450 – 550 lux, while hallways and loading docks are lit to about 250 – 350 lux. When auditing the facility with a light meter, use an average of several readings (both under ceiling lamps and in darker areas) to obtain an estimate. Note that windows on bright cloudy days render the highest light readings for windowed rooms. If the facility is illuminated significantly above these levels, it deserves further investigation to see if de-lamping is an option. You can easily remove lamps temporarily to see if this option is viable. In areas where you find de-lamping a permanent opportunity, remember to disconnect the lamp ballasts as these units draw some power too. Usually de-lamping opportunities manifest well in cafeterias, loading docks, and hallways. Verify that lighting schedules coincide with building occupancy to improve efficiency even more.

A night survey of lighting is invaluable. Sometimes construction problems have never been corrected. For example you may find large areas where lighting is not under the lighting control system. You may find excessive lighting wired to the night lighting circuit. Evening and night ornamental lighting should be eliminated. Consult with building security and only illuminate outdoors as necessary for security.

Lighting technology is changing constantly. If you are interested in purchasing more efficient lighting, opportunities abound. If your facility still operates with T12 fluorescent lamps, there is about a 1 – 3 year payback (at office usage levels) to recoup your investment by retrofitting the system with T8 lamps.

If you operate 32 watt T8 fluorescent lamps there are lower wattage versions available down to 25 watts per lamp with little loss in illumination. However, lower wattage T8 lamps may not work with rapid start ballasts or dimming devices. You must check compatibility with your existing fixtures before switching to these. Consult your lamp manufacturer or lamp distributor to assure your ballasts are compatible with the lamps you intend to purchase. If your facility is equipped with incandescent lamps (restrooms, exit signs, loading docks, etc.) that operate for the full day, re-lamping these compact fluorescents has a simple payback 2 – 6 months and significantly reduce the load on your air conditioning.

5. Plug load management

Plug Load Management

Similar to lighting, facility plug-in equipment also affects HVAC operation, as all the energy consumed eventually ends up as heat in the building that must be either managed or removed by the HVAC system. Controlling plug loads can be challenging, but two opportunities are easy to achieve:

  1. Eliminate computers operating at night. At a minimum, have these machines set to “hibernate” if unattended for extensive periods. Similarly, copy machines and desk printers should be set to “sleep” at night. Network servers can be programmed to reset computers even when the computer operator bypasses sleep modes. Contact your information technology person to see how to easily achieve these goals.
  2. Eliminating desk comfort heaters is challenging as some people really need the extra warmth, however, excessive use should be controlled. Desk heaters should only be authorized on a case by case basis, and the specific brand of the unit should be mandated by building management in order to eliminate the potential for a fire hazard. Desk heaters should be assessed to assure they do not bias building thermostats. A thermostat that is biased by localized heating (desk heaters, copiers, etc.) will destabilize office temperatures.


The post was based on material found on the following ASHRAE handbooks:
2008 - Fundamentals of HVAC Systems
2011 - HVAC Applications
2012 - HVAC Systems And Equipment

Read also

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

Monday, 23 February 2015

Human Comfort & HVAC System Operation


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 cold, dry day, the facility may need to operate warmer than a cold, damp day. If the 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 setpoint should be set 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 definite 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, the 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 flow in buildings is probably rare, but it can be a source of discomfort and complaints. Also, 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 airflow 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 effect while residents on the south and west side feel a heating effect. Discomfort will be greatest near ‘single pane’ windows, where radiant heating or cooling has the most significant 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 to reduce radiative heat flow – significantly reduce radiant heat transfer, as do drapes and blinds.

Radiant heating and cooling also relate 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 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 significantly reduced.

Biological toxins can be produced from live colonies of mold thriving due to excessive humidity in the occupied space and ductwork. Mold can also flourish in condensate drip pans that drain poorly. These conditions cannot be tolerated due to the potentially 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 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 accurately control a space are other causes of variable conditions within an area. Even when these issues are adequately covered, unevenness in building conditions may still occur from occupant activity such as office partitions (cubicles) and operate 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 individuals working in sedimentary jobs generally require a warmer building environment to be comfortable. Younger workers, people who are a bit overweight and those in active jobs usually are more comfortable in cooler conditions.


The post was based on material found in 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

5 Common Inefficiencies That Affect HVAC System’s Efficiency
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


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

    '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:
    '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
    '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
    '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
        '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
        '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.
        '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
    '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.
    '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 "", _
                    "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 "", _
                    "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 video demonstrates the result of the "WebsiteLogIn" macro, as well  as shows the 7-step procedure that was described above.



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