Posts by Om Avataar

Re: Range(activecell, Activecell.end(xlright)).copy

Thanks, Andy, for that clarification... But now I have a real problem - for my overall program, I need my code to be able to copy Cell A5 and ALL cells to the right of cell A5 right up to the last non-blank cell in that row - and there WILL be empty, possibly non-consecutive cells scattered in between cell A5 and the last non-blank cell in the row.

Also, I need the code to just copy all these cells on an 'as is' basis - without deleting the empty cells in between.

Pls help... Thanks

Re: Select Vs Activate: When to use which?

Thanks, Andy... Appreciate it

Is the following valid code in Excel VBA?

If Application.WorksheetFunction.isblank(Cells(RowIndex, ColIndex)) Then

Thanks

Does this code copy all cells from the active cell up to the last non-blank cell, or is it up to the first blank cell after the last non-blank cell? Thanks

1. What's the difference between:

Worksheets("Sheet1").Range("A5").Select

and

Worksheets("Sheet1").Range("A5").Activate

?

2. Can I use a Selection. method with an object 'selected' using .Activate in the preceding code line?

3. Say I am currently on Sheet2, Cell A5. When I click the macro button, I want Excel to copy the value from Sheet1, Cell A5 and paste it into Sheet2, Cell A5. But I do NOT want the screen display to actually jump from Sheet2 to Sheet1, and then back to Sheet2. So do I need to use .Activate, or .Select, to copy the value of Sheet1, Cell A5?

Dear VBA Gurus,

They say a picture is worth a thousand words; I have attached a zipped Excel workbook with 2 worksheets - which I hope will be self-explanatory as to what I am trying to achieve via the VBA procedure/macro I need help in coding.

If anyone can help, that'd be awesome; I'm hoping my VBA code objective here will be a walk in the park for most of you Gurus out there.

Many gazillion thanks in advance to anyone who can assist with the VBA code to 'macrofy' this. Just in case, my email address is [email protected]

Cheers,
- Om Avataar

Dear VBA Gurus,

In my Excel workbook, I have a main/index sheet (worksheet 'Main') which essentially acts as a Navigation sheet for the entire workbook. The workbook has about 40 or so sheets, which can be categorized into 6 groups. I have created a click button for each of these 6 groups - and clicking any button displays a combo-box listing of the names of worksheets belonging to that group. For example, if a user selects 'ABC' from this combo-box list, he will be brought to worksheet 'ABC' (the click button macro should also scroll to cell A1 of worksheet 'ABC'), and so on. There is a also a 'Cancel' option in each combo-box list should a user wish to not select any worksheet in that group.

I have coded function insertCboPerfMgmt() below, which is called upon clicking button PerfMgmt_NavButton(). Although the combo-box object appears and is populated as expected - that is about all that goes right.
Two main problems I am facing are: The macro does NOT take me to the worksheet selected in the combo-box, and the combo-box object does not disappear or get 'destroyed' after the user has made his selection.

Plse advise what I am doing wrong... Hope I've described the problems in detail. Also do not assume any VBA knowledge on my part; I'm a total, absolute beginner!! Thanks a million in advance.

Sub insertCboPerfMgmt()
    Set oleX = ActiveSheet
     
    oleX.Shapes.AddOLEObject(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=125, Top:=200, Width:=180, Height:= _
    16).Select
     
    Selection.ListFillRange = "AA9:AA15"
End Sub

Sub PerfMgmt_NavButton()
    Call insertCboPerfMgmt
    
    Select Case ComboBox1.ListIndex + 1
        Case 1
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
            ActiveWorkbook.Worksheets("Deal_Search").Select
            ---> I need for Worksheets("Deal_Search") to SCROLL TO CELL A1
        Case 2
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
            ActiveWorkbook.Worksheets("M_to_M_Conduit").Select
        Case 3
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
            ActiveWorkbook.Worksheets("M_to_M_Losers").Select
        Case 4
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
            ActiveWorkbook.Worksheets("M_to_M_Detail").Select
        Case 5
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
            ActiveWorkbook.Worksheets("Deal_Analyst").Select
        Case 6
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
            ActiveWorkbook.Worksheets("DSAC_Approval").Select
        Case 7
            ---> I need for ComboBox OLEObject to Disappear from worksheet Main!
    End Select
