Tag Archives: VisualBasic

Customizing the editor for coding

A lot of people hate software just because they do not like the look and feel of the user interface. Surprisingly even the knowledgeable “geeks” have strong preferences on the GUI (Graphical User Interface). This often takes away the focus from functionality and versatility of programs and impact negatively on high quality products.

The good news is that most programs have customizing options available for both GUIs and back-end. However, very few users are aware of these hidden gems. In this article we will discuss the customizability of the open source free software Notepad++ (“Notepad Plus Plus”).

Notepad++

Notepad++ is highly customizable and flexible enough to work with almost all programming languages. As soon as you save your file as a .php, .html, .py, .cpp, etc it will pick up all the standard syntax related to each language. In addition you can use this program as a standard text editor.

The initial setup is simple. Download the program from here and follow the on screen instructions.

Select "Themes" during install.
Select Themes during install.
Make sure you select themes (check mark it). If you do not have much experience with the program, I would recommend you select exactly what I have above. I do not have an image of the next window, but if you are unsure, do not select anything on the next window. Just move on as next, next, etc.

Once the installation is completed, go to settings on the top ribbon. Open Style Configurator…

Settings drop down menu.
Settings drop down menu.

In the Style Configurator window you have variety of options. You can have fun with this, but for now, I would select a per-configured set from Select theme drop down.

Styles selection drop down list.
Styles selection drop down list.
Personally I like black background because I find it better on my eyes. I spend hours at a time writing code and most people I talked to also found back background to be very pleasant (as opposed to white).
PHP syntax coloured (automatic) on black background.
PHP syntax coloured (automatic) on black background.
It is my personal preference. The beauty of customization is you can choose a theme and/or color scheme to get your ideal working environment. Since you can always reset these, I entourage you to experiment with it.

HTML-Kit (free version)

Don’t get fooled by the name! This is one of the best free code editors out there. It started as a basic HTML editor and then transformed into what it is today. Like Notepad++, it also have support for almost all languages. However, this is big HOWEVER, it is not open source nor it comes with all the customization features like Notepad++. You cannot change the themes without having to buy their “premium” version. While I have used this extensively in the past, I am tired of limitations in free version. Please try Notepad++ first and if you don’t find it useful, the second best option would be HTML-Kit free version. Why pay for features that already included in FREE Notepad++?

Other Editors

All general text editing software that allow saving files in different formats (.php, .html, etc) can be used for programming. If you are new to coding please find a software that have built in syntax highlighter (eliminate Microsoft Notepad). This will make those first few steps into coding fun.

Extract data from rows for email

If you are a teacher or a professor, you probably have several students in your class. Let’s say you would like to provide them with an update on their averages after every single quiz and exam they take. But you hate to email each and everyone of them because it consumes a lot of time to copy data from your Excel file of student records to individual emails. This is where the extract data from row by row using loop iteration comes to save you.

At the beginning of the year you may have created a spread sheet with student names, email address, averages for each and every exam and may be even student numbers. The following example is created to show such Excel file (exception to student ID, which you can enter in a new column).

Sheet for the loop iteration in VisualBasic.
Sheet for the loop iteration in VisualBasic.

VB Script

If you have not work with VB Scripts before, please read the introduction article, here. I assume assume you know the fundamentals of Excel Macros. You may copy and paste the following VB Script to your Excel file OR download the file from here. (I will upload the file tonight!).

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 150 'Looping the rows 2-150, extracting data one row at a time'
'       Extract the email address found in every row  (limit defined in the for loop above) at cell number 2
        Email = Cells(r, 2)
        
'       Message subject
        Subj = "Your final exam grade are up!"

'       Compose the message
        Msg = ""
        Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that we have completed submitting your exam averages as follows..." & vbCrLf
        Msg = Msg & "Quiz No. 1: "
        Msg = Msg & Cells(r, 3).Text & "," & vbCrLf
        Msg = Msg & "Quiz No. 2: "
        Msg = Msg & Cells(r, 4).Text & "," & vbCrLf
        Msg = Msg & "Quiz No. 3: "
        Msg = Msg & Cells(r, 5).Text & "," & vbCrLf
        Msg = Msg & "Overall Average: "
        Msg = Msg & Cells(r, 6).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "Sanuja Senanayake" & vbCrLf
        Msg = Msg & "Instructor"
        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
'       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
    Next r
End Sub

