EzDevInfo.com

Alternative IDE for VB6 and VBA [closed]

I've been spoiled by Visual studio 2008 and Eclipse and have to do a little maintainence work on a VB6 app.

Does anyone know of an alternative/ updated IDE for VB6?

A rewrite is not an option I'm just fixing a couple of bugs and it's a big codebase.

Excel VBA - Exit For loop

I would like to exit my for loop when a condition inside is met. How could I exit my for loop when the if condition has been met. I think some kind of exit at the end of my if statement, but don't know how that would work.

Dim i As Long
For i = 1 To 50
    Range("B" & i).Select
    If Range("B" & i).Value = "Artikel" Then
        Dim temp As Long
        temp = i
    End If
Next i
Range("A1:Z" & temp - 1).EntireRow.Delete Shift:=xlToLeft

Deleting a file in VBA

Using VBA, how can I:

1. test whether a file exists, and if so,
2. delete it?

VBA array sort function?

I'm looking for a decent sort implementation for arrays in VBA. A Quicksort would be preferred. Or any other sort algorithm other than bubble or merge would suffice.

Please note that this is to work with MS Project 2003, so should avoid any of the Excel native functions and anything .net related.

Repeated calls of Chart.SetSourceData give error 1004

I have a problem with an application that was created in Excel 2003 in my company. The application retrieves data from a source and updates a Chart using the SetSourceData in a VBA routine passing a Range containing the cells where the relevant data is written.

The application runs just fine in Office 2003, but when the application is executed in Office 2010 it gives this error:

Run-time error '1004': Method 'SetSourceData' of object'_Chart' failed.

I have created a For loop in a simple Excel file in Office 2010 and depending on the number of columns passed in the Range to the Chart the error will come up sooner or later. The more columns passed in the Range the sooner it will come up. I guess this has to be related with the number of series in the Chart(more columns more series).

Is this some sort of mechanism/buffer in the Chart Object or Series implemented in Office 2010 that did not exist in Office 2003? The same For loop never shows a problem when it is run in Office 2003 and I am not sure how to solve this problem.

So far I have only been able to delete all the Series controlling the Error with a Goto instruction to delete all the series in the SeriesCollection using a For Each loop to select all the objects in the SeriesCollection of the Chart. If I do this and resume the execution of the application when I pass the Range again all the data is painted in the Chart Object properly.

Example to reproduce the error. The following code is to be put in a VBA module in a new Excel 2010 workbook. Run the Sub setDataChart and the application will run until the error message is displayed.

    Sub setDataChart()
    Call createAColValues
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Range("A1:FA6"), PlotBy:=xlColumns
    ActiveSheet.ChartObjects(1).Activate
    With ActiveChart.Parent
         .Height = 325
         .Width = 900
         .Top = 120
         .Left = 10
    End With
    Call updateValues
    Call sendData
End Sub

    Sub sendData()
    Dim cht As ChartObject
    Set cht = ActiveSheet.ChartObjects(1)

    'On Error GoTo delSeries:
    For i = 0 To 1000
        cht.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6"), PlotBy:=xlColumns
    Next i
End Sub

Sub createAColValues()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A1:A2").Select
    Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
    Range("A1:A6").Select
End Sub

Sub updateValues()
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,10)"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B6"), Type:=xlFillDefault
    Range("B1:B6").Select
    Selection.AutoFill Destination:=Range("B1:FA6"), Type:=xlFillDefault
    Range("B1:FA6").Select
End Sub

VBA - how to conditionally skip a for loop iteration

I have a for loop over an array. What I want to do is test for a certain condition in the loop and skip to the next iteration if true:

For i = LBound(Schedule, 1) To UBound(Schedule, 1)
    If (Schedule(i, 1) < ReferenceDate) Then
        PrevCouponIndex = i
        Continue   '*** THIS LINE DOESN'T COMPILE, nor does "Next"
    End If
    DF = Application.Run("SomeFunction"....)
    PV = PV + (DF * Coupon / CouponFrequency)
Next

I Know I can do:

 If (Schedule(i, 1) < ReferenceDate) Then Continue For

but I want to be able to record the last value of i in the PrevCouponIndex variable.

Any ideas?

Thanks

What does the Excel VBA range.Rows property really do?

OK, I am finishing up an add-on project for a legacy Excel-VBA application, and I have once again run up against the conundrum of the mysterious range.Rows(?) and worksheet.Rows properties. Does anyone know what these properties really do and what they are supposed to provide to me? (note: all of this probably applies to the corresponding *.Columns properties also).

What I would really like to be able to use it for is to return a range of rows, like this:

   SET rng = wks.Rows(iStartRow, iEndRow)

But I have never been able to get it to do that, even though the Intellisense shows two arguments for it. Instead I have to use one of the two or three other (very kludgy) techniques. The help is very unhelpful (typically so for Office VBA), and googling for "Rows" is not very useful, no matter how many other terms I add to it.

The only things that I have been able to use it for are 1) return a single row as a range ( rng.Rows(i) ) and 2) return a count of the rows in a range ( rng.Rows.Count ). Is that it? Is there really nothing else that it's good for?

Clarification: I know that it returns a range and that there are other ways to get a range of rows. What I am asking for is specifically what do we get from .Rows() that we do not already get from .Cells() and .Range()? The two things that I know are 1) an easier way to return a range of a single row and 2) a way to count the number of rows in a range. Is there anything else?

automatically execute an Excel macro on a cell change

How can I automatically execute an Excel macro each time a value in a particular cell changes?

Right now, my working code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H5")) Is Nothing Then Macro
End Sub

where "H5" is the particular cell being monitored and Macro is the name of the macro.

Is there a better way?

Disable compile errors when editing VBA macros

You are editing a VBA macro or function in Excel and get half way through typing a line, then move the cursor to a different line. The VBA editor decides to interrupt your workflow by popping up a message box that has to be dismissed.

Is there any way to disable this behaviour? I find it irritating...

How to get the path of current worksheet in VBA?

I wrote a macro as an add=in. And I need to get the path of the current worksheet on which it is being executed. How do I do this? How do I get the file path (just the directory)?

How to parse XML using vba

I work in VBA, and want to parse a string eg

<PointN xsi:type='typens:PointN' 
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' 
xmlns:xs='http://www.w3.org/2001/XMLSchema'>
    <X>24.365</X>
    <Y>78.63</Y>
</PointN>

and get the X & Y values into two separate integer variables.

I'm a newbie when it comes to XML, since I'm stuck in VB6 and VBA, because of the field I work in.

How do I do this?

Break out of a While...Wend loop in VBA

I am using a While...Wend loop of VBA.

Dim count as Integer

While True
    count=count+1

    If count = 10 Then
        ''What should be the statement to break the While...Wend loop? 
        ''Break or Exit While not working
    EndIf
Wend

I don't want to use condition like While count<=10...Wend

Please guide me.

Excel VBA App stops spontaneously with message "Code execution has been halted"

From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this:

We have a number of Excel VBA apps which work perfectly on a number of users' machines. However on one machine they stop on certain lines of code. It is always the same lines, but those lines seem to have nothing in common with one another.

If you press F5 (run) after the halt, the app continues, so it's almost like a break point has been added. We've tried selecting 'remove all breaks' from the menu and even adding a break and removing it again.

We've had this issue with single apps before and we've 'bodged' it by cutting code out of modules, compiling and then pasting it back in etc.

The problem now seems to relate to Excel itself rather than a single .xls, so we're a little unsure how to manage this.

Any help would be gratefully received :)

Thanks,

Philip Whittington

Microsoft Excel ActiveX Controls Disabled?

I have some Excel worksheets that use ActiveX checkboxes to control certain activity. They worked recently but today started to give errors. I was alerted to this by a colleague, but it was still working on my computer. I checked his version of Excel against mine and his was newer. I noticed there were new Windows updates, so I did the update. After I applied pending updates, it now no longer works on my computer. I cannot check the ActiveX checkboxes any longer, and, as a part of trying to debug, it appears I cannot even add an ActiveX control to any worksheet, even a new worksheet, any more. I get an error dialog that says, "Cannot insert object." (I can still add form controls, just not ActiveX.) Anyone else experiencing this after a recent update? Any suggestions?

Thanks,

Mike

Loop through files in a folder using VBA?

I would like to loop through the files of a directory using vba in Excel 2010.

In the loop, I will need

• the filename, and
• the date at which the file was formatted.

I have coded the following which works fine if the folder has no more then 50 files, otherwise it is ridiculously slow (I need it to work with folders with >10000 files). The sole problem of this code is that the operation to look up file.name takes extremely much time.

Code that works but is waaaaaay too slow (15 seconds per 100 files):

Sub LoopThroughFiles()
   Dim MyObj As Object, MySource As Object, file As Variant
   Set MySource = MyObj.GetFolder("c:\testfolder\")
   For Each file In MySource.Files
      If InStr(file.name, "test") > 0 Then
         MsgBox "found"
         Exit Sub
      End If
   Next file
End Sub

Problem solved:

1. My problem has been solved by the solution below using Dir in a particular way (20 seconds for 15000 files) and for checking the time stamp using the command FileDateTime.
2. Taking into account another answer from below the 20 seconds are reduced to less than 1 second.