Tuesday, November 13, 2012

Creating A Text File With Vba

Sub WriteTextFile()
     ' Writing a text file using File System Object in VBA
     ' This code requires a reference (In the VBE Tools > References) to Microsoft Scripting Runtime
    
    Dim FSO As FileSystemObject
    Dim FSOFile As TextStream
    Dim FilePath As String
    Dim NoOfLoop As Integer
    
    FilePath = "c:\Vilas.txt" ' create a test.txt file or change this
    
    Set FSO = New FileSystemObject
     ' opens  file in write mode
    Set FSOFile = FSO.OpenTextFile(FilePath, 2, True)
     'loop round adding lines
    For NoOfLoop = 1 To 10
         ' write your code here
        FSOFile.WriteLine ("How are you " & NoOfLoop)
    Next NoOfLoop
    
    FSOFile.Close
End Sub

Saturday, September 8, 2012

Copying Multiple Charts as a Picture in Excel 2007

Using this technique throws an 'Application-defined or object-defined error' on the CopyPicture line.

ActiveSheet.ChartObjects.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Sheet2").Paste
 
ActiveSheet.ChartObjects.Copy    
Sheets("Sheet2").PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
 
 
However,  If I use the chart as a Shape object it works.
   
ActiveSheet.Shapes("Chart 6").CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Sheet2").Paste

This also works
ActiveSheet.Shapes("Chart 6").Copy
Sheets
("Sheet2").PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
 

Delink Excel Chart Data



There are occasions when you may want to break the link between a chart and its underlying data. Maybe you copied the chart and pasted it into another workbook, and opening the other workbook brings up the dialog box asking whether you want to update the links to another workbook:
There are ways you can make a copy of a chart that is not linked to the parent data.
Paste a Picture of the Chart
The easiest way to represent an unlinked chart is to copy a picture of the chart, then paste it where desired. If you select the chart, then hold down the Shift key while pulling down the Edit menu, a different set of choices will appear:
You are presented with a set of options. I generally use the options shown in this dialog box:
This picture can be pasted wherever you want it to appear. I use this method whenever pasting an Excel chart into another application, such as PowerPoint or Word.
The disadvantage to this technique is that the pasted picture is no longer an Excel chart. You can no longer format any of the chart elements (rescale the axes, change marker styles or colors, etc.). So I find this method unsuitable for use within Excel.
Unlink the Chart Series from Worksheet Data
It is possible to unlink a chart from its source data, without losing the ability to edit it as a chart. Refer to the example data and chart below:
The series formula data source is a reference to two ranges in the worksheet:
=SERIES(,Sheet1!$B$4:$B$15,Sheet1!$C$4:$C$15,1)
Notice that these two ranges are outlined in the sheet when the series is selected. The X Values (Sheet1!$B$4:$B$15) are outlined in purple, and the Y Values (Sheet1!$C$4:$C$15) are outlined in green.
Click in the formula bar, and press F9 (or Ctrl and the '=' key). The range references in the formula are converted to the arrays of data contained within the ranges, and the formula changes to this:
=SERIES(,{15.031454211777,8.1383538616338,10.9998151055087,
13.410691201042,10.6248254334369,10.6371217842496,11.3995831152254,
19.5323146287637,11.0885090885436,4.4742985867933,6.31296089699334,
3.81550937155244},{116855.089242441,239494.950013403,185567.581627991,
64882.6685031719,318350.869651478,129554.347163989,458906.997532184,
31286.2635801765,368058.120913394,304468.990447535,295618.685237458,
376771.217071102},1)
Your chart can now be pasted into any workbook, without generating links to the original data ranges.
You can convert just part of the series formula, if desired. Highlight part of the series formula, and press F9. If the Y Value reference (Sheet1!$C$4:$C$15) was selected, the formula now converts to this:
=SERIES(,Sheet1!$B$4:$B$15,{116855.089242441;239494.950013403;
185567.581627991;64882.6685031719;318350.869651478;129554.347163989;
458906.997532184;31286.2635801765;368058.120913394;304468.990447535;
295618.685237458;376771.217071102},1)
If there are multiple series to be delinked, you can use a macro like this to automate the process:
Sub DelinkChartFromData()
''' Thanks to Tushar Mehta
  Dim mySeries As Series

  ''' Make sure a chart is selected
  If Not ActiveChart Is Nothing Then
    ''' Loop through all series in active chart
    For Each mySeries In ActiveChart.SeriesCollection
      '''' Convert X and Y Values to arrays of values
      mySeries.XValues = mySeries.XValues
      mySeries.Values = mySeries.Values
      mySeries.Name = mySeries.Name
    Next mySeries
  End If
