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.

Advertisements

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.

Few Toy Programs to Mesh Excel and Fortran together

Fortran DLL and Excel
Fortran 90 with Excel – gfortran example is one of the post that constantly gets lot of attention as measured by the top posts sidebar widget on this blog.

So thought about revisiting it and this post is to show some toy programs that shows how to link fortran program as a dll with excel.

I am using gfortran and the following simple fortran programs to create a DLL which is called from excel.

Continue reading

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

Slowing Down is the Key…..

slow down

Slowing down is the key to increased speed.

Past couple of months I was dabbing with fortran GUI and trying pgplot graphics library. I have produced gui’s in c, vb and then integrated them with fortran, but creating GUIs from fortran was new to me.

As the exploration began I took the fire aim adjust approach!! Dived deep into the tutorials and anything that I could lay my hands on.

Quickly from tutorials I graduated to actually creating my own little programs. This went on for a couple of months.

In the beginning I was sprinting as hard as possible. Learning, doing, getting stuck, reading and then doing again. The pace was fast.

But as I become comfortable, my approach shifted. I slowed.

I wrote a program and pondered how and what am I actually doing. This slowing down and pondering doubled my learning. It felt like I was learning at greater pace with this slowdown.

So the technique I want to advocate to anyone learning a new programming language, a new analysis tool or cad software, is to sprint in the first few weeks. Race and learn as much as you are able to handle. Dive deep and continue the pace as long as you are able to.

When exhaustion, sense of acheivement begins to creep in, slow down. Become deliberate in what you do? Question why and what you are doing?

I hope applying this method will help you as much as it has helped me.

What are your views, do let it out in the comments.

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"
Else
NTDomainUserName = strTemp
End If

End Function


' Test function

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

An Excel App for Your Brain!

Screen Shot 2013-04-29 at 10.24.23 PM
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.

So here’s the result. Click to download the excel file and play

What I learnt from the exercise?
The application.ontime function in excel 2007 is broken. Was not able to stop it. So had to introduce a new Boolean variable to stop the play.

Working with shapes was a new thing for me. I have always used cells or a user form for working in excel VBA, but for this I wanted to see how to use shapes.

Not bad for a total of 2+ hours of effort and it refreshed my dormant vba skills!!

Lot can be improved in the app, but hey I have real work to do. If you want, you are free to refine the game further. or look at the code here.