converting an Excel macro into a VB .exe

Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
thanks you Justin,

I hope one day the softwarelandscape will be not like a puzzle. If you didn't post the last linke, I was still turning in circles on the MS-site.

report will follow!
:bow:
Erik
 
Upvote 0
I killed the link from my post containing the example download:

This link contains the small example plus the .Net framework which is necessary for using the application. It is a big download, so be patient. I will be removing this link in a day or two, but it is active now. Please let me know if you have any problems.

http://www.jlxl.net/ExampleConversion.exe
 
Upvote 0
Justin,

Thank you for all your efforts.
I got your example to work now.

little problem
The MessageBox which appears when you click Cancel only shows an "OK" button instead of "Yes-No", then the programm just asks again your name ...
Could you check that ?

I downloaded the conversion programm of your last post and presume it will take a while to learn to work a bit with that.

kind regards,
Erik
 
Upvote 0
Apologies for ths issue Erik, the Conversion was just what the setp program was called, it contained the .net framework and I just threw in your example too.

See if this is better:

http://www.jlxl.net/CreateTextFile.zip


I am just designing this and throwing examples an random stuff in also just to show some Vb.Net power, If you would like me to design it in a certain way for you or some of your users to use, let me know I can whip it up exactly how you need.

Later
 
Upvote 0
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.
 
Upvote 0
If you would like me to design it in a certain way for you or some of your users to use, let me know I can whip it up exactly how you need.
Justin,
this was just intresting stuff I would like to dig out a bit
at the moment no users are waiting for such solutions, but it's better to start experiments before :)

vacations are coming to do so ...

tusharm, :bow:

kind regards,
Erik
 
Upvote 0
Yeah this post got going and things got a bit off track as to what the actual topic was :oops:

I made an assumption also, and that was that the op wanted to know if a VBA code could be made into a VB code?

I do agree with the "why write a code in VBA if it's needed it's better suited in VB" theory, but I have seen an increase in posts about how to convert one to another, and I took that to mean, instead of writing with vba, how much different is writing in vb? I attempted to just show how a code written in VBA could be somewhat easily written in VB, by just copying Erik's code as is, changing the necessary bits, then compiling. I know before I finally got a copy of Visual Studio, I wrote programs using Excel-VBA, that really had nothing to do with Excel, but I needed a platform.

Tushar, your statements are spot on, and I could not agree with you more, I have not had any reason as of yet to use VB for anything other than Stand-alone apps, plus the previously posted front loader for Excel, and I am starting to play around with building dll's, etc..

Apologies for getting off the subject, and I look forward to a mythical VBA from you Tushar :wink:
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top