Handy Sub to Unlock Protected Excel Worksheet

Had a request a few weeks ago from someone who needed to unprotect an Excel sheet which had been protected by someone who was no longer with the company so they could not get the Password.

here is the procedure I developed,

Sub PasswordReset()

Dim ia As Integer, ib As Integer, ic As Integer
Dim id As Integer, ie As Integer, ig 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 ia = 65 To 66: For ib = 65 To 66: For ic = 65 To 66
For id = 65 To 66: For ie = 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 ig = 32 To 126
ActiveSheet.Unprotect Chr(ia) & Chr(ib) & Chr(ic) & _
Chr(id) & Chr(ie) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(ig)
If ActiveSheet.ProtectContents = False Then
MsgBox "Your Sheet has Been Unlocked Using " & Chr(ia) & Chr(ib) & _
Chr(ic) & Chr(id) & Chr(ie) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(ig)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

So next time you forget your password and need to unprotect a sheet just add this to the VBA window and run the Module. Hey Presto.

Glookup Function - Combining Vlookup and Hlookup

I have been working on a new Excel function of late as I have always liked Vlookup and Hlookup but as discussed in an earlier post the more you try and do excel the less use they become, the issues I was finding more and more is that I used Match and Offset far more often. The main problem I had is that Vlookup will find a value in the left hand column and return a value, and Hlookup finds a value at the top but what if you have a grid and need to select a value based on a V and an H lookup?

In this case you use Match to find the first value and then use Count and Offset to create the dynamic range for the second, this was fine for one calculation but after writing 100 formulas on each sheet it becomes a pain. Therefore I worked on my new function.

Glookup (Grid Lookup) it works on the same lines as V and H lookup only the two lookup values are also the column return counts.

Here is the Formula broken down =Glookup([Vlookup],[Hlookup],[GArray])

Where Vlookup is the value in the Left Hand Column

Hlookup is Value in Top Row

GArray is the Grid Array (Where your data is – Must include your two header rows)

In the above example therefore =glookup("Feb",2007,A2:K14) would have the result of 23

So how does it work? Firstly I have to say in the interest of fairness and because it would simply be wrong not to, I have to say a big vote of thanks to RomperStomper over at Excelhelp.net who helped me with tidying up the code into a very nice succinct module.

Anyway to use Glookup simply paste the following code into the VBA window of your spreadsheet (Pressing Alt + F11 or going to Tools-Macro-Visual Basic Editor)

Function GLookup(VLookup, HLookup, GArray As Range)

Dim VRef

Dim HRef

VRef = WorksheetFunction.Match(VLookup, GArray.Columns(1), 0)

If IsError(VRef) Then

GLookup = CVErr(xlErrValue)

Else

HRef = WorksheetFunction.Match(HLookup, GArray.Rows(1), 0)

If IsError(HRef) Then

GLookup = CVErr(xlErrValue)

Else

GLookup = WorksheetFunction.Index(GArray, VRef, HRef)

End If

End If

End Function

It should now look like this

Now you can call the function from any worksheet in this book, don’t forget though you will unfortunately have to copy the function into each sheet you want to use it on.

Null values in Select function - VBA Access

I have been asked about how you can trigger an event in a SELECT CASE when there is a null value. (As you can not have null as a case)

The answer is actually incredibly easy, you use the nz() function, NZ returns a variable you can set when there is a null value, it needs to arguments.

1. the field to be evaluated
2. What to replace the Null value with

nz([Field to be Evaluated],[Value to Return if Null])

in this quick example I want to return a message box if the field is null.

SELECT CASE nz(me.nullfield,0) 'This will replace a null value with a 0
CASE 0
msgbox "Error, value Needed"
CASE "Value 1"
msgbox "Value 1 Selected"
CASE "Value 2"
msgbox "Value 2 Selected"
END SELECT

obviously if "0" is one of your CASE values you can replace it with a string i.e. "None"

Week Ending Function VBA

Just Finished working on a problem for a DB, the client wants all their reporting to be based on Week Ending dates but their files are stored by Date. How can you summarise per week?

I sorted it by creating a function I could then include in my queries -

Public Function WeekEnding(Actual as variant) As Variant
If IsDate(Actual) Then 'Check that variable is a date
WeekEnding = DateAdd("d", Actual, 7-Weekday(Actual)) 'Add on 7 days minus variable day
End If

End Function

Access Date Format Issues

I have been asked by a few people how to solve the issue that when you are sending data from Excel to Access (and vice versa) in VBA the dates get flipped into US format :- MM/DD/YYYY instead of DD/MM/YYYY now this will only happen when you the day of the date is less than 12, the issue comes from the way the Excel or Access interperate the date it recieves. annoyingly it does not give any regard to the regional settings.

Anyway the way I found to get around it is using a function to convert the date into a format that can not confuse the system.

Public Function Fixdate(ByVal str)

Dim dteDay
Dim dteMonth
Dim dteYear

If isnull(str) Then
msgbox "Error"
Exit Function
End If

dteDay = Day(str)
dteMonth = MonthName(Month(str), True)
dteYear = Year(str)

FixDate = dteDay & "-" & dteMonth & "-" & dteYear

End Function

This is then used as follows

myDate = fixdate(date)

This will return a value to mydate as 25-May-2010 which Excel or Access will be able to interperate as the correct date.