Spring cleaning the desktop, found this old vba code that I must have mashed together sometime back.
Well I tested it today and it works, so posting it here for future use.
What it does, collects every email for the GAL in outlook…
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olGAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntries
Dim olMember As Outlook.AddressEntry
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olGAL = olNS.GetGlobalAddressList()
Set olEntry = olGAL.AddressEntries
On Error Resume Next
Open "d:\project\email\emails.csv" For Output As #1
Dim i As Long
For i = 1 To olEntry.Count
Set olMember = olEntry.Item(i)
If olMember.AddressEntryUserType = olExchangeUserAddressEntry Then
strName = olMember.Name
strAlias = olMember.GetExchangeUser.Alias
strAddress = olMember.GetExchangeUser.PrimarySmtpAddress
strPhone = olMember.GetExchangeUser.BusinessTelephoneNumber
strCity = olMember.GetExchangeUser.City
strCom = olMember.GetExchangeUser.CompanyName
strJobT = olMember.GetExchangeUser.JobTitle
strDepar = olMember.GetExchangeUser.Department
strOffLoc = olMember.GetExchangeUser.OfficeLocation
Print #1, strName & vbTab & " (" & strAlias & ") " & vbTab & strAddress & vbTab & strPhone & vbTab & strCity & vbTab & strCom & vbTab & strJobT & vbTab & strDepar & vbTab & strOffLoc
' for testing
' If (i = 200) Then
' GoTo 10
' End If
10 msgbox ('Done!!')
A week ago had a personal task to convert 40 or so word documents to pdf in standard and minimum size. Doing them by one at a time was felt such a waste, so recorded and edited a vba macro to do the same task in a jiffy.
Here’s the simple vba subroutine to do the conversion.
I am handing few big excel data files lately and these files are loaded with vlookups, sumifs,sumproduct and truckload of loopy VBA code.
Running the VBA code was pain, excel drags as if a dinosaur is sitting on it. The files were so muddled that even the excel in-built functions to convert a range of cells to number takes 5 mins.
Trying to optimise the vba application in the excel file, I did the usual Application.ScreenUpdating = False and Application.Calculation = xlManual in the VBA to speed up all macros. This helped but not entirely.
The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates and re-calculates each cell and resolves dependencies. Excel recalculate’s whenever an event occurs (for example a cell is changed, a VBA code calls for re calculation or anything else). For each change the entire calculation process is carried out, so the trick to gain major benefit came from looking at all the formulas and optimising them.
It’s not easy but there are many fantastic resources that can help. Here are some of my favourite ones.
Has this ever happened to you? You are writing a big image heavy test report or user document in Microsoft word. And as you get to the end of your job, you suddenly realise you forgot to caption your images in the document.
You get angry on yourself at the prospect of spending an extra hour to correct this oversight.
Well a similar thing happened recently. And when I thought about it, it had happened more than I want to reveal here. Since writing report isn’t a daily activity of my job, this thing reoccurs. After a hundred and odd page, I go back and add caption to the pictures that I forgot in the first place.
So this time, instead of fixing the captions, I spent the next hour experimenting, how to fix the mess and came up with the following VBA script to add auto captions to images in a word document.
Simple script. Posting it here in case if someone is in similar situation.
Dim objPic As InlineShape
For Each objPic In ActiveDocument.InlineShapes
Selection.InsertCaption Label:="Figure", TitleAutoText:="", Title:="", _
Working on a side project in VBA, a few weeks ago, I needed a function to get the userid of the person currently logged in to the system. Here’s the function that came handy! Posting it here for the general audience!
Option Compare Text
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long</code>
Public Function NTDomainUserName() As String
' This function gets the user id of the person currently logged in
Dim strBuffer As String * 255
Dim lngBufferLength As Long
Dim lngRet As Long
Dim strTemp As String
lngBufferLength = 255
strBuffer = String(255, 0)
lngRet = GetUserName(strBuffer, lngBufferLength)
strTemp = UCase(Left(strBuffer, lngBufferLength - 1))
If strTemp = "" Then
NTDomainUserName = "Not Known"
NTDomainUserName = strTemp
' Test function
UserId = NTDomainUserName
Sheets(1).Cells(1, 1) = UserId
While browsing through google play, this android app got my attention. Having discarded my android phone, I asked my friend N to download the app on his mobile.
Liked the game and then we two went for a walk to stretch our legs and give our eyes break from the computer screen.
It is in this walk that we challenged each other if we can create this simple game in our programming language of choice.
We figured by an hour we can complete the game. I wanted to do it in java but didn’t have the sdk installed on my system, so decided to use my old pal vba. Launched excel and began with my phone timer set to shout in an hour.
By stretching 5 mins more to an hour, I had the basic functionality ready in excel.
Then it took, another one hour to get this game little more polished. Though far from complete.
Wasting time and inefficiencies are the way to discovery. When Condé Nast’s editorial director Alexander Liberman was challenged on his inefficiencies in producing world-class magazines such The New Yorker, Vanity Fair, and Architectural Digest, he said it best: “I believe in waste. Waste is very important in creativity.”
Out of many things, inefficiencies have helped me learn java and pgplot at a faster rate.
Trying things, creating silly programs has produced so many wasteful programs, but each has sharpened the skills.
Now it’s a habit.
If you are into programming then you might be all too familiar with the first hello world program that we all start our learning with.
This was true for me till I got into aerospace, now my first program is always the program to calculate temperature at an altitude for international standard atmosphere.
This has become a habit. I do this for all the new programming languages that I learnt. Perl, python, VBA, vb, java, etc you name it and my first program in all this language was ISA.
The other day I was browsing my old desktop and found this long forgotten visual basic excel program that I wrote 6+ years back. It’s a small program that calculates and gives temperature and pressure at different altitude according to international standard atmosphere.