Best way to quantify time spent coding VBA?

[mykulpasskwa]

I'm working a very big VBA project from scratch and I'm supposed to present a demo to my boss on Monday. This week I've been working pretty much 4-5 hours a day and I'm barely an 1/18th of the way through if not less. I'm sure there are things I'm not accounting for.

If I were a say freelance coder and I wanted to figure out the best way to express to a client how much I can get done and how fast, is there a reasonable way to articulate that? For instance, is there a way to see how many lines are written for a project and I could figure out how many hours so that I could say "I can write 100 lines of code per hour and your project requires 1000 lines of code so i can finish it in 10 hours?"

My boss isn't going to ream me for not having much completed and I'm not going to be a freelance coder. I just want to figure out if theres a way to get that information in case those things ever do happen. Thanks for any input or ideas!

 

[VBE313]

I was in a similar situation, I’m not sure your exact situation, but heres mine. I joined a company and no one had any idea how to automate data. My company spends millions and millions a year just for data entry people. All the “C” suite executives and upper level management care about is their profit and loss statement. You need to find a way to automate a process that reduces your company’s overhead. For my example, I figured out a way to automate a process that Program Managers were billing 50 hours a week in OT total on average. I automated this process that took them hours and got it down to minutes. You really need to quantify the hours reduced which in the long run reduces overhead, costs, and time spent on tedious tasks. It is really hard to prove it to your boss unless you figure out the process and quantify how long people spend entering data, how much faster you can automate the process and how much money you can potentially save the company.

 

[NdNoviceHlp]

"is there a way to see how many lines are written for a project and I could figure out how many hours so that I could say "I can write 100 lines of code per hour and your project requires 1000 lines of code so i can finish it in 10 hours?" U may spend minutes or hours on 1 line of code. It seemed to me that U actually want to be able to quantify how much times is spent coding. Seemed interesting. If U equate accessing the VBE with coding then the following may be of interest. I don't really like it's recursion but maybe it will be useful as a starting point. Find 2 open cells (sheet1 A1 & A2 in this example) and adjust the Call to the function to suit. HTH. Dave
This workbook code..

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "AutoSaveMacro"
End Sub

module code...

Public Sub AutoSaveMacro()
Application.OnTime Now + TimeValue("00:00:10"), "AutoSaveMacro"
Call TimeMacro(Sheets("sheet1").Range("A" & 1), Sheets("sheet1").Range("A" & 2))
End Sub

Public Function TimeMacro(Arng As Range, Brng As Range)
'Saves record of VBE open
'Call TimeMacro(Sheets("sheet1").Range("A" & 1), Sheets("sheet1").Range("A" & 3))
Dim D1 As Date, D2 As Date, D3 As Date
If Application.VBE.MainWindow.Visible = True Then
D1 = Now
If Arng <> vbNullString Then
D2 = Brng
D3 = D2 + (D1 - Arng)
Brng = Format(D3, "Hh.Nn.ss")
Else
Arng = D1
D3 = D1
Brng = vbNullString
End If
Arng = D1
End If
End Function

To operate, insert the code, save then close the wb. Then re-open the wb. It will start recording the total amount of time that the VBE is open in 10 sec intervals (cumulative across all sessions)

 

[mykulpasskwa]

I was in a similar situation, I’m not sure your exact situation, but heres mine. I joined a company and no one had any idea how to automate data. My company spends millions and millions a year just for data entry people. All the “C” suite executives and upper level management care about is their profit and loss statement. You need to find a way to automate a process that reduces your company’s overhead. For my example, I figured out a way to automate a process that Program Managers were billing 50 hours a week in OT total on average. I automated this process that took them hours and got it down to minutes. You really need to quantify the hours reduced which in the long run reduces overhead, costs, and time spent on tedious tasks. It is really hard to prove it to your boss unless you figure out the process and quantify how long people spend entering data, how much faster you can automate the process and how much money you can potentially save the company.

Yeah figuring out the reduction is the tricky part. On one hand I can replicate the "old" process and how long that took and show how much faster the automation worked, but then I'd be putting aside other work. It's great advice; I just need to find a practical way to do it. If I do it might get me a better position here ?‍

 

[mykulpasskwa]

"is there a way to see how many lines are written for a project and I could figure out how many hours so that I could say "I can write 100 lines of code per hour and your project requires 1000 lines of code so i can finish it in 10 hours?" U may spend minutes or hours on 1 line of code. It seemed to me that U actually want to be able to quantify how much times is spent coding. Seemed interesting. If U equate accessing the VBE with coding then the following may be of interest. I don't really like it's recursion but maybe it will be useful as a starting point. Find 2 open cells (sheet1 A1 & A2 in this example) and adjust the Call to the function to suit. HTH. Dave....

So this is very interesting and I'm very impressed with it. Even if its not exactly what I'm looking for it will give me at least some approximation of how long a project is taking. I really really really appreciate it!

 

[VBE313]

Yeah figuring out the reduction is the tricky part. On one hand I can replicate the "old" process and how long that took and show how much faster the automation worked, but then I'd be putting aside other work. It's great advice; I just need to find a practical way to do it. If I do it might get me a better position here ?‍

It also isn't easy when your co-workers make it very difficult for you to understand the process! you need to find the "bottlenecks" of the process and solve them with VBA

 

[NdNoviceHlp]

You are welcome. Here's a bit of an improved code using only 1 blank cell to save your VBE access info. Dave
ps. I still don't like a sub running every 10 secs in the background.
Workbook open code...

Private Sub Workbook_Open()
Sheets("sheet1").Range("A" & 1) = vbNullString
Application.OnTime Now + TimeValue("00:00:10"), "AutoSaveMacro"
End Sub

Module code...

Public Arng As Variant
Public Sub AutoSaveMacro()
Application.OnTime Now + TimeValue("00:00:10"), "AutoSaveMacro"
Call TimeMacro(Sheets("sheet1").Range("A" & 1))
End Sub

Public Function TimeMacro(InputRng As Range)
'Saves record of VBE open
'Call TimeMacro(Sheets("sheet1").Range("A" & 1))
Dim D1 As Date, D2 As Date, D3 As Date
If Application.VBE.MainWindow.Visible = True Then
If InputRng <> vbNullString Then
D2 = InputRng
D3 = D2 + TimeValue("00:00:10")
InputRng = Format(D3, "Hh.Nn.ss")
Else
InputRng = Format(TimeValue("00:00:10"), "Hh.Nn.ss")
End If
End If
End Function

To operate, insert the code, save then close the wb. Then re-open the wb. It will start recording the total amount of time that the VBE is open in 10 sec intervals (cumulative across all sessions)

 

[NdNoviceHlp]

Whoops. Missed the edit timer. Replace the Workbook open code so time is cumulative. Dave

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "AutoSaveMacro"
End Sub

 

[mykulpasskwa]

Whoops. Missed the edit timer. Replace the Workbook open code so time is cumulative. Dave

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "AutoSaveMacro"
End Sub

Worked great. Thanks for the followup! Ive been using it everyday just to get an idea

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave