EzDevInfo.com

Remove leading or trailing spaces in an entire column of data [closed]

How do I remove leading or trailing spaces of all cells in an entire column?

The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Loop through each row of a range in Excel

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it.

How do I loop through each row of a multi-column range using Excel VBA? All the tutorials I've been searching up seem only to mention working through a one-dimensional range...

How can I URL encode a string in Excel VBA?

Is there a built-in way to URL encode a string in Excel VBA or do I need to hand roll this functionality?

How do I use FileSystemObject in VBA?

Is there something that I need to reference? How do I use this:

Dim fso As New FileSystemObject
Dim fld As Folder
Dim ts As TextStream

I am getting an error because it does not recognize these objects.

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

How can I send an HTTP POST request to a server from Excel using VBA?

What VBA code is required to perform an HTTP POST from an Excel spreadsheet?

What is the equivalent of "!=" in Excel VBA?

The problem is that != does not work as a function in excel vba.

I want to be able to use

If strTest != "" Then instead of If strTest = "" Then

Is there another approach to do this besides !=?

My function to mimic != is

Sub test()

Dim intTest As Integer
Dim strTest As String

intTest = 5

strTest = CStr(intTest) ' convert

Range("A" + strTest) = "5"



    For i = 1 To 10
        Cells(i, 1) = i

        If strTest = "" Then
            Cells(i, 1) = i
        End If

    Next i


End Sub

Save each sheet in a workbook to separate CSV files

How do I save each sheet in an Excel workbook to separate CSV files with a macro?

I have an excel with multiple sheets and I was looking for a macro that will save each sheet to a separate CSV (comma separated file). Excel will not allow you to save all sheets to different CSV files.

Function to convert column number to letter?

Does anyone have an Excel VBA function which can return the column letter(s) from a number?

For example, entering 100 should return CV.

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)?

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?

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.

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords.

Is there a way of removing or cracking the password on a VBA project?

How to Return a result from a VBA Function

How do I return a result from a function?

For example:

Public Function test() As Integer
    return 1
End Function

This gives a compile error.

How do I make this function return an integer?

How to avoid using Select in Excel VBA macros

I've heard much about the understandable abhorrence of using .Select in Excel VBA macros, but am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use variables instead of Select functions. However, I am not sure how to refer to things (like the ActiveCell etc.) if not using Select.

I have found this article on ranges and this example on the benefits of not using select but can't find anything on how?