Difference between revisions of "Vba"

Revision as of 19:16, 29 April 2010

vba or visual-basic for applications is a scripting/programming language and development environment embedded within the Office suite software from microsoft. The syntax is equivalent to visual-basic, but the libraries and functions give direct access to the objects of the Office environment.

Sample code

Delete charts

Subroutine that deletes all charts present in excel sheet

'Rotina que apaga os gráficos existentes
Public Sub apagaGrafico()

    'Iterador de tipo "gráfico"
    Dim Chart As ChartObject

    'Para cada gráfico dentro da colecção de gráficos da ActiveSheet ...
    For Each Chart In ActiveSheet.ChartObjects

        '... apaga o gráfico.
        Chart.Delete

    Next

End Sub

Create chart

Subroutine that creates a nice graphic

'Rotina que cria um grafico na mesma folha que a chama
Sub makegraphic(lineSpan, colSpan As Integer, _
                strtLine, strtCol As Integer, _
                yScale As Single)
    
    Dim graphic As Chart                'Cria uma variavel de grafico
    Dim i As Single

    'Cria um gráfico dentro da active sheet
    Set graphic = ActiveSheet.ChartObjects.Add _
        (Left:=60, Width:=500, Top:=100, Height:=300).Chart
    
    With graphic
    
        .ChartType = xlLine
        .SetSourceData _
            Source:=Range(Cells(strtLine, strtCol), Cells(strtLine - 1 + lineSpan, strtCol - 1 + colSpan)), _
            PlotBy:=xlRows
        
        'define as abcissas
        .SeriesCollection(1).XValues = Range(Cells(strtLine - 1, strtCol), Cells(strtLine - 1, strtCol - 1 + colSpan))
        
        'Põe os eixos
        .HasTitle = True
        .ChartTitle.Characters.Text = "Crescimento de flores numa estufa"
        .Legend.Clear
        
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Characters.Text = "Domínio (m)"
        End With
        
        With .Axes(xlValue)
            .HasTitle = True
            .AxisTitle.Characters.Text = "Tamanho (cm)"
            .TickLabels.NumberFormat = "0.0"
            .MaximumScale = yScale
            .MinimumScale = 0#
        End With
       
    End With
    

End Sub

Delete text

'Rotina que apaga todos os valores na tabela de Excel
Public Sub ApagaTexto()

    Range(Cells(1, 5), Range("E1:E1").End(xlToRight).End(xlDown)).ClearContents

End Sub

External links

• Class of environmental modeling given at IST, 2008-2009