VBA
Make wykres transparent ;>
Friday, July 10th, 2009 | VBA | No Comments
Sub make_chart_transparent()
With ActiveChart
.ChartArea.Border.LineStyle = xlNone
.ChartArea.Interior.ColorIndex = xlNone
.PlotArea.Border.LineStyle = xlNone
.PlotArea.Interior.ColorIndex = xlNone
.Legend.Delete
End With
With ActiveChart.Axes(xlCategory)
.MajorTickMark = xlNone
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
End With
With ActiveChart.Axes(xlValue)
.MajorTickMark = xlNone
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
End With
For Each ax In ActiveChart.Axes
ax.HasMajorGridlines = False
ax.Border.ColorIndex = xlNone
Next
For Each sr In ActiveChart.SeriesCollection
sr.Border.LineStyle = xlNone
sr.HasDataLabels = False
Next
End Sub
Kolorowanie punktów danych w serii, wykres xls
Sub kolorowanie_serii()
Dim sr As Seriesodliczaj = val(InputBox(”Odliczaj co -n- kolumn”))
With ActiveChart
For Each sr In .SeriesCollection
For i = LBound(sr.Values) To UBound(sr.Values)
If i Mod odliczaj = 0 Then
sr.Points(i).Interior.ColorIndex = 35
End If
Next i
Next
End With
End Sub
VBA - Grupowanie kolorem
Wednesday, July 8th, 2009 | VBA | 1 Comment
Sub grupuj_kolorem()
Dim row As Range
Dim target As Range
Dim x As Integer
Dim licznik As Integercol_number = Val(InputBox(”Podaj numer kolumny do grupowania”))
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).SelectFor Each row In Selection.Rows
Set target = row.Range(Cells(1, col_number), Cells(1, col_number))
If target.Value <> target.Offset(-1, 0).Value Then
x = Abs(x - 1)
licznik = licznik + 1
End If
If x = 0 Then
row.Interior.ColorIndex = 35
Else
row.Interior.ColorIndex = 37
End If
Next
MsgBox (”Liczba Grup: ” & licznik)
End Sub
Excel - oznacz myszką wybrany wiersz
Thursday, June 4th, 2009 | VBA | No Comments
Private Declare Function GetKeyState Lib “user32″ (ByVal nVirtKey As Long) As Integer
Private Const kCapital = 20
Private Const kNumlock = 144
Private Const TargetCol = 3
Public Function CapsLock() As Boolean
CapsLock = KeyState(kCapital)
End Function
Private Function KeyState(lKey As Long) As Boolean
KeyState = CBool(GetKeyState(lKey))
End Function
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If CapsLock Then
For Each cell In Target.Cells
x = ActiveSheet.Cells(cell.Row, TargetCol)
x = Abs(x - 1)
ActiveSheet.Cells(cell.Row, TargetCol).Value = x
Next
End If
End Sub
Sub dodaj_formatowanie()
Range(”A1:D31″).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=”=$D1=1″
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub
VBA MsWord - Document Mining
Friday, February 13th, 2009 | Data Mining, VBA | No Comments
http://word.mvps.org/FAQs/MacrosVBA/index.htm
Ms Word - VBA - MVPS - FAQ
http://www.kayodeok.btinternet.co.uk/favorites/kbofficeword.htm
Using Visual Basic .NET from VBA to Serialize Word Documents as XML
http://msdn.microsoft.com/en-us/library/aa140276(office.10).aspx
Transforming Word Documents into the XSL-FO Format
http://msdn.microsoft.com/en-us/library/aa537167(office.11).aspx
XSL-FO is an intermediate form that results from applying an XSLT style sheet to an XML structured document. The XML-FO form describes how pages appear when presented to a reader, such as a Web browser. Currently, there are no readers that directly interpret an XSL-FO document. To interpret them, you must run them through a formatter, along with other data, such as graphics and font metrics, to create a final displayable or printable file. Possible formats for the resulting file include Adobe’s Portable Document Format (PDF) and Hypertext Markup Language (HTML).
When compared to Cascading Style Sheets (CSS), XSL-FO provides a more sophisticated visual layout model. You can use CSS to apply specific style elements to an XML or HTML document. By contrast, XSL-FO is a language for describing a complete document. It includes everything needed to paginate and format a document. Some of the formatting supported by XSL-FO, but not by CSS, includes right-to-left and top-to-bottom text, footnotes, margin notes, page numbers in cross-references, and more. Note that while CSS is primarily intended for use on the Web, XSL-FO is designed for broader use. As an example, you could use an XSL-FO document to lay out an XML document as a printed book. You could write a completely separate XSL-FO document to transform the same XML document into HTML.
XPath Tutorial - WC3 School
http://www.w3schools.com/xpath/default.asp
XPath is a language for finding information in an XML document. XPath is used to navigate through elements and attributes in an XML document.
XSL-FO Tutorial
http://www.w3schools.com/xslfo/xslfo_intro.asp
What XSL-FO is, and how to use XSL-FO to format your XML documents for output.
Projektowanie aplikacji VBA
Wednesday, December 10th, 2008 | VBA | No Comments
Microsoft Developer Network prezentuje przewodnik dotyczący projektowania aplikacji w środowisku OfficeXP. Projektowanie, debugowanie, obsługa błędów, na co zwrócic uwagę podczas projektowania interfejsu użytkownika, bezpieczeństwo i kilka podobnych zagadnień.
Warto rzucić okiem przed rozpoczęciem większego projektu, albo kiedy brakuje pomysłów “co jeszcze mogę poprawić” przed ostatecznym zakończeniem projektu.
http://msdn.microsoft.com/en-us/library/aa140974(office.10).aspx
Pętle w VBA - czy efektywne?
Friday, December 5th, 2008 | VBA | No Comments
Dlaczego należy unikać sotoswania pętli programując Excela w VBA? W wielu przypadkach wbudowane funkcje Excela wykonają zadanie znacznie szybciej niż funkcje oparte na iteriacji po wszystkich elementach zbioru.
Zanim stworzysz pętlę pomyśl czy któraś z funkcji: SpecialCells, AutoFilter, Find, AdvancedFilter lub Subtotals nie znajdzie zastosowania.