A friend asked me to look into online spreadsheets.
The best option seems to be Editgrid because he wants to collaborate with others and see
the changes realtime. I will be blogging on this topic in the near future.
Any comments are welkom.
Jelle-Jeroen
I just read the Smurf on Spreadsheets blog and found a post on a Excel Dashboard Competition.
witch I’m gonna check out and maybe even enter.
Check out the site…..
Cheers
Jelle-Jeroen
A couple days ago Dick Kusleika from Daily Dose of Excel was the first person to ccomment on my blog.
I want to thank him for that and hoping he will be reading my blog as promised.
All sugestions / comments will be appreciated.
Jelle-Jeroen
A while back when there was no xlns.lamkamp.nl I wrote and posted code for a Modified Dietz Method
UDF on wikipedia. For those of you in the financial industry it might be usefull.
Here is the link to that page on wikipedia.
Thinking about Rick Williams remarks I rewrote the VLOOKPLUS function to perform better.
For an even better performance I shall have to rewrite the function in C/C++ or VB.Net.
Here is the result for now.
( It’s only good for exact matches when working with a negative column_index ).
Public Function VLOOKUPPLUS(l_v, t_a As Range, c_i As Long, Optional r_l) As Variant
Dim i As Long: Dim l_r As Range
With Application.WorksheetFunction
If c_i < 0 Then
If t_a.Columns.Count + c_i < 0 Then VLOOKUPPLUS = CVErr(xlErrRef): Exit Function
Set l_r = t_a.Offset(0, t_a.Columns.Count - 1).Resize(t_a.Rows.Count, 1)
VLOOKUPPLUS = .Index(l_r.Offset(0, c_i + 1), .Match(l_v, l_r, 0))
ElseIf c_i > 0 Then
VLOOKUPPLUS = .VLookup(l_v, t_a, c_i, r_l)
Else
VLOOKUPPLUS = CVErr(xlErrNA)
End If
End With
End Function
Here is the link more readable code file.
Gahban berry wrote a great blog on writing custom Excel Worksheet Functions in C#.
Here is the link
Rick Williams had a interesting comment on my Extended VLOOKUP UDF post.
He uses a index(match()) combination for these purposes.
I will look in to the performance comparison.
Here is the link to his reply on the Excel User Group.
In my daily job we use a lot of VLOOKUP on external datasheets.
We don ‘t want to change these sheets but often we want to do a sort of VLOOKUP but
with a negative column_index parameter, so we can look up to the left of the lookup_value.
I wrote a UDF to do just that.
Can some of you guys comment on my code…….. thnx
Function VLOOKUPPLUS(l_v, t_a As Range, c_i As Long, Optional r_l) As Variant
Dim i As Long
If c_i < 0 Then
If t_a.Columns.Count + c_i < 0 Then
VLOOKUPPLUS = CVErr(xlErrRef)
Exit Function
End If
If r_l = 0 Then
For i = 1 To t_a.Rows.Count
If t_a.Cells(i, t_a.Columns.Count).Value = l_v Then
VLOOKUPPLUS = t_a.Cells(i, (t_a.Columns.Count + 1 + c_i)).Value
Exit Function
End If
VLOOKUPPLUS = CVErr(xlErrValue)
Exit Function
Next i
Else
For i = 1 To t_a.Rows.Count
If t_a.Cells(i, t_a.Columns.Count).Value <= l_v Then
VLOOKUPPLUS = t_a.Cells(i, (t_a.Columns.Count + 1 + c_i)).Value
End If
Next i
End If
ElseIf c_i > 0 Then
VLOOKUPPLUS = Application.WorksheetFunction.VLookup(l_v, t_a, c_i, r_l)
Else
VLOOKUPPLUS = CVErr(xlErrNA)
End If
End Function
Here is the link more readable code file.
In my daily job validating security codes is a must.
So I wrote a UDF for validating ISINCodes.
There are no comments in the code, sorry for that…. maybe later…. enjoy..
Public Function ISINCODE(ByVal sISINCode As String) As Boolean
Dim i As Integer: Dim iTotalScore As Integer
Dim s As String: Dim sDigits As String
sISINCode = UCase(Trim(sISINCode))
If Len(sISINCode) <> 12 Then Exit Function
If MID(sISINCode,1,1) < "A" Or MID(sISINCode,1,1) > "Z" Then Exit Function
If MID(sISINCode,2,1) < "A" Or MID(sISINCode,2,1) > "Z" Then Exit Function
sDigits = ""
For i = 1 To 11
s = Mid(sISINCode, i, 1)
If s >= "0" And s <= "9" Then
sDigits = sDigits & s
ElseIf s >= "A" And s <= "Z" Then
sDigits = sDigits & CStr(Asc(s) - 55)
Else
Exit Function
End If
Next i
sDigits = StrReverse(sDigits)
iTotalScore = 0
For i = 1 To Len(sDigits)
iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
If i Mod 2 = 1 Then
iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
If CInt(Mid(sDigits, i, 1)) > 4 Then
iTotalScore = iTotalScore - 9
End If
End If
Next i
If (10 - (iTotalScore Mod 10)) Mod 10 = CInt(Mid(sISINCode, 12, 1)) Then ISINCODE = True
End Function
Here is the link to the text file.
This my first post on my first blog.
I will be blogging on mainly spreadsheet topics.
I hope someone will find this helpfull or enjoy it….
cheers
Jelle-Jeroen