Distributing Add-ins via Installer
Recently I needed to create an installer for my add-ins using Inno Setup installer. Inno Setup is a free ware professional installer. It lets you use Pascal to create special code to help you install the add ins.
Luckily, over at Dutch Gemini’s website he has helped with plenty of the code to get there. Very well done, I might add.
It looks like he uses a single add-in file for Excel 2003 and below and Excel 2007 and above (for ribbon purposes). I prefer to have all my code in a single XLA file (if I’m not using Excel DNA) for the main code and then a separate XLAM file for the ribbon. So I had to change some of his code to account for that.
.NET and Modeless Forms
When I was doing some programming in VB.NET (for the Time Stamp program), I tried putting a modeless form in Excel. It came up as a new window in the task bar but then wouldn’t actually show on the screen. When I did a modal form it would show on the screen but also in the task bar.
.NET let’s you choose the parent window of your form. I found some code on StackOverflow in C# that gives you the proper owner window in IWin32Window form.
Range Address from Number to Letter
Sometimes, before I realize it (and, I’m sure, others too) I do a bunch of work, that turns out that I didn’t need to do before. The RangeAddress function that I created is a perfect example of this. This function goes from a column number to a letter and returns the whole string range address. I started off with someones else’s code. I then made it work for me. Their code only worked for Excel 2003 and below. The code below will work for All Excel versions. But there is a better way! The better way is in the RRange function I posted about before. Below this code I’ll show you the basic gist on how to go about it.
Find Last Cell
Finding the last cell is a common thing people look for when they are coding in VBA. I made a function that does this automatically for me when I am looking for the last cell. In conjunction with the RRange function this simplifies the programming life for VBA significantly.
Heres some example code on how to use the function FindEnd.
Sub FindEndExamples()
Dim l As Long
'Find last row in column 2 on current worksheet
l = FindEnd(2)
Debug.Print l
'Find last column in row 5 on current worksheet
l = FindEnd(5, 2)
Debug.Print l
'Find very last row in current worksheet
l = FindEnd()
Debug.Print l
'Find very last column in current worksheet
l = FindEnd(, 2)
Debug.Print l
End Sub
And here’s the actual FindEnd Function.
Get Range from Row & Column Numbers
Before I created a quite elaborate code to get the column letter from the column numbers. It turns out that it was much easier than I had thought before. So in the code below I’ve simplified it considerably by using the Cells method.
In the function below I use a function called FindEnd.
'RRAnge
' --------------------------------------------------------------
'Comments: This Function returns the desired range.
'
'Arguments: lRow First cell row number.
' lCol First cell column number.
' lRowEnd Last cell row number or column number (negative) to find last row in.
' lColEnd Last cell column number or row number (negative) to find last column in.
' wks Worksheet of range.
'
'Date Developer History
'--------------------------------------------------------------
'Jan 6, 2012 Jon Nyman Initial version
'
Public Function RRAnge(ByVal lRow As Long, ByVal lCol As Long, Optional ByVal lRowEnd As Long = 0 _
, Optional ByVal lColEnd As Long = 0, Optional ByVal wks As Worksheet = Nothing) As Range
'Determine if worksheet exist, if it doesn’t then set to current location.
If wks Is Nothing Then Set wks = ActiveWorkbook.ActiveSheet
'Determine if the last row was set, if it isn’t then get last row number in wks
If lRowEnd < 1 Then
If lRowEnd Then
'If lRowEnd is negative then get the last row in column number lRowEnd
lRowEnd = FindEnd(Abs(lRowEnd), , wks)
Else
'If lRowEnd is 0 then use the range find function to determine last row.
lRowEnd = FindEnd(, , wks)
End If
End If
'Determine if the last column was set, if it isn’t then get last column number in wks
If lColEnd < 1 Then
If lColEnd Then
'If lColEnd is negative then get the last column in row number lColEnd
lColEnd = FindEnd(Abs(lColEnd), 2, wks)
Else
'If lColEnd is 0 then use the range find function to determine last column.
lColEnd = FindEnd(, 2, wks)
End If
End If
'Return range, if there is an error then return nothing.
On Error Resume Next
With wks
Set RRAnge = .Range(.Cells(lRow, lCol), .Cells(lRowEnd, lColEnd))
End With
If Err.Number Then Set RRAnge = Nothing
End Function
Coming Soon...
I am currently working on a time card that will allow the user to track their time in Excel. This will allow the user to choose multiple projects at once and produce charts as a great visual to see how you are working.
After that I will work on my advanced find in VB.NET.
The long term project is the spreadsheet budget in Excel. This project is expected for first release in mid 2012.
Import Data from Excel to VBA
I’ve always wanted a function that can get me data from excel in a format that will always be the same. Sometimes I get the data and I expect a 2D variant array but instead get a string or double value. So I created a function to make it easy for myself, getting the same format that I expect.
'ImportExcelData
' --------------------------------------------------------------
'Comments: This Function imports excel data in different formats (1D, 2D, or String).
'
'Arguments: rRng Range to be imported.
' i1D_2D_Str3 Type of import, 1=1D, 2=2D, and 3=String
' iValueType Import type .Value (Excel checks for different types)
' or .Value2 (Excel retrieves all values as string or double)
' sDelimiter Delimiter to use for string value that is returned.
'
'Notes: This was created for use of Jon Nyman and can be distributed by Paciolan.
'Date Developer History
'--------------------------------------------------------------
'Dec 30, 2011 Jon Nyman Initial version www.SpreadsheetBudget.com
'
Public Function ImportExcelData(ByRef rRng As Range, Optional ByVal i1D_2D_Str3 As Integer = 2 _
, Optional iValueType As Integer = 2, Optional ByVal sDelimiter As String = "`") As Variant
Dim i As Long, j As Long
Dim saData() As String, sData As String
Dim vaData(1 To 1, 1 To 1) As Variant, va1D() As Variant
Dim vData As Variant
'Get data by Value or Value2
If iValueType = 2 Then
vData = rRng.Value2
Else
vData = rRng
End If
'If data is single cell then put in 2D
If rRng.Cells.Count = 1 Then
vaData(1, 1) = vData
vData = vaData
End If
If i1D_2D_Str3 = 2 Then
'Return 2D results
ImportExcelData = vData
ElseIf i1D_2D_Str3 = 3 Then
'Concatenate 2D results and return string.
If rRng.Columns.Count > 1 Then
sData = vbNullString
ReDim saData(1 To UBound(vData, 2))
For i = 1 To UBound(vData)
For j = 1 To UBound(vData, 2)
saData(j) = CStr(vData(i, j))
Next j
sData = sData & Join(saData, sDelimiter) & vbNewLine
Next i
ImportExcelData = Left$(sData, Len(sData) - 1)
Else
'Concatenate 1D results and return string
ReDim saData(1 To UBound(vData))
For i = 1 To UBound(vData)
saData(i) = vData(i, 1)
Next i
ImportExcelData = Join(saData, sDelimiter)
End If
Else
'Dimension 1D result variant array.
ReDim va1D(1 To UBound(vData) * UBound(vData, 2))
'Create 1D out of 2D
If rRng.Columns.Count > 1 Then
For i = 1 To UBound(vData)
For j = 1 To UBound(vData, 2)
va1D((i - 1) * UBound(vData, 2) + j) = vData(i, j)
Next j
Next i
Else
For i = 1 To UBound(vData)
va1D(i) = vData(i, 1)
Next i
End If
ImportExcelData = va1D
End If
End Function
Welcome
I decided that using Drupal was more work than I wanted to put into my website, so here’s to WordPress! Hopefully I won’t regret it.
Here’s my previous entry for this post:
Welcome to SpreadsheetBudget.com. This is my blog that will demonstrate ideas and learning material as I create a budget spreadsheet using Excel. Once I finish creating the software, or at least get closer, I will begin to publish how to articles on how to use the software. This is a journey for me as I learn how to program in VBA and VB.NET and eventually other languages. One step at a time!
I’m finally giving in and reading the book and doing the courses for “The Road to React.”
In the introduction he made it sound like SPA’s don’t need to hit a server on every page navigation. Which would only be true if you don’t need to interact with others, like my personal apps that I write. So, it is a bit off putting that the author is misleading there. I do think what he meant was that you don’t need to get the HTML for each page navigation.
About
Personal
Father of four who enjoys, backpacking, reading, and programming.
Work in a Nutshell
I enjoy functional programming and am currently doing mostly F# with ASP.NET WebAPI (RESTful API), with SQL Server and also some ASP.NET MVC and front end programming (JavaScript/HTML/CSS).
I was doing node.js with MongoDB but found F# to a great language to work with in a .NET shop. So I ported it back to .NET platform.