A Tech Blog by David Kandie

Tuesday, April 14, 2015

Force CAPS on enter with Excel VBA

Force CAPS on Entry

Hate having to put your Caps Lock on or using the Shift key at all? Suppose you have a column in your Excel spreadsheet for entering County names in Column C, or you must enter T or F for True/False, or M or F for Male/Female. 

You keep missing it and typing it in lower case. Easy enough! This code changes any text entry in the designated range to upper case.

Place the following code on the ThisWorksheet code window. The Range ("C:C") can be changed to any range. C:C tells it to CAP any entry in the entire C column. You can change the range to anything you like, such as C1:C15.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C:C")) Is Nothing Then
    Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub


Crack Sheet Protection Password in Excel with VBA

Password Cracker for Excel 

This routine provides a password to un-protect your worksheet. However, it may not give you the original password that was used. Open the workbook that has the protected sheet in it. Hit Alt+F11 to view the Visual Basic Editor. Hit Insert-Module and paste this code into the right-hand code window:
Sub PasswordBreaker()
 Dim i As Integer, j As Integer, k As Integer
 Dim l As Integer, m As Integer, n As Integer
 Dim i1 As Integer, i2 As Integer, i3 As Integer
 Dim i4 As Integer, i5 As Integer, i6 As Integer

 On Error Resume Next

For i = 65 To 66:
For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66:
For i1 = 65 To 66 For i2 = 65 To 66:
For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66:
For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If ActiveSheet.ProtectContents = False Then MsgBox "One usable password is " & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) 

ActiveWorkbook.Sheets(1).Select Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next

End Sub Close the VB Editor window. Navigate to the worksheet you want to unprotect.

Hit Tools-Macro-Macros and double-click PasswordBreaker in the list.

Thursday, April 9, 2015

Free Excel / VBA Functions Tutorial Add-ins

Free Excel / VBA Functions Tutorial Add-ins 

By using the most suitable functions in your formulas, you will get the most accurate results possible, plus your Excel models will be easier to maintain and audit.

  • Are you searching for a new function to simplify your formulas ? (Several added in 2013) Are you wondering, if a function is backwards compatible with previous Excel versions ? Are you looking for translated function help (besides English) ? 
  •  Do you want to read about the syntax and usage of an Excel 2013 or VBA function in your native language and practice with a demo workbook ? 

The facts: There are over 600 Excel & VBA functions in Office 2013. Excel functions have been translated in 16 languages. Microsoft offers over 20,000+ function help webpages in 50+ languages. How to navigate fast among so many help pages ? Our free Ribbon Add-in will help you navigate to Microsoft's online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice.

The VBA Add-in is unlocked and requires enabled macros. Read how to enable macros.

The Ribbon Add-in works with Excel 2007, 2010, 2013 (both 32 & 64-bit Office)

License: Creative Commons Attribution-ShareAlike CC-BY-SA (commercial use allowed)

Download it Here

Tuesday, April 7, 2015

Athi 4 X 4 Challenge

You've gotta love that smoke

Posted by The Athi 4x4 Challenge on Tuesday, April 7, 2015

Tuesday, March 31, 2015

Optimization with Excel Solver Addin - An Overview

What are Solvers Good For?

Solvers, or optimizers, are software tools that help users find the best way to allocate scarce resources. The resources may be raw materials, machine time or people time, money, or anything else in limited supply. The "best" or optimal solution may mean maximizing profits, minimizing costs, or achieving the best possible quality.  An almost infinite variety of problems can be tackled this way, but here are some typical examples:

Finance and Investment

  • Working capital managementinvolves allocating cash to different purposes (accounts receivable, inventory, etc.) across multiple time periods, to maximize interest earnings.
  • Capital budgetinginvolves allocating funds to projects that initially consume cash but later generate cash, to maximize a firm's return on capital.
  • Portfolio optimization -- creating "efficient portfolios" -- involves allocating funds to stocks or bonds to maximize return for a given level of risk, or to minimize risk for a target rate of return.

Manufacturing

  • Job shop scheduling involves allocating time for work orders on different types of production equipment, to minimize delivery time or maximize equipment utilization.
  • Blending(of petroleum products, ores, animal feed, etc.) involves allocating and combining raw materials of different types and grades, to meet demand while minimizing costs.
  • Cutting stock(for lumber, paper, etc.) involves allocating space on large sheets or timbers to be cut into smaller pieces, to meet demand while minimizing waste.

Distribution and Networks


  • Routing(of goods, natural gas, electricity, digital data, etc.) involves allocating something to different paths through which it can move to various destinations, to minimize costs or maximize throughput.
  • Loading(of trucks, rail cars, etc.) involves allocating space in vehicles to items of different sizes so as to minimize wasted or unused space.
  • Schedulingof everything from workers to vehicles and meeting rooms involves allocating capacity to various tasks in order to meet demand while minimizing overall costs. 

Monday, March 30, 2015

The Ethics & Anti-Corruption Commission (EACC) Annual Report

The Ethics & Anti-Corruption Commission (EACC) Annual Report for the financial year 2013/2014. This Report is prepared pursuant to Section 27 of the Ethics and Anti-Corruption Act, 2011 http://www.eacc.go.ke/docs/annual%20report%202013-2014.pdf