End Sub

I have set up a combolist box, and wish to run different VBA subroutines depending on what the user has selected from the combolist. How do I do this? Apologies in advance if this sounds like a silly question... Thanks in advance

Re: Navigation Buttons With Sub-menus

Hi Zimitry,

Right next to a Main button on the Start/Index tab itself, pls... If you could shed some light on code as well, that would be really awesome.

Many thanks in advance.

- Om

Dear VBA Gurus,

I have a large Excel workbook with close to 44 or so worksheets. These worksheets can be roughly grouped into 6 Main groups, i.e. {A1,A2,A3...A7},{B1,B2,B3...B7,B8,B9}, etc. On the main Start or Index tab, I have placed 6 buttons for each of the Main groups.

Is there a way to code the buttons so that on clicking a Main button, say Button A, a drop-down menu opens to its right with navigation options to go directly to any tab/worksheet within that group, i.e. for {A1,A2,A3...A7}, the drop-down will have seven items, etc.

Thanks.

...and exporting results from Visual C++ 2005 (VC++) to Excel. How does one do this? I want to use VC++ as the main calculation/simulation engine, as it is rumored to execute code ~15x faster than an equivalent algorithm coded/executed in Excel VBA.

I'm not too sure about the interfacing part though, i.e. importing input data from Excel to VC++, and subsequently re-exporting results from VC++ back to Excel. Would anyone have sample code showing how such interfacing is done? I think I should be able to extend/customize your sample interfacing code for my specific needs.

A million thanks in advance... and apologies if this question concerning VC++ does not belong here; I wasn't sure of where else to turn to for help.

- om

Assume code execution has reached line# 101 in a subroutine, and based on a condition I need to jump back to line# 57. How do I do this? Is there a 'Go To Line #' equivalent in Excel VBA?

Many thanks.

Re: Can a Subroutine be called within a Function?

Thanks, one & all...

1. Can a Subroutine be called from within a user-defined Public Function? How?

2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?

Pls advise. Thanks.

Re: MC w/Random Number Generation

Dear VBA Gurus,

Pls advise at your convenience. Thanks.

- Om

I get the following error whenever I try running a Monte Carlo simulation with iterations in excess of 64000 (The MC simulation calls the 'NormSInv' worksheet function at least once per iteration, and the argument to the 'NormSInv' function is always a standard normal RANDOM number also generated by Excel VBA):

[FONT="Palatino Linotype"]Run-time error '1004': Unable to get the NormSInv property of the WorksheetFunction class[/FONT]

Why do I inevitably encounter this error ONLY when the number of iterations exceeds 64000, for instance? Simulations with iterations < 64000 run smoothly without a glitch.

Pls advise soonest possible. Many thanks in advance...

My following code doesn't work:

If D(count) Mod (0.08333) = 0.00 Then

, where D(count) is of type 'Double' as well.

Q1. Is it true that Excel VBA's Mod fn can only take integer arguments?

Q2. Is there an equivalent of this Mod fn that works on floating point numbers?

Thanks.

Does using ReDim to size an array also initialize all array elements to zero by default? If not, is there a quick way to autoinitialize all array elements to zero? Thanks

I get the following cryptic error message:

"Run-time error '438': Object doesn't support this property or method"

when I try to run the following seemingly innocent code in Excel VBA:

j = Application.WorksheetFunction.NormSInv(Application.WorksheetFunction.Rand())

What am I doing wrong here?

By the way, the following code

j = Application.WorksheetFunction.NormSInv(Rnd(Now()))

doesn't generate any error message, altho I'm not very sure if it does in fact generate random standard normal numbers as required.

Any advice would be appreciated... Thanks.

Hi VBA Gurus,

The following code deletes the entire range from current row to row 2500:

Rows(ActiveCell.Row & ":2500").Delete

But I need for only Columns A-G in the above range of rows (i.e. current row to row 2500) to be 'deleted' or cleared of their contents (is there a 'ClearContents' or similar function in Excel VBA?); columns H onwards of current row to row 2500 I still absolutely need!

To what should the code above be changed in order to achieve my objective? Is something like the following valid Excel VBA code??
Rows(ActiveCell.Row & ":2500").Columns("A:G").ClearContents

Pls advise... Thanks a million in advance.

Cheers!
- Om