End Sub
Select a chart and run the DelinkChartFromData procedure.
Note: The problem with this approach is that Excel chokes on a series definition formula longer than around 1000 characters. But our formula obviously carries a lot of extra characters (16 per value), so I have modified the procedure above to excise the insignificant digits, in the procedure DelinkChartFromLotsOfData below:
Sub DelinkChartFromLotsOfData()
  Dim nPts As Long, iPts As Long
  Dim xArray As String, yArray As String
  Dim xVals As Variant, yVals As Variant
  Dim sxVal As String
  Dim syVal As String
  Dim ChtSeries As Series
  Dim iChars As Integer
  Dim sSrsName As String
  Dim sSrsFmla As String
  Dim iPlotOrder As Integer

  ''' Make sure a chart is selected
  If Not ActiveChart Is Nothing Then
    ''' Loop through all series in active chart
    For Each ChtSeries In ActiveChart.SeriesCollection
      nPts = ChtSeries.Points.Count
      xArray = ""
      yArray = ""
      xVals = ChtSeries.XValues
      yVals = ChtSeries.Values
      sSrsName = ChtSeries.Name
      iPlotOrder = ChtSeries.PlotOrder

      For iPts = 1 To nPts
        If IsNumeric(xVals(iPts)) Then
          ''' shorten numbers in X array (remove excess digits)
          iChars = WorksheetFunction.Max _
              (InStr(CStr(xVals(iPts)), "."), 5)
          ''' handle values in scientific notation
          sxVal = CStr(xVals(iPts))
          If InStr(sxVal, "E") = 0 Then
            sxVal = Left(sxVal, iChars)
          Else
            sxVal = Format(xVals(iPts), "0.000E+0")
          End If
          xArray = xArray & sxVal & ","

        Else
          ''' put quotes around string values
          xArray = xArray & """" & xVals(iPts) & ""","
        End If

        ''' shorten numbers in Y array (remove excess digits)
        iChars = WorksheetFunction.Max _
            (InStr(CStr(yVals(iPts)), "."), 5)

        If IsEmpty(yVals(iPts)) Or WorksheetFunction.IsNA(yVals(iPts)) Then
          ''' handle missing data - replace blanks and #N/A with #N/A
          yArray = yArray & "#N/A,"
        Else
          ''' handle values in scientific notation
          syVal = CStr(yVals(iPts))
          If InStr(syVal, "E") = 0 Then
            syVal = Left(syVal, iChars)
          Else
            syVal = Format(yVals(iPts), "0.000E+0")
          End If
          yArray = yArray & syVal & ","
        End If
      Next iPts

      ''' remove final comma
      xArray = Left(xArray, Len(xArray) - 1)
      yArray = Left(yArray, Len(yArray) - 1)

      ''' assign arrays to X and Y values
      ChtSeries.Values = "={" & yArray & "}"
      ChtSeries.XValues = "={" & xArray & "}"
      ChtSeries.Name = sSrsName
    Next ChtSeries
  End If
End Sub
Thanks to Andy Pope for correcting a glitch that was reversing the plot order in charts with multiple series, and for reminding me to handle missing values. Also thanks to PGC (Mr Excel MVP) for suggesting a problem with numbers stored in scientific notation.
Look at the new, svelter series formula:
=SERIES(,{15.03,8.138,10.99,13.41,10.62,10.63,11.39,19.53,11.08,4.474,
6.312,3.815},{116855,239494,185567,64882,318350,129554,458906,31286,
368058,304468,295618,376771},1)
Unlink Chart and Axis Titles from Worksheet Cells
One nice feature is the ability to link a chart title or axis title to the contents of a cell. To do this, select the title, press the equals key, then select the cell with your mouse, or type the fully qualified address of the cell after the equals sign in the formula bar. Fully qualified means start with the sheet name, in single quotes if the name has a space, then an exclamation point, and finally the cell address.
The following short macro breaks the links between the chart's titles and the worksheet cells, replacing the cell reference with the contents of the cell.
Sub UnlinkTitles()
  Dim iAx As Integer, iGrp As Integer
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      If .HasTitle Then .ChartTitle.Text = .ChartTitle.Text
      For iAx = 1 To 2
        For iGrp = 1 To 2
          If .HasAxis(iAx, iGrp) Then
            With .Axes(iAx, iGrp)
              If .HasTitle Then _
                  .AxisTitle.Text = .AxisTitle.Text
            End With
          End If
        Next
      Next
    End With
  End If
End Sub
 

Saturday, January 1, 2011

Full Screen Userform

Private Sub UserForm_Initialize()

    With Application

        .WindowState = xlMaximized

        Zoom = Int(.Width / Me.Width * 100)

        Width = .Width

        Height = .Height

    End With

End Sub

Tuesday, December 28, 2010

17 ways to Optimize VBA Code for FASTER Macros

Here is a summary of the article:
1. Analyze the Logic
2. Turn off ScreenUpdating
3. Turn off 'Automatic Calculations'
4. Disable Events
5. Hide Page breaks
6. Use 'WITH' statement
7. Use vbNullString instead of ""
8. Release memory of Object variables
9. Reduce the number of lines using colon(:)
10. Prefer constants
11. Avoid Unnecessary Copy and Paste
12. Clear the Clipboard after Paste
13. Avoid 'Macro Recorder' style code.
14. Use 'For Each' than 'Indexed For'
15. Use 'Early Binding' rather 'Late Binding'
16. Avoid using Variant
17. Use Worksheet Functions wherever applicable

Saturday, December 25, 2010

Excel Formula : Excel Formula & Macro Basics - Shared by Aditya Deo

Hi Friends,

This is really help full for all

Download

Macro : Check workbook open or not

Dim wBook As Workbook

On Error Resume Next
Set wBook = Workbooks(wfn & ".xls")

If wBook Is Nothing Then 'Not open
'MsgBox "Workbook is not open", _
vbCritical, "PSS Ltd"

Set wBook = Nothing
On Error GoTo 0
Else 'It is open
MsgBox "Workbook already in use by another user!" & Chr(10) & _
"Please try again later!", vbCritical, "PSS Ltd"
Set wBook = Nothing
On Error GoTo 0
End If