There are several impotent codes in this program. The first line is a command to open shell32.dll which is the Windows default email. Depend on which program is set as your default, it may open any email program such as Outlook, LiveMail, Thunderbird, etc. Please be advised that once this program is “RUN”, it will automatically start creating email temples and send them. You cannot stop and check unless you force terminate it or it comes across an error.

The Sub SendEMail() is calling in the function to automate email processing. A function is like a box which contains data specific to an operation. In this perticular example, the box starts at Sub SendEMail() at line 5 and it ends at End Sub at line 47. Between the lines 5 and 47, we excite several commands in one of which is a for loop. The for loop is a very impotent iteration command. But I don’t want to go into detail since it is widely used in several different programming languages. What it does is to repeat few operations over and over for the given set window. The window in this case is defined as r = 2 To 150, which is rows 2 to 150 (in other words, if each row is for a new student, 148 students can be covered). We started iteration from row 2, because the first row is used for titles; Name, Email, Quiz# and Overall.

But what if you don’t have 148 students? No problem. Any VB Script like this can be modified to serve specific needs. All you have to do is to change the iteration window to any value you like in the for loop. Let’s say you only have 20 students, rewrite the for loop as For r = 2 To 22. Now you have few students who decided to join the last later than others. So, you decided to enter their names at the very end of the file. If you wan to send an email with their grades, but you do not want to send the same email to all the students, you can change the upper limit of the for loop to avoid unnecessary emails. For example, For r = 151 To 162 will only extract data from rows 151 to 162 and compose emails accordingly.

Next, you have five different quizzes, a midterm and a final exam. To use this script, simply add more cell reader in the the for loop extracting the cell you want. It is as simple as adding Msg = Msg & Cells(r, XXX).Text & “,” & vbCrLf in the current for loop , where XXX is the cell number.

There are many other modifications you can do with this type of script. However, I am not going to spoon feed you on flexibility of VB. Instead, I encourage you to come up with your own modifications. The script is free to copy, modify, reuse and republish with no credit to sanuja.com. To give you an idea on what you can do with it, try these;

  • change the plain text to bold, italicized, etc texts in the email body
  • replace the for loop with a while loop to prevent run on program
  • create an if statement in the for/while loop to prevent invalid email address errors (exceptions)
  • check for multiple email addresses on the machine and generate a pop-up before run the loop to give user the choice
  • personalized the email by only taking the first word in the name column (avoiding the full name)
  • …and many more creative ideas!

Sample Email

Here is an example of this script generated email.

Email Format
Email Format

If you have questions on customization and you don’t understand my comments within the script, fire me an email and I will do my best to help you out. You can also read more Visual Basic articles here.

Visual Basic Script using the Interface

This is the second article a on going series of VB scripting guide. If you are unfamiliar with programming in Microsoft Excel, don’t be afraid, start your journey into VisualBasic with basics of VB explained here.

Macro recorder is a great for those who are new to the coding in general. A user can utilize the graphical front end of Excel to generate Modules, which can be modified using conventional VBA Editor.

Find the Developer section.

Choose “Record Macro”
dev_options_mrc_rec
Click “OK” when the Record Macro Options window pop up.
excel_vb_record_pop
Once you are done, click “Stop Recording” on the Developer section.
excel_vb_record_stop
Now you can open the Macro your created by Alt + F11. It will open the VB Editor (MS Visual Basic for Applications. On the left hand side of the VB Editor, under VBAProject section, you should see a Modules folder. Under that folder you will be able to open your script for editing.
excel_vb_rec_edit

Above image (last one) is an example of what you would get once Excel has taken your input and converted into a VBScript. What I recorded for this example is the addition of two cells, C1 and C2 and printing it in A4. Please note the A3 cell select. It is not used in any operations of this script. It was inserted because I click on the A3 cell while the Macro Record is in operation. Wile the record is in operation, everything you select and do on the Excel will be added to the VBScript. This is why writing a script in it’s native form is more beneficial than to use the graphical front end interface. To write scripts in the native form, you must first learn the basis of VB programming. We will discuss this on a later article. Until then, happy coding!

Excel Visual Basic Scripts

A simple program like Microsoft Excel 2010 can be used as a powerful tool by manipulating the software. Microsoft has published several “how to” guides such as this one Getting Started with VBA in Excel 2010. They are very good articles, and I am not trying to beat them. After all, they wrote the software anyway. I just want to introduce Excel as a powerful programing tool for home users Continue reading Excel Visual Basic Scripts