Edit: Justin, I realize you are addressing something of interest to you and that is just fine. However, the OP started this discussion with a conversion of VBA to VB query.
This discussion is going way out into left field and adds nothing to the conversion of code from VBA to VB. At most, and even that is not clear, it might address some issues related to VB to VB.Net conversion.
One assumption I am making is that the standalone program will still interact with XL. If it doesn't then I would be at a loss as to why it was written in VBA in the first place.
Note that most of the same issues apply to creating a COM Add-In (i.e., a DLL) as to a standalone VB (or .Net) program that interacts with XL.
Please don't get me wrong but I don't intend to provide worked solutions to the examples below for two reasons. One, I've already provided what I think are very useful references (and another reference worth looking at is Excel Professional Development by Bullen, Bovey, and Green). Two, I just don't have the time to write up the solutions. Over the past few months I have had reason to explore the relative pros and cons of VBA, VB, and VB.Net. It's taken me weeks of work to understand all the subtleties that can haunt converting a fully functional VBA system to VB and VB.Net. It would take me even longer to write up explanations in detail -- if it is even possible in a text-only forum. Sorry.
Of course, if I ever get around to writing my mythical book on the subject of VBA, you might find the same (and more) scenarios addressed in detail there. {grin}
Scenario 1: The easiest to address is something which simply requires addressing XL specific entities.
Code:
Option Explicit
Sub Example1()
Dim x As Range, y As Worksheet
Set x = Range("a1")
Set y = Workbooks.Add().Worksheets.Add()
x.CurrentRegion.Copy
y.Range("a1").PasteSpecial xlPasteValuesAndNumberFormats
End Sub
Scenario 2: Handling a userform. This gets rather complicated because unlike VBA code, VB code runs independent of XL (two asynchronous threads) and also because showing a modal form is no longer as trivial as just using the Show method.
There is a userform with 1 textbox and one button, which just hides the userform. In a standard module is code such as:
Code:
Option Explicit
Sub Example2()
UF.Show
MsgBox UF.TextBox1.Text
End Sub
Scenario 3: A userform with a refedit box. This is very, very messy and there is no straightforward solution. Interestingly, I found absolutely no information anywhere on how to make this work. I hope to put a write up on my web site in the coming weeks, but who knows which will be *the* week. {grin} The same setup as in Scenario 2 but now instead of a textbox there is a refedit control. The code in the standard module is:
Code:
Option Explicit
Sub Example3()
UF.Show
MsgBox UF.RefEdit1.Text
End Sub
Scenario 4: Handling XL events (and a very good example of why one should use only application level events). Given the enthusiasm of so many in this forum for worksheet level event procedures this should be particularly relevant. {grin} One worksheet (and if you want a bigger challenge multiple worksheets) have an event procedure such as:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
With Target.Offset(0, -1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Application.EnableEvents = True
End Sub
Scenario 5: The worksheet change event procedure interacts with the result of a user specified column (provided through a userform). The code in the worksheet event procedure is:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, MonitoredRng) Is Nothing Then _
Exit Sub
Dim aCell As Range
On Error GoTo ErrXIT
Application.EnableEvents = False
For Each aCell In Intersect(Target, MonitoredRng)
With aCell.Offset(0, -1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Next aCell
ErrXIT:
Application.EnableEvents = True
End Sub
The code in the standard module to get things going. Since a refedit is simply too difficult to implement, the userform uses a textbox to specify a column. Also note the use of double-minus. While I am very opposed to its use, many use the construct every chance they get and I've intentionally included it here. [I would have used CInt(). {grin}]
Code:
Option Explicit
Public MonitoredRng As Range
Sub Example5()
UF.Show
'Set MonitoredRng = Range(UF.RefEdit1.Text)
Set MonitoredRng = ActiveSheet.Columns(--UF.TextBox1.Text)
End Sub
Scenario 6: A user defined function -- any user-defined function.