Word to PDF

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

Continue reading


Simple Code to Find Which Excel Row is Being Modified

Excel’s inbuilt functions are powerful. They can achieve a lot. Almost anything can be conquered with these functions.

Well almost……everything!!

Recently needed a function to know which row is being modified. As far as i know, there’s no way this can be achieved by using excel’s function. This is where little bit of vba helps.

Here’s the code that helped.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 3 Or Target.Column = 4) Then
        If Target.Row > 1 Then Cells(1, 6) = Target.Row
    End If
End Sub

In the above sheet change method, the code checks if any row has changed in the columns 3 and 4 and if something has changed beyond row 1 in these columns, the F1 cell is updated with the row number.

Simple and small code but very useful when one wants to know which row is getting modified in excel.

The information about the row being modified can be used to do lot of things using excel in built functions.

Now think where can one use this. Stay tuned I have a follow up post on the use.

Is There a Dinosaur Sitting on Your Excel File?

Dinosaur Sitting On your excel App
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.

75 Excel Speeding Tips
Its a long List, many repeated but worth a visit.

Optimize VBA
Excellent tips to optimize vba code. You should do this before you move onto anything.

How to speed up calculation and Improve performance of Excel and VBA
These are excellent tips and must visit to know more about the calculations steps followed by Excel.

Pareto Chart in Excel

Pareto Chart in Excel
Creating pareto chart in excel requires multiple steps as there is no direct method to accomplish this in excel. Here’s a vba a script that creates a pareto chart in excel.

How to use the script?
Select the data and run the ParetoChart sub. That’s it. The scripts always assumes the first row is headers. Don’t forget that.

And if you need Boxplot in excel, this is where you should go.

Here’s the code….

Continue reading

VBA Script to Add Auto Captions to Images In a Word document

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.

Sub CaptionPictures()
Dim objPic As InlineShape

For Each objPic In ActiveDocument.InlineShapes
Selection.InsertCaption Label:="Figure", TitleAutoText:="", Title:="", _
Position:=wdCaptionPositionBelow, ExcludeLabel:=0
Next objPic
End Sub

Getting User Id of The Person Currently LoggedIn [vba]

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
End If

End Function

' Test function

Sub test_Open()
UserId = NTDomainUserName
Sheets(1).Cells(1, 1) = UserId
End Sub

Plants Become Weeds when they…..

FortranDLL in visual studio

How to Create Fortran DLL in Visual Studio? – The simple way

Plants become weeds when they obstruct our plans or our tidy maps of the world. Fortran is also perceived in the same way. Although still a formidable programming language, many now treat it as weeds.

They encounter it and they go back to fortran 66 mindset. Fortran’s new capabilities are discounted and people look past the new features of the language to talk about the horrible “go to” mess!! They might want to look at this Myths Of Fortran post!

There’s so much legacy code in fortran, but no one wants to touch it. The easiest solution commonly used is to convert it into a dll or some library and use with new languages. This explains the popularity of the post how to create fortran dll in visual studio on this blog.

This brings me to today’s post. Here’s an even more convenient animated gif to explain the process of converting a fortran program to a dll in visual studio with intel fortran compiler!