Innhold

Innledning

Notatene er et resultat av en kartlegging av Excel VBA og Objektmodellen.  Alle eksemplene i VBA er testet i Excel for Office 365 (ver. 16) og VBA 7.1, 64-bit.

Eksemplene er sannsynligvis hverken optimale eller feilfrie. Notatene er ikke en lærebok i Excel VBA, kune en grov kartlegging av området.

 

Lunde 06.08.2019

Inge Tang

1. Intro

Hva er Visual Basic for Application?

Visual Basic for Application (VBA) er et objekt-orientert programmeringsspråk. VBA brukes til å automatisere oppgaver i Microsoft Office applikasjoner som Excel, Word, Powerpoint m.fl. VBA består av programinstruksjoner som betegnes som prosedyrer eller makroer.

Hva kan VBA gjøre i Excel?

  • Utføre samme operasjon på flere ark eller arbeidsbøker
  • Utføre repeterende sekvenser av programinstruksjoner
  • Opprette nye Excel-funksjoner som kan brukes i applikasjonen
  • Reagere på hendelser (events) via brukerinteraksjon
  • Designe brukergrensesnitt(form)
  • Kommunisere med bl.a. Microsoft Office applikasjoner

Hvorfor lære VBA?

  • VBA kan redusere overflødige operasjoner i arbeidsflyten
  • VBA kan forsterke tekniske ferdigheter i bruk av Excel og i analyser
  • VBA er en utmerket introduksjon til programmering av datamaskiner i et kjent miljø
  • VBA er en verdifull ferdighet i jobbmarkedet

Historien om VBA

VBA er en moderne dialekt av BASIC-programmering introdusert i 1960-årene

  • I 1991 utga Microsoft Visual Basic (VB) programmeringsspråk
  • I 1994 utga Microsoft den første versjonen av VBA med Excel 5
  • Både VB og VBA er inspirert av Basic, men de er fullstendig ulike programmeringsspråk

Forutsetninger i dette kurset:

  • VBA er installert sammen med Excel
  • Anbefaler her Excel 2016 for Windows
  • Problemer med bakoverkompabilitet kan forekomme
  • Støtter ikke Excel for Mac

Bør beherske følgende på et middels nivå:

  • Excel-funksjoner (SUMIF (SUMMERHVIS), COUNTIF (ANTALL.HVIS), VLOOKUP (FINN.RAD))
  • Pivottabeller (PivotTables),diagrammer (Charts) og grafer (Graphs)
  • Datatyper (number,text,date,boolean)

Aktivere Utvikler på båndet

  • Utvikler-menyen (Excel VBA) er ikke aktivert på båndet som standard.
  • Dette må gjøres ved å høyreklikke på visning å velge Tilpass båndet… og markere
  • Utvikler-menyen under hovedfaner.
  • Utforsk Utviklermenyen, åpne VB og utforsk verktøylinjer/menyer/prosjekt

Excel filtyper

  • Excel Arbeidsbok (xlsx), standard lagringsformat uten makroer
  • Makroaktivert Excel-arbeidsbok (xlsm),lagringsformat for arbeidsbok med eller uten makroer
  • Binær Excel-arbeidsbok (xlsb), binært lagringsformat for arbeidsbok med eller uten makroer
  • Standard lagringsformat kan endres under Alternativer->Lagre->Lagre arbeidsbøker

Makrosikkerhet

  • Innstillinger for makrosikkerhet gjøres via valget Makrosikkerhet på båndet
  • Utforsk Klareringssenter.
  • Velg et alternativ vedr. makrosikkerheten under Makroinnstillinger og vurder å tilpasse Klarerte plasseringer.

Registrer makro

  • Denne kommandoen gjør at tastetrykk, menyvalg etc registreres og konverteres til VBA-kode.
  • Makroen gis et navn som kort beskriver hva makroen utfører.
  • Navnet skal begynne med en bokstav,ikke innholde mellomrom eller cellereferanse.
  • Makroen kan aktiveres med en tastekombinasjon, Ctrl+. Her bør en bruke tastekombinasjoner som ikke overskriver standard tastekombinasjoner i Excel.
  • Det kan f.eks. bruke tastekombinasjonen Ctrl+Shift+Tast.

Makroen kan lagres i

  • Denne arbeidsboken
  • Global arbeidsbok
  • Ny arbeidsbok

Makroinnspilling

  • Makroen kan gis en nærmere beskrivels av hva den utfører
  • Makroinnspilling medfører mye overflødige instruksjoner og er ikke egnet til reelle makroer
  • Makroinnspilling egner seg mer til å utforske VBA-kode for læringsformål

Eksempel på makro med svært mye overflødige instruksjoner ved formatering

Sub SkrivogFormater()

‘ Makroen SkrivogFormater skriver inn teksten Makrotest i celle A1 i arket Makrotest og formaterer cellen.

Sheets(«Makrotest»).Select

Range(«A1»).Select

Selection.FormulaR1C1 = «Makrotest»

With Selection.Font

.Name = «Calibri»

.Size = 24

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

Selection.Font.Bold = True

Columns(«A:A»).EntireColumn.AutoFit

With Selection.Font

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

End With

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 49407

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub

Redigert makro som utfører det samme som makroen over

Sub SkrivogFormaterNy()

‘ Makroen SkrivogFormaterNy skriver inn teksten Makrotest i celle A1 i arket Makrotest og formaterer cellen.

Sheets(«Makrotest»).Select

Range(«A1»).Select

Selection.FormulaR1C1 = «Makrotest»

With Selection.Font

.Size = 24

.Bold = True

End With

Columns(«A:A»).EntireColumn.AutoFit

With Selection.Interior

.Color = 49407

End With

End Sub

Absolutte vs relative referanser

  • Ved registrering av makroer bruker Excel normalt absolutt referanse til celler.
  • (Dette er standard innstilling for registrering av makroer) Dette er av og til ikke den riktige metoden.
  • Den andre metoden er å bruke relative referanser i forhold til aktiv celle.
  • Ved absolutte referanser, vil Excel bruke eksplisitte verdier for cellereferansen. Ved relative referanser bruker Excel referanser relativt til aktiv celle.

Eksempel

Sub Absolutt()

‘ Absolutt registrering

‘ Skriver inn en tekst i celle D4.

Sheets(«Makrotest»).Select

Range(«D4»).Select

Selection.FormulaR1C1 = «Absolutt»

End Sub

 

Sub Relativ()

‘ Relativ registrering

‘ Skriver inn tekst i en celle -7 rader og 3 kolonner forskjøvet (offset) i  forhold til ‘aktiv celle

Sheets(«Makrotest»).Select

Cells(10, 3).Select

ActiveCell.Offset(-7, 3).Range(«A1»).Select

Selection.FormulaR1C1 = «Relativ»

End Sub

2. Grunnleggende om Excel’s Objektmodell

  • VBA er et objektorientert programmeringsspråk (OOP)
  • Objektorientert programmering er et utviklingsparadigme som betrakter en applikasjon som en samling objekter som interagerer med hverandre
  • Et objekt er en entitet, en datastruktur, en ting
  • Et objekt har egenskaper (properties) og metoder

Analogier

  • Objekter som substantiver
  • Egenskaper som adjektiver
  • Metoder som verb

Eksempler

  • En bok. En bok skrevet av en forfatter. En bok skrevet av en forfatter kan leses. (Bok, Forfatter, Lese)
  • Et eple. Et eple av en bestemt type. Et eple av en besemt type som kan spises. (Eple, Type, Spise)
  • En ball. En ball med en gitt vekt. En ball med en gitt vekt som selges. (Ball, Vekt, Selge)

Ulike objekter kan ha felles / delte egenskaper / metoder.

Syntaks for egenskaper og metoder (Prikknotasjon)

Aksjon Syntaks Syntaks
Hent en verdi for egenskap Objekt.Egenskap Bok.Forfatter
Sett en verdi for egenskap Objekt.Egenskap=NyVerdi Bok.Pris=14,90
Bruk en metode Objekt.Metode Bok.Selg

Objektsamlinger i virkeligheten

Mange av Excel’s objekter tilhører kolleksjoner/samlinger som i det vesentligste er grupper med like objekter. På samme måte som huset du bor i tilhører et nabolag som er en samling hus. Hvert nabolag tilhører en samling nabolag som kalles en by. Excel betrakter selv samlinger som objekter.

I hvert Workbook-objekt har vi en samling av Worksheets. Samlingen av Worksheets er et objekt som du kan kalle opp med VBA. Hvert Worksheet i arbeidsboken finnes i samlingen av Worksheets.

Hvis du vil referere til et Worksheet i Worksheets-samlingen kan du referere til dette med posisjonen i samlingen, et indeksnummer som starter på 1, eller ved navnet som tekst i dobble anførselstegn. Hvis du kjører følgende to linjer med kode i en arbeidsbok som kun har et ark med navnet MittArk, gjør begge det samme.

Worksheets(1).Select

Worksheets(«MittArk»).Select

Hvis du har to ark i den aktive arbeidsboken som har navnene MittArk og DittArk i denne rekkefølgen, kan du referere til det andre arket ved følgende instruksjoner:

Worksheets(2).Select

Worksheets(«DittArk»).Select

Hvis du vil referere til et ark MittArk i en arbeidsbok MinBok, som ikke er aktiv, må du kvalifisere referansen til arket og til arbeidsboken:

Workbooks(«MinBok.xls»).Worksheets(«MittArk»).Select

Et objekt som en egenskap ved et annet objekt

Et objekt kan ha egenskaper (properties) som refererer tilnet annet objekt med egenskaper (properties) som igjen kan være referanse til et annet objekt osv. Det betyr at vi har en objektstruktur med foreldre-barn-barnebarn osv. Prikknotasjon skiller referansene.

Objektet Bok har egenskapene

Tittel

Forfatter

Sidenatall

Pris

 

Metode: Les

 

Objektet Forfatter har egenskapene

Navn

Alder

Inntekt

 

Metode: Skriv

 

Egenskapen Forfatter i objektet Bok hentes fra objektet Forfatter:

Bok.Forfatter.Navn

Metoden Skriv hentes fra objektet Forfatter:

Bok.Forfatter.Skriv

 

Eksempel fra Excel

  • Et Range-objekt inneholder både egenskaper og metoder
  • Egenskapen Range.Font returnerer et Font-objekt
  • Font-objektet har også egenskaper og metoder

Eksempel på hieraki av objekter/egenskaper med prikk.notasjon

Sub Range_Objektet()

Sheets(«Makrotest»).Select

With Range(«A1»)

Value = «RangeObjekt»

Font.Name = «Times New Roman»

.Font.Size = «24»

Interior.Color = 244

End With

End Sub

Hierarkiet av objekter i VBA kalles Excels ObjektModell

  • På toppen av hierarkiet ligger objektet Application. Dette objektet representerer hele Excel.
  • En egenskap med Application er Workbooks som inneholder en samling av objekter, Workbook (entall).
  • Objektet Workbook har egenskapen Worksheets som inneholder en samling av objekter Worsksheet (entall).
  • Objektet Worsksheet har egenskapen Range som referer til en eller flere celler.
  • Sammenlikner strukturen med et slektstre.

Sub Kvalifisering_Indeks()

Debug.Print Application.UserName

Debug.Print Application.Name

Debug.Print Application.Version

MsgBox («Applikasjon: » & Application.Name & vbNewLine & _

«Versjon: » & Application.Version & vbNewLine & _

«Bruker: » & Application.UserName)

Range(«A1»).Value = «Hei verden»

‘Tilgang til objekter via indekser eller navn

Debug.Print Application.Workbooks(1).Worksheets(«Makrotest»).Range(«A1»).Value

Debug.Print Workbooks(1).Worksheets(«Makrotest»).Range(«A1»).Value

Debug.Print Worksheets(«Makrotest»).Range(«A1»).Value

Debug.Print Range(«A1»).Value

End Sub

Tilgang til objekter kun via navn

Sub Kvalifisering_Navn()

‘Bruker navn for å kvalifisere

Workbooks(«VBA_Kurs_1»).Worksheets(«Makrotest»).Range(«Range_A1»).Value = «Bruker her kun navngiving»

Debug.Print _Workbooks(«VBA_Kurs_1»).Worksheets(«Makrotest»).Range(«Range_A1»).Value

Debug.Print Range(«Range_A1»).Value

End Sub

Standard egenskaper

Ved referanse til et gitt objekt bruker Excel standaregenskapen. Ønsker du se verdien til Rangeobjekt kan du enten bruke Range(«A1»).Value eller Range(«A1»). Begge gir samme resultat, i den siste utelates referansen til egenskapen Value, Excel henter standardegenskapen (default) Kan være en fordel å ikke bruke standardverdiene pga lesbarhet.

Sub Standard_Egenskaper()

Sheets(«Makrotest»).Select

Debug.Print Range(«A1»).Value

‘Standardverdi for Range

Debug.Print Range(«A1»)

Debug.Print Application.Name

‘Standardverdi fo Application

Debug.Print Application

End Sub

Egenskapen Name

Egenskaper kan være lesbare og/eller skrivbare. Det betyr at noen egenskaper kun kan leses og noen egenskaper er skrivbare. Det betyr også at noen egenskaper kan både skrives til og leses.

Sub Egenskapen_Name()

‘Name-egenskapen for Arbeidsbok er kun lesbar

Debug.Print Workbooks(1).Name

Debug.Print Workbooks(«VBA_Kurs_1.xlsm»).Name

‘Dette gir en feilmelding ved kompilering

Workbooks(1).Name = «En ny Bok.xlsm»

‘Name-egenskapen for Ark kan leses og skrives til/tilordnes

Debug.Print Worksheets(2).Name

Worksheets(2).Name = «NyPortal»

Debug.Print Worksheets(2).Name

End Sub

3. Visual Basic for Application Editor

Konfigurering av VBA Editor

Menu -> Tools -> Options

Tab -> Editor

Code Settings

Nei: Auto Syntax Check, kan forsinke og hemme arbeidet. VBA vil allikevel visesyntaksfeil.

Ja : Require Variable Declaration, krever deklarasjon av variabler

Ja : Auto List Members, viser forslag som logisk kompletterer instruksjonen ved kursoren

Ja : Auto Quick Info, viser info når du skriver eller høyreklikk for å få info

Ja : Auto Data Tips, nyttig ved debugging og mouseover viser verdier

Ja : Auto Indent,innrykk som gir et bedre visuelt inntrykk

Window Settings

Ja : Drag-and-Drop Text Editing, merk tekst, dra og slipp teksten

Ja : Default to Full Module View,viser alle prosedyrene i en sammenhengende liste

Ja : Procedure Separator, skiller prosedyrene med en vannrett linje

 

Tab -> Editor Format

 

Code Colors

Text  Velg hvilken tekst
Foreground  Tekstfarge
Background  Bakgrunnsfarge
Indicator  Velg type indikator
Font Velg font, monospace pga lesbarhet
Size Velg størrelse

 

Margin Indicator Bar  : Grå marg i venstre kant, nyttig ved debugging

Sample                          : Viser resultat av valgene over

 

Tab -> General

Form Grid Settings

Ja : Show Grid. Grid Units: Points. Width: 6 Height: 6

Ja : Align Controls to Grid

 

Edit and Continue

Nei: Notify Before State Loss

Error Trapping

Nei: Break on All Errors

Nei: Break in Class Module

Ja : Break on Unhandled Errors

Compile

Ja : Compile On Demand

Ja : Background Compile

 

Tab -> Docking

Dockable

Ja : Immediate Window

Ja : Locals Window

Ja : Watch Window

Ja : Project Explorer

Ja : Properties Window

Nei: Object Browser

Opprette og slette en prosedyre

Project Explorer -> Microsoft Excel Objects -> Høyreklikk -> Insert-> Module

Endre navn på modul -> Properties Window

Option Explicit: Krever deklarasjon av variabler

Menu -> Insert -> Procedure… ->

 

Sub MinFoersteProsedyre()

‘Ingen instruksjoner -> Likevel en gyldig prosedyre

End Sub

Modulen slettes ved å høyreklikke modulnavnet i Project Window…

Immediate Window og Debug.Print Method

  • Immediate Window brukes til forhåndsvisning av VBA-kode, enkle instruksjoner

Eksempler i Immediate Window:

Spørsmålstegnet eller kommandoen Print brukes når vi ønsker å se informasjon

? 2 + 2

? Range(«A1»).Value

Print 2 + 2

Print Range(«A1»).Value

Vi kan også tilordne verdier, uten spørsmålstegn

Range(«A2″).Value=»Hallo»

En annen metode er å bruke metoden Debug.Print i en prosedyre

Sub PrintTingTilKonsollet()

Debug.Print Range(«A1»).Value

Range(«A1»).Value = «Debug er ok!»

Debug.Print Range(«A1»).Value

End Sub

MsgBox Method

Eksempel på bruk av metoden MsgBox i Immediate Window

MsgBox «Hei»

MsgBox «Ha det»

MsgBox «Inge » & «Tang»

MsgBox «Tall: » & 1 + 2

Eksempel på bruk av metoden MsgBox i en prosedyre

Sub VisMeldinger()

MsgBox «Hei»

MsgBox «Ha det»

MsgBox «Inge » & «Tang»

MsgBox «Summen av 1+2=» & 1 + 2

End Sub

Kommentarer i VBA

  • Kommentarer merkes med ‘ og ignoreres av VBA

Sub KommentarEksempel()

‘ En kommentar til prosedyren/metadata

MsgBox 1 + 1 ‘En kommentar om meldingen

‘ Debug.Print «Hei»  Instruksjonen er kommentert ut

End Sub

4. Objects and Methods

Metoder uten argumenter, eksempler

Sub Metoder_Uten_Argumenter()

Workbooks.Add

Workbooks(«Kurs»).Close

Workbooks(«VBA_Kurs_1.xlsm»).Save

Workbooks(«VBA_Kurs_1.xlsm»).Close

Worksheets(«Makrotest»).Select

Range(«A1:E4»).Clear

End Sub

Metoder med argumenter, eksempler

Sub Metoder_Med_Argumenter()

Worksheets(«Makrotest»).Select

‘https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect

Worksheets(«Makrotest»).Protect Password:=»access»

Worksheets(«Makrotest»).Unprotect Password:=»access»

End Sub

Metoder med flere argumenter, eksempler

Sub Metoder_Med_Flere_Argumenter()

Workbooks.Open Filename:=»VBA_Kurs_Kopi.xlsm», ReadOnly:=True

End Sub

Typename Metoden

Sub Type_Navn()

‘Metoden returnerer datatypen for argumentet

Debug.Print TypeName(«Inge»)

Debug.Print TypeName(1.234)

Debug.Print TypeName(Workbooks)

Debug.Print TypeName(Workbooks(1))

Debug.Print TypeName(Worksheets)

Debug.Print TypeName(Worksheets(1))

Debug.Print TypeName(Worksheets(«Makrotest»))

Debug.Print TypeName(Worksheets(«Makrotest»).Range(«A1»).Value)

If TypeName(Worksheets(«Makrotest»).Range(«A1»).Value) = «Empty» Then

MsgBox («Cellen er tom»)

Else: MsgBox («Datatype: » & TypeName(Worksheets(«Makrotest»).Range(«A1»).Value))

End If

‘Metoden kan feks brukes i Forms ved validering av input

‘Loop for datatyper fra en liste i kolonne A. Datatypen skrives i kolonne B

Dim i As Long

Dim DataType As String

Range(«A1»).Select

Do While TypeName(ActiveCell.Value) <> «Empty»

DataType = TypeName(ActiveCell.Value)

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = DataType

ActiveCell.Offset(1, -1).Activate

Loop

Range(«A1:B4»).HorizontalAlignment = xlLeft

End Sub

5. Variabler og datatyper

Syntaks Tips

Litt om å gjøre instruksjonene mer lesbar

Sub Syntaks_Demo()

‘En instruksjon pr linje

‘Bryt opp lange instruksjoner med _ (underscore)

‘Innrykk med Tab eller fra meny

End Sub

Anførselsttegn/Sitattegn «

Sub Romeo_Said()

‘Tekst: Romeo said «I love you Juliet»

Debug.Print «Romeo said «»I love you Juliet»»»

‘Dobbelt anførselstegn vises som et tegn

End Sub

Deklarasjon av variabler og tilordninger

  • Variabel som plassholder med et gitt navn og datatype

Datatyper i VBA

Byte

Boolean

Integer

Long (long integer)

Single

Double

Currency

Decimal

LongLong (Kun 64-bit)

Date

Object

String  (variable – Length)

String  (Fixed – Length)

Variant (with numbers)

Variant (with characters)

User-defined

Restriksjoner for variabelnavn

  • Variabelnavn kan ikke begynne med et nummer eller underscore (_)
  • Variabelnavn kan ikke inneholde mellomrom eller punktum
  • Variabelnavn kan ikke inneholde følgende tegn !, %, ?, #, $, +, –
  • Variabelnavn kan ikke være et reservert ord
  • Variabelnavn må være unike innen samme prosedyre
  • Variabelnavn kan ikke være lengre enn 255 tegn
  • Variabelen lever så lenge prosedyren lever (er aktiv)
  • Hvis Option Explicit, variabler skal deklareres på forhånd
  • Tilordning ved bruk av likhetstegn =
  • VBA er ikke case-sensitive

Sub Variabler()

Dim Alder As Integer ‘Dimensjonering av variabelen Alder med dataypen Integer

Alder = 35 ‘Alder tilordnes verdien 35

Debug.Print Alder

MsgBox Alder + 2 ‘Utføre en operasjon på variabelen Alder, legger 2 til Alder

End Sub

Deklarasjon av multiple variabler

Sub Flere_Variabler()

‘ Deklarasjoner

‘Alternativ 1

Dim Alder As Integer

Dim LottoTall As Integer

Dim AntallArk As Integer

‘Alternativ 2

Dim Alder As Integer, LottoTall As Integer, AntallArk As Integer

‘Feil

Dim Alder, LottoTall, AntallArk As Integer

End Sub

Option Explicit

  • Hvis Option Explicit angis må den ligge før alle prosedyrene i modulen.
  • Når Option Explicit er angitt må alle variabler deklareres med Dim, Private, , Redim eller Static.

 

  • Hvis variabelen ikke er deklarert vil det oppstå en feilmelding ved kompilering.
  • Hvis du ikke bruker Option Explicit vil alle ikke-deklarerte variabler være av typen Variant med mindre standardtypen er spesifisert forskjellig med en Deftype instruksjon.
  • Bruk Option Explicit for å unngå feilstaving av eksisterende variabelnavn eller for å unngå uklarheter i programkoden hvor variablens rekkevidde (scope) er uklar.

Sub Ikke_Deklarert()

‘ Gir feilmelding ved kompilering

MsgBox «Alder :» & Din_Alder

End Sub

Heltall, datatypene Byte, Integer og Long

Verdiområde:

Byte     0 til 255

Integer  -32768 til 32767

Long     -2 147 484 648 til 2 147 483 647

LongLong -9 223 372 036 854 775 808 til 9 223 372 036 854 775 807 kun 64-bit

Sub Tilordne_Noen_Heltall()

Dim b As Byte

Dim i As Integer

Dim l As Long

Dim ll As LongLong

b = 4

i = 25000

l = 1000000

ll = 1.21212121212121E+18

MsgBox «b er: » & b & vbNewLine & «i er: » & i & vbNewLine _

& «l er: » & l & vbNewLine & «ll er: » & ll & » (Kun 64-bit)», , «Heltall i Excel»

End Sub

Matematiske operasjoner

Grunnleggende operasjoner

Addisjon +
Subtraksjon
Multiplikasjon *
Divisjon /
Eksponent ^
Modulo Mod

Sub Morro_med_Matte_Heltall()

Dim a As Long

Dim b As Long

a = 4

b = 3

MsgBox «Addisjon: » & a & «+» & b & «= » & a + b

MsgBox «Subtraksjon: » & a & «-» & b & «= » & a – b

MsgBox «Multiplikasjon: » & a & «*» & b & «= » & a * b

MsgBox «Divisjon: » & a & «/» & b & «= » & a / b

MsgBox «Eksponent: » & a & «^» & b & «= » & a ^ b

MsgBox «Modulo: » & a & » Mod » & b & «= » & a Mod b

End Sub

Desimaltall, datatypene Single og Double

Verdiområde:

Single -3.4028235E+38 til -1.401298E-45 for negative verdier

og fra 1.401298E-45 til 3.4028235E+38 for positive verdier.

 

Double -1.79769313486231E308 til -4.94065645841247E-324 for negative verdier

4.94065645841247E-324 til 1.79769313486232E308 for positive verdier.

Sub Moro_med_Matte_Desimaltall()

Dim Pi As Double

Pi = 3.14159

Dim Radius As Long

Radius = 5

Dim Omkrets As Double

Omkrets = 2 * Pi * Radius

MsgBox «Datatype for Pi=» & TypeName(Pi) & vbNewLine & _

«Datatype for Radius=» & TypeName(Radius) & vbNewLine & _

«Omkrets (2 * Pi * Radius)=» & Omkrets & vbNewLine & _

«Datatype for Omkrets=» & TypeName(Omkrets)

End Sub

Tekst, datatypen String

To typer

  • Fast lengde, verdiområde angis ved deklarasjon med asteriks og lengde (* xx)
  • Variabel lengde, verdiområde 0 til ca 2 milliarder Unicode tegn

Sub To_Typer_String()

Dim Variabelt_Navn As String

Dim Fast_Navn As String * 10

Variabelt_Navn = «Petter»

Fast_Navn = «Petter»

Debug.Print Variabelt_Navn ‘Gjengir alle tegnene

Debug.Print Fast_Navn ‘Gjengir alle tegnene <=10

Variabelt_Navn = «Petter Thommassen»

Fast_Navn = «Petter Thommassen»

Debug.Print Variabelt_Navn ‘Gjengir alle tegnene

Debug.Print Fast_Navn ‘Avkorter til 10 tegn

End Sub

Logisk, datatypen Boolean

  • Verdiområde True eller False (Sann eller Usann)

Sub Morro_med_Boolean()

‘Eksempler på bruk av Logiske operatorer på to variabler p og q

Dim p As Boolean

p = True

Dim q As Boolean

q = True

 

Debug.Print (p Or q)

Debug.Print (p And Not q)

Debug.Print (p Or Not q)

Debug.Print (Not p And Not q)

Debug.Print (p Or Not q)

Debug.Print (p Xor q)

Debug.Print (p Or q) And Not (p Xor q)

End Sub

Dato/Tid, datatypen Date

  • Verdiområde 0:00:00(midnatt) 1.Januar 0001 til 11:59:59 31.Desember 9999
  • Må omsluttes av # (Hashtag)
  • Bør bruke 4-siffret årstall

Sub Moro_med_Dato_og_Tid()

Dim Fodselsdag As Date

Fodselsdag = #9/25/1953#

Debug.Print Fodselsdag

Worksheets(«Makrotest»).Select

Range(«E1»).Value = Fodselsdag

Dim Lunch As Date

Lunch = #12:30:00 PM#

Range(«E2»).Value = Lunch

Dim En_Hendelse As Date

En_Hendelse = #9/25/1953 1:00:00 PM#

Range(«E3») = En_Hendelse

End Sub

Datatypen Variant

  • En spesiell datatype som kan inneholde hvilke som helst data unntatt String med fast lengde.
  • Variabler som ikke er typedeklarert vil være av typen Variant
  • Variabler kan deklareres som Variant når det usikkert hvilken datatype variabelen vil inneholde

Sub Moro_med_Variant()

Dim Min_Variant As Variant

‘Alternativt uten å angi datatype

Dim Min_Variant

Min_Variant = True

Debug.Print TypeName(Min_Variant)

Min_Variant = 5

Debug.Print TypeName(Min_Variant)

Min_Variant = 4.5

Debug.Print TypeName(Min_Variant)

Min_Variant = #9/25/1953#

Debug.Print TypeName(Min_Variant)

Min_Variant = «Kamelon»

Debug.Print TypeName(Min_Variant)

End Sub

Datatypen Object

  • Addresser som refererer til objekter.
  • Ved bruk av Set kan en variabel som er deklarert som Object ha hvilke som helst objektreferanse tilordnet.

Sub Object_Variabler()

Dim wb As Workbook

Dim ws As Worksheet

Dim rng As Range

Worksheets(«Makrotest»).Select

Set wb = Workbooks(1)

Set ws = wb.Worksheets(1)

Set rng = ws.Range(«E1»)

Debug.Print wb.Name

Debug.Print ws.Name

Debug.Print rng.Value

End Sub

Standardverdier for deklarerte variabler

  • Avhenger av datatypen
String  Blank
Numeriske  0
Boolean  False
Date  00.00.00

 

Sub Standardverdier()

Dim s As String

Dim b As Byte

Dim i As Integer

Dim l As Long

Dim ll As LongLong

Dim d As Double

Dim Dato As Date

Dim bool As Boolean

‘Ingen tilordninger

‘Viser standardverdier

Debug.Print s

Debug.Print b

Debug.Print i

Debug.Print l

Debug.Print ll

Debug.Print d

Debug.Print Dato

Debug.Print bool

End Sub

6. Prosedyrer

Scope

  • Levetid/rekkevidden/grensene for en variabel

Tre typer

Macroscope

Modulescope

Applicationscope

Dim MittNummer As Long ‘Variabelen er tilgjengelig i hele modulen, Modulescope

MittNummer As Long ‘variabelen er tilgjengelig i alle moduler, Applicationscope

Sub Prosedyre_A()

Dim MittNummer As Long ‘variabelen er tilgjengelig i kun Prosedyre_A, Macroscope

Debug.Print MittNummer

End Sub

 

Sub Prosedyre_B()

Dim MittNummer As Long ‘variabelen er tilgjengelig i kun Prosedyre_B, Macroscope

Debug.Print MittNummer

End Sub

Prosedyrekall fra en annen prosedyre

  • Refaktorering eller omstrukturering av kode eller (en: refactoring) er en teknikk for å trinnvis forbedre kvaliteten på programkode
  • Formålet er å omstrukturere kode for å den enklere og dermed mer lettlest, lettere å vedlikeholde og videreutvikle
  • En viktig detalj med omstruktureringen er at funksjonaliteten utad skal være identisk etterpå, det vil si at oppførselen på koden ikke skal endre seg. Derfor bør omstrukturering skje trinnvis i mindre enkle endringer
  • Ettersom omstrukturering ikke tilfører noe nytt til koden er dette et ganske mekanisk arbeide som det finnes bra verktøy for å automatisere.

(Wikipedia)

Sub Steg_En()

Debug.Print «Hei dette er første steg»

End Sub

 

Sub Steg_To()

Debug.Print «Hei dette er andre steg»

End Sub

 

Sub Master()

‘Opprinnelig kode

Debug.Print «Hei dette er første steg»

Debug.Print «Hei dette er andre steg»

‘Refaktorering til to mindre prosedyrer Steg_En og Steg_To

Call Steg_En

Call Steg_To

End Sub

Prosedyrer med argumenter

Eksempler på prosedyrer med argumenter/parametere

Sub Logg_Melding_til_Konsoll(Verdi As Long)

‘Skriver innholdet av variabelen Verdi til konsollet

Debug.Print Verdi

End Sub

 

Sub Vis_Melding(Verdi As Long)

‘Skriver innholdet av variabelen Verdi til meldingsboksen

MsgBox Verdi

End Sub

 

Sub Skriv_Verdi_til_Ark(Verdi As Long)

‘Skriver innholdet av variabelen Verdi til cellen G1

Sheets(«Makrotest»).Select

Range(«G1»).Value = Verdi

End Sub

 

Sub Skriv_Beregning_til_Ark()

Dim Matte_Beregning As Long

Matte_Beregning = 5 * 4 * 3 * 2 * 1

‘Variabelen Matte_Beregning (Macroscope) er nå argument i prosedyrene under

Logg_Melding_til_Konsoll (Matte_Beregning)

Vis_Melding (Matte_Beregning)

Skriv_Verdi_til_Ark (Matte_Beregning)

End Sub

 

Rekkevidden (Scope) for en prosedyre( vs. Private)

  • Standardinnstillinger for prosedyrer er at instruksjonen ikke er angitt foran prosedyren
  • Private prosedyrer er tilgjengelige kun innenfor modulen den er kodet i. Vises ikke i Makro-oversikten i arbeidsboken

Sub Implisitt_()

MsgBox 1 + 1

End Sub

 

Private Sub Eksplisitt_Private()

Sheets(«Makrotest»).Select

Range(«G2»).Value = «Private prosedyre kallt fra en  prosedyre»

End Sub

 

Sub Eksplisitt_()

Call Eksplisitt_Private

MsgBox 1 + 1

End Sub

Exit Sub Instruksjonen

  • Terminerer øyeblikkelig Sub prosedyren hvor Exit Sub forekommer
  • Programutførelsen fortsetter med instruksjonen etter instruksjonen som kalte Sub prosedyren
  • Exit Sub kan kun brukes i Sub prosedyrer

Sub Exit_Tidlig()

Debug.Print 1

Debug.Print 2

Exit Sub

Debug.Print 3

Debug.Print 4

End Sub

Konstanter

  • Variabler som ikke endrer verdi under utførelsen av prosedyren.

Sub Konstanter()

Const Pi As Double = 3.14159

Const cUker As Long = 52

Const cSkatte_Sats = 0.32

End Sub

 

Sub Yrke()

Debug.Print «Jeg er en » & JobbTittel

‘Se i starten av modulen: Const JobbTittel As String = «VBA Guru» ‘Modulescope ev ‘Applicationscope

End Sub

Predefinerte konstanter (enumeration)

  • Koblingen mellom et begrep og en numerisk verdi. F.eks koblingen mellom månednavn og månednummer Januar 1, Februar 2 osv

Sub Endre_Orientering()

Dim ps As PageSetup

Set ps = Worksheets(«Makrotest»).PageSetup

‘Name=xlPortrait Value=1

‘Name=xlLandscape Value=2

If ps.Orientation = xlLandscape Then

ps.Orientation = xlPortrait

Else: ps.Orientation = xlLandscape

End If

End Sub

7. Object Deep Dive

Application Object

  • Topp/globalt nivå i objekthierarkiet
  • Oppgaven er å bestemme hvilket objekt i hierarkiet som skal adresseres

Sub Moro_med_Application()

‘Eksempler på addressering av Applicationobjektet

Worksheets(«Makrotest»).Select

Debug.Print Application.Name

Debug.Print Application.Version

Debug.Print Application.Path

Debug.Print Application.UserName

Debug.Print Application.ActivePrinter

Application.Quit

End Sub

Application.DisplayAlerts Property

  • Aktivere eller deaktivere advarsler i Excel

Sub Lukk_Aktiv_Arbeidsbok()

‘Dekaktiverer advarsler

Application.DisplayAlerts = False

If ActiveWorkbook.Name = «Test_Bok.xlsm» Then

Workbooks(«Test_Bok.xlsm»).Close

Else: MsgBox «Test_Bok.xlsm er ikke aktiv», , «Lukke arbeidsbok»

End If

‘Aktiverer advarsler

Application.DisplayAlerts = True

End Sub

Workbooks.Count og Worksheets.Count Properties

Sub Vis_Arbeidsboeker_og_Ark()

Dim wb As Long ‘Iterator for arbeidsbøker

Dim ws As Long ‘Iterator for ark

‘Skriver ut navn på alle åpne arbeidsbøker og tilhørende ark i Immediate Window

For wb = 1 To Workbooks.Count

Workbooks(wb).Activate

Debug.Print «Arbeidsbok: » & wb & » » & Workbooks(wb).Name

For ws = 1 To Worksheets.Count ‘Alternativt bruke objektet Sheet som ‘inkluderer Chart Sheet

Debug.Print «Ark » & ws & » » & Worksheets(ws).Name

Next ws

Debug.Print

Next wb

End Sub

Workbooks.Open Method og Workbook.Path Property

Sub Apne_en_Arbeidsbok()

Dim Filepath As String

Dim wb As Workbook

Filepath = «C:\Users\Nyeier\OneDrive\EXCEL_Prosjekter\R_D\VBA_2019\VBA_Kurs»

Filepath = Workbooks.Path Normal bruk uten OneDrive/Nettet

Set wb = Workbooks.Open(Filepath & «\Test_Bok.xlsm»)

MsgBox «Du har åpnet arbeidsboken » & wb.Name

MsgBox «Filen er lokalisert i » & wb.Path

MsgBox «Filformat » & wb.FileFormat

End Sub

Workbooks.Close Method

  • Lukker alle åpne arbeidsbøker, viser ev. relevante melding hvis det er foretatt endringer i en åpen arbeidsbok.

Sub Lukk_Alle_Arbeidsboeker()

Application.DisplayAlerts = False

Workbooks.Close

Application.DisplayAlerts = True

End Sub

Workbooks.Add Method

  • Oppretter en ny arbeidsbok. Den nye arbeidsboken blir aktiv arbeidsbok. Kan opprettes med parameter Template.

Sub Lag_Ny_Arbeidsbok()

‘Oppretter en arbeidsbok med 2 ark fra template VBA_Test.xlsm

Application.SheetsInNewWorkbook = 2

Workbooks.Add Template:=ActiveWorkbook.Path & «\VBA_Test.xlsm»

End Sub

Workbook.SaveAs og Workbook.Save Methods

Sub Lag_og_Lagre_To_Arbeidsboeker()

‘Oppretter to arbeidsbøker med to ark i hver

‘Lagrer arbeidsbøkene med SaveAs

‘Skriver inn verdier i celler og lagrer med Save

‘Lukker til slutt begge arbeidsbøkene

Application.DisplayAlerts = False

Application.SheetsInNewWorkbook = 2

 

Dim w1 As Workbook, w2 As Workbook

 

Set w1 = Workbooks.Add

Set w2 = Workbooks.Add

 

w1.SaveAs ThisWorkbook.Path & «\Red.xlsm», xlOpenXMLWorkbookMacroEnabled

w2.SaveAs ThisWorkbook.Path & «\Blue.xlsm», xlOpenXMLWorkbookMacroEnabled

 

w1.Worksheets(1).Range(«C2»).Value = «Red!»

w1.Worksheets(2).Range(«E5»).Value = «Blue!»

 

w1.Save

w2.Save

 

w1.Close

w2.Close

 

Application.DisplayAlerts = True

End Sub

Workbook.Activate Method

Sub Endre_Arbeidsboeker()

‘Åpner to arbeidsbøker og aktiverer disse i tur og orden

‘Skriver inn verdier i cellen A1 i begge arbeidsbøkene

‘Lagrer arbeidsbøkene og lukker disse

Application.DisplayAlerts = False

 

Dim red As Workbook, blue As Workbook

 

Set red = Workbooks.Open(ThisWorkbook.Path & «\Red.xlsm»)

Set blue = Workbooks.Open(ThisWorkbook.Path & «\Blue.xlsm»)

 

red.Activate

ActiveWorkbook.ActiveSheet.Range(«A1») = «Another red1»

 

blue.Activate

ActiveWorkbook.ActiveSheet.Range(«A1») = «Another blue1»

 

red.Save

blue.Save

 

red.Close

blue.Close

 

Application.DisplayAlerts = True

End Sub

Workbook.Close Method

Sub Lukk_Arbeidsboeker()

‘Åpner to arbeidsbøker

‘Skriver inn verdier i celler i begge arbeidsbøkene

‘Lukker arbeidsbøkene med Close SaveChanges:=True/False

‘I den ene arbeidsboken lagres endringene i den andre lagres ikke endringene

 

Application.DisplayAlerts = False

 

Dim red As Workbook, blue As Workbook

 

Set red = Workbooks.Open(ThisWorkbook.Path & «\Red.xlsm»)

Set blue = Workbooks.Open(ThisWorkbook.Path & «\Blue.xlsm»)

 

red.Worksheets(2).Range(«A2»).Value = «Red is awsome!»

blue.Worksheets(2).Range(«A1»).Value = «Blue blue!»

 

red.Close SaveChanges:=True

blue.Close SaveChanges:=False

 

Application.DisplayAlerts = True

End Sub

 

Worksheets.Add Method

 

Sub Lag_et_Nytt_Ark()

‘Oppretter to ark Før og Etter, ett før og ett etter arket Makrotest

 

Worksheets.Add before:=Worksheets(«Makrotest»)

ActiveSheet.Name = «Før»

 

Worksheets.Add after:=Worksheets(«Makrotest»)

ActiveSheet.Name = «Etter»

End Sub

 

Worksheet.Visible Property

 

Sub Skjul_Ark()

‘Skjuler eller viser ark

‘xlVeryHidden innebærer at arket må gjøres synlig med VBA

 

Worksheets(«Før»).Visible = xlSheetVisible

Worksheets(«Før»).Visible = xlHidden

Worksheets(«Etter»).Visible = xlHidden

Worksheets(«Etter»).Visible = xlVeryHidden

Worksheets(«Etter»).Visible = xlSheetVisible

End Sub

 

Worksheet.Copy Method

 

Sub Kopier_Ark()

‘Lager en kopi av arket Makrotest etter arket Etter

Worksheets(«Makrotest»).Copy after:=Worksheets(«Etter»)

 

‘Lager en kopi av arket Makrotest før arket Før

Worksheets(«Makrotest»).Copy before:=Worksheets(«Før»)

End Sub

 

Worksheet.Delete Method

 

Sub Slett_Ark()

‘Sletter arkene Før og Etter. Advarsel er deaktivert

Application.DisplayAlerts = False

Worksheets(«Etter»).Delete

Worksheets(«Før»).Delete

Application.DisplayAlerts = True

End Sub

 

Worksheet.Move Method

 

Sub Flytt_Ark()

‘Endrer rekkefølgen på ark

Worksheets(«Makrotest»).Move before:=Worksheets(«NyPortal»)

Worksheets(«NyPortal»).Move after:=Worksheets(«Makrotest»)

End Sub

 

8. Range References

Range.Select Method

  • Range er en samling av en eller flere celler
  • Range-objektet er en egenskap ved Worksheet-collection
  • Chartsheet har ikke egenskapen Range

 

Sub Merk_Range()

‘Eksempler på bruk av Select

Worksheets(«Makrotest»).Select

 

Velge en enkelt celle og sette en verdi

Range(«D3»).Select

Selection.Value = «Celle D3»

 

‘Velge et område av celler og sette verdier

Range(«D5:G10»).Select

Selection.Value = «Cellene D5:G10»

 

‘Velge en kolonne og sette verdier

Range(«D:D»).Select

Selection.Value = «Kolonne D»

 

‘Velge flere kolonner og sette verdier

Range(«H:I»).Select

Selection.Value = «Kolonnene H:I»

 

‘Velge en rad og sette verdier

Range(«7:7»).Select

Selection.Value = «Rad 7»

 

‘Velge flere rader og sette verdier

Range(«7:12»).Select

Selection.Value = «Radene 7:12»

 

‘Velge usammenhengende områder og sette verdier

Range(«J1,L2:L3»).Select

Selection.Value = «Områdene J1, L2:L3»

End Sub

 

Value vs. Text Properties

  • Value er det interne formatet for data i en celle
  • Text er det eksterne (hva som vises )formatet for data i en celle

 

Sub Value_Text()

Dim i As Long

Dim Val As Variant

Dim txt As Variant

 

‘Viser egenskapene Value og Text for cellene A2:A5

Range(«A2»).Select

 

For i = 1 To 4

Val = Selection.Value

txt = Selection.Text

Selection.Offset(0, 1).Select

Selection.Value = Val

 

Selection.Offset(0, 1).Select

Selection.Value = txt

 

Selection.Offset(1, -2).Select

Next i

End Sub

 

R1C1 Notation, Part I

  • A1-notering (absolutt), cellen adresseres med bokstav for kolonne og nummer
  • R1C1-notering (relativ), cellen adresseres med rad-og kolonnenummer
  • R1C1-notering brukes i bakgrunnen (rad/kolonne)
  • Innstillingene endres i menyen Fil/Alternativer/Formler og kryss av for R1C1 referansestil

 

  • Referanser med firkantparanteser, relativ referanse
  • R[+/-r],C[+/-c]]betyr referanse til en celle som ligger forskjøvet
  • +/-r rader og +/-c kolonner i forhold til aktiv celle
  • Referanse til samme rad brukes kun R og C for samme kolonne
  • =RC[2], samme kolonne og forskjøvet 2 celler til høyre
  • =R[2]C, forkskjøvet 2 rader ned og i samme kolonne

 

R1C1 Notation, Part II

 

Eks. =Sum(RC[-2]:RC[-1])som kopieres, formelen endres ikke som ved absolutt referanse

 

Uten hakeparenteser er referansen absolutt

Eks. =R[-1]*R1C5, R1C5 er absolutt, det øvrige er relativ

 

The Formula and FormulaR1C1 Properties

 

Sub Skriv_Formel()

‘Legger inn en formel med A1-notasjon

‘Merk at formelen må være på engelsk

Range(«A3»).Formula = «=SUM(A1:A2)»

End Sub

 

Sub Hent_Celle_Verdi()

‘Viser innholdet av Value, Text og Formula i Immediate vinduet

Debug.Print Range(«A3»).Value

Debug.Print Range(«A3»).Text

Debug.Print Range(«A3»).Formula

End Sub

 

Sub Beregne_Total()

‘Legger inn en formel i R1C1-notasjon

‘Merk at formelen må være på engelsk

Range(«A4:C4»).FormulaR1C1 = «=SUM(R[-3]C:R[-1]C)»

End Sub

 

The Range.Offset Property

  • Brukes til traversering/navigering/forflytting til rad/kolonne
  • Range(«A1»).Offset(2,1).Select,velger celle A1 og forflytter seg 2 rader opp og 1 kolonne til høyre
  • Fortegnet bestemmer retningen, – opp/venstre og + ned/høyre
  • Forflyttingen er relativ til aktiv celle
  • I et område er det nedre venstre hjørne som er utgangspunkt for forflyttingen

 

Sub Traverser()

Worksheets(«Makrotest»).Select

‘Velger celle A1 og forflytter seg 2 rader ned og 1 kolonne til høyre

Range(«A1»).Offset(2, 1).Select

 

‘Velger område A1:C1 og forflytter seg 10 rader ned og 2 kolonner til høyre

Range(«A1:C1»).Offset(10, 2).Select

 

‘Forflytter seg 1 rad opp og 2 kolonner til venstre     i forhold til aktiv celle (nedre høyre ‘hjørne)

‘Selection.Offset(-1, -2).Select

End Sub

 

The Range.Resize Property

  • Dette innebærer å endre størrelse/redimensjonere et område
  • Resize tar to argumenter, antall rader og kolonner som området skal inkludere med øvre venstre celle som utgangspunkt

 

Sub Dimensjonere_Et_Område()

Worksheets(«Makrotest»).Select

‘Dimensjonerer et område B2 til et område med 5 rader og 5 kolonner

‘Cellen B2 er øvre venstre hjørne i området

Range(«B2»).Resize(5, 5).Select

 

‘Fyller ut området C5:D10 med verdien Excel

Range(«C5:D10»).Value = «Excel»

‘Dimensjonerer området C5:D10 til et område med 2 rader og 2 kolonner

‘Cellen C5 er øvre venstre hjørne i området

Range(«C5:D10»).Resize(2, 2).Select

End Sub

 

The Cells Property

  • En egenskap på toppnivå etter Application
  • En alternativ måte å adressere celler på, jfr R1C1
  • Kan kun brukes på en celle
  • Kan kombineres med Resize for å utvide området

 

Sub Merke_Celler_Igjen()

Worksheets(«Makrotest»).Select

Selection.Activate

 

‘Merk alle celler i arket

Application.Cells.Select

 

‘Application kan utelates

Cells.Select

 

‘Cells tar to argumenter, rad og kolonne, jfr R1C1

‘Eks velge celle i rad 2 og kolonne 5 og skrive inn en verdi

Cells(2, 5).Select

Selection.Value = «25»

 

‘Utvider Cells(2,5) til et område med totalt 4 rader og 5 kolonner

‘Skriver inn en verdi i områdets celler

Cells(2, 5).Resize(4, 5).Value = «Fill»

End Sub

 

The Range.CurrentRegion Property

  • Returner et Rangeobjekt som representerer gjeldende region
  • Gjeldende region er et område avgrenset av blanke rader og blanke kolonner
  • Cellene omskrives av et rektangel selv om dette inneholder blanke celler
  • CurrentRegion er kun lesbar
  • Egnet til å utvide et område, jfr Resize

 

Sub Velg_Currentregion()

Worksheets(«Makrotest»).Activate

 

‘Finner CurrentRegion/Boundingbox med utgangspunkt     i en bestemt celle

Range(«E8»).CurrentRegion.Select

 

‘Finner CurrentRegion/Boundingbox med utgangspunkt  i aktiv celle

ActiveCell.CurrentRegion.Select

 

‘Finner CurrentRegion/Boundingbox med utgangspunkt i et område. Velger det delområde ‘som er avgrenset av blanke rader og kolonner som ligger øverst til venstre

Range(«C4:F11»).CurrentRegion.Select

End Sub

 

The Range.End Property

  • Returnerer et Rangeobjekt som representerer cellen på slutten av området som inneholder kildeområdet.
  • End tar et argument som bestemmer retningen. Jfr. trykke End så en Piltast (Nord, Syd, Øst, Vest)

 

Sub Endre_Hjørner()

Worksheets(«Makrotest»).Activate

 

‘Markerer hjørnene i et tom regneark med End-egenskapen

Range(«A1»).Value = «Top left corner»

Range(«A1»).End(xlDown).Value = «Bottom left corner»

Range(«A1»).End(xlToRight).Value = «Top right corner»

Range(«A1»).End(xlDown).End(xlToRight).Value = «Bottom right corner»

End Sub

 

The Range.Count and Range.CountLarge Properties

  • Returner en Long-verdi som representerer antall celler i samlingen

 

Sub Antall_Objekter()

Worksheets(«Makrotest»).Activate

 

‘Antall rader og kolonner i arket

MsgBox «Antall kolonner i arket: » & Range(«1:1»).Count

MsgBox «Antall rader i arket: » & Range(«A:A»).Count

 

‘Antall celler i arket

MsgBox «Antall celler i arket: » & Range(«1:1»).CountLarge * Range(«A:A»).CountLarge

 

‘Antall celler i et område

MsgBox «Antall celler i området A3:F123 : » & Range(«A3:F123»).Count

End Sub

 

The Range.Row and Range.Column Properties

  • Returner nummeret,type Long, for første rad eller kolonne i arealet til området
  • Egenskapen er kun lesbar

 

Sub Rad_Kolonne_Nummer()

Dim ForsteRad As Long, ForsteKolonne As Long

 

Worksheets(«Makrotest»).Activate

 

ForsteRad = Range(«B4:D10»).Row

ForsteKolonne = Range(«B4:D10»).Column

 

MsgBox «Nummer for første rad: » & ForsteRad

MsgBox «Nummer for første kolonne:» & Range(«B4:D10»).Column

 

Cells(ForsteRad, ForsteKolonne).Select

End Sub

 

The Range.Rows and Range.Columns Properties

  • Returnerer et Range-objekt som representerer radene eller kolonnene i et spesifikt område

 

Sub Rader_Kolonner()

Worksheets(«Makrotest»).Activate

 

‘Velger alle radene i arket

Rows.Select

 

‘Velger alle kolonnene i arket

Columns.Select

 

‘Velger hele rad 2 i arket

Rows(2).Select

 

‘Velger radene 2,3,4,5 og 6

Rows(«2:6»).Select

 

‘Velger hele kolonne E i arket

Columns(«E»).Select

 

‘Velger kolonnnene B,C,D,E og F i arket

Columns(«B:F»).Select

 

‘Velger den 4. raden (absolutt rad 7) som ligger i kolonnene B, C og D

Range(«B4:D12»).Rows(4).Value = «Rad 4 internt»

 

‘Velger den 2. kolonnen (absolutt kolonne C)som ligger i radene i området B4:D12

Range(«B4:D12»).Columns(2).Value = «Kolonne 2 internt»

End Sub

 

The Range.EntireRow and Range.EntireColumn Properties

  • Returnerer et Range-objekt som representerer alle radene eller kolonnene i et spesifikt område

 

Sub Alle_Rader_eller_Kolonner_i_et_Område()

Worksheets(«Makrotest»).Activate

 

‘Velger hele raden i aktiv celle

ActiveCell.EntireRow.Select

 

‘Velger hele kolonnen i aktiv celle

ActiveCell.EntireColumn.Select

 

‘Velger kolonnene D,E,F og G i området D2:G5

Range(«D2:G5»).EntireColumn.Select

 

‘Velger radene 2,3,4 og 5 i området D2:G5

Range(«D2:G5»).EntireRow.Select

End Sub

 

 

Get Last Row of Data in Worksheet

  • Finn siste rad og kolonne med data i arket

 

Sub Finn_Siste_Rad_og_Kolonne()

Dim Siste_Rad As Long, Siste_Kolonne As Long

 

Siste_Rad = Cells(Rows.Count, 1).End(xlUp).Row

Siste_Kolonne = Cells(2, Columns.Count).End(xlToLeft).Column

 

MsgBox «Siste rad med data i kolonne 1 er radnummer: » & Siste_Rad & vbNewLine _

& «Siste kolonne med data i rad 2 er kolonnenummer: » & Siste_Kolonne

End Sub

 

 

9. Range Actions

The Range.FillDown Method

  • Fyller ut fra toppcellene i et spesifert område og nedover til bunnen av området
  • Innhold og format i cellene er identisk med innhold og format av cellene i begynnelsen av området
  • Kan også brukes på å fylle ut celler med formler

 

Sub Fyll_Ned()

Worksheets(«Makrotest»).Activate

Range(«C2:C10»).FillDown

End Sub

 

The Range.Replace Method

 

  • Erstatter inneholdet i en celle med et nytt innhold

 

Sub Erstatte_Noe()

Worksheets(«Makrotest»).Activate

Range(«A1:A13»).Replace what:=2, replacement:=4

End Sub

 

The Range.TextToColumns Method

  • Splitter en kolonne med celler som inneholder tekst til en eller flere kolonner

 

Sub Splitt_Tekst_til_Kolonner()

Worksheets(«Makrotest»).Activate

 

‘Splitter kommaseparert tekst i området A1:A3 til  kolonner fra Celle C1

Range(«A1:A3»).TextToColumns Destination:=Range(«C1») _

, Comma:=True

 

‘Splitter tekst separert med | i området A1:A3 til  kolonner fra Celle C1

Range(«A1:A3»).TextToColumns Destination:=Range(«C1») _

, other:=True, otherchar:=»|»

End Sub

 

The Range.Worksheet Property

  • Returnerer et Worksheet-object som representerer arket som inneholder det spesifikke området
  • Worksheet er kun lesbar
  • Traversere oppover i modellhierarkiet

 

Sub Litt_Om_Arket()

Worksheets(«Makrotest»).Activate

 

‘Ulike eksempler på bruk av Range.Worksheet

MsgBox Range(«A1»).Worksheet.Name

MsgBox ActiveCell.Worksheet.Name

MsgBox Range(«Min_Range»).Worksheet.Name

MsgBox Cells(1, 1).Worksheet.Name

End Sub

 

The Range.Sort Method

  • Sortering av kolonner

 

Sub Sortere_Kolonner()

Worksheets(«Makrotest»).Activate

 

‘Sorterer kolonne B i omrædet B i stigende rekkefølge. Kolonnen har header

Columns(«B:B»).Sort key1:=Range(«B:B») _

, order1:=xlAscending, Header:=xlYes

 

‘Sorterer kolonne A i området A:B i stigende rekkefølge. Kolonnen har header

Columns(«A:B»).Sort key1:=Range(«A:A») _

, order1:=xlAscending, Header:=xlYes

 

‘Sorterer kolonnene A og B i området A:B.  Først A i stigende rekkefølge, så B i avtagende ‘rekkefølge. Kolonnen har . header

Columns(«A:B»).Sort key1:=Range(«A:A») _

, order1:=xlAscending, key2:=Range(«B:B»), _

order2:=xlDescending, Header:=xlYes

End Sub

 

The Range.Font Property

  • Returnerer et Font-objekt som representerer fonten til det spesifiserte objektet

 

Sub Endre_Font()

‘Endre på fontegenskapen

Dim r As Range

 

Worksheets(«Makrotest»).Activate

Set r = Range(«A1»)

 

r.Font.Bold = True

r.Font.Italic = True

r.Font.Name = «Times New Roman»

r.Font.Underline = xlUnderlineStyleDouble

r.Font.Size = 24

r.Font.Color = vbBlue

r.Font.Color = RGB(125, 125, 125)

End Sub

 

The Range.Interior Property

  • Returner et Interior-objekt som representerer bakgrunnen til det spesifiserte objektet

 

Sub Endre_Interiør()

‘Endre på bagkrunnen

Dim i As Long

Worksheets(«Makrotest»).Activate

For i = 1 To 56

Cells(i, 1).Interior.ColorIndex = i

Cells(i, 1).Value = i

Next i

End Sub

 

The Range.ColumnWidth and Range.RowHeight Properties

  • Returnerer eller setter bredden eller høyden for alle kolonner eller rader for et spesifisert objekt

 

Sub Vis_og_Sett_Rad_Kolonne()

Worksheets(«Makrotest»).Activate

 

‘Kolonnebredden i en spesifisert kolonne endres

Range(«A1»).EntireColumn.ColumnWidth = 10.57

 

‘Kolonnebredden i kolonnene i et spesifisert område endres

Application.Columns(«B:E»).ColumnWidth = 10.57

 

‘Radhøyden i en spesifisert rad endres

Range(«A1»).EntireRow.RowHeight = 15

 

‘Radhøyden i et spesifisert område endres

Application.Rows(«1:4»).RowHeight = 15

End Sub

 

The Range.AutoFit Method

  • Tilpasser kolonnebredde eller radhøyde for et spesifisert område til bredde eller høyde på celleinnholdet

 

Sub Bruk_Autofit()

Worksheets(«Makrotest»).Activate

 

‘Tilpasser kolonnebredden til innholdet i et spesifisert område

Application.Columns(«A:B»).AutoFit

 

‘Tilpasser radhøyden til innholdet i et spesifisert område

Application.Rows(«1:2»).AutoFit

End Sub

 

The Range.Clear, Range.ClearContents and Range.ClearFormats Methods

  • Clear: Fjerner både innhold og formatering i spesifisert område
  • ClearContents: Fjerner innholdet i spesifisert område
  • clearFormats: Fjerner formatet i spesifisert område

 

Sub Rensk_Opp()

Worksheets(«Makrotest»).Activate

 

‘Fjerner både innhold og formatering i spesifisert område

Range(«A:D»).Clear

 

‘Fjerner både innhold i spesifisert område

Range(«A:D»).ClearContents

 

‘Fjerner formatering i spesifisert område

Range(«A:D»).ClearFormats

 

‘Setter ny verdi for kolonnebredde og radhøyde

Range(«A:D»).ColumnWidth = 10.57

Range(«A:D»).RowHeight = 15

End Sub

 

The Range.Delete Method

  • Sletter cellene i et Range-objekt
  • Skiftretningen bestemmes av parametrene xlUp/xlShiftUp eller xlToLeft/xlShiftToLeft

Sub Slett_Objekt()

Worksheets(«Makrotest»).Activate

 

‘Sletter rader, radene under flyttes opp

Rows(«4:5»).Delete

 

‘Sletter kolonner, kolonnene til venstre flyttes mot venstre

Columns(«C:D»).Delete

 

‘Sletter celle, cellene under flyttes opp

Range(«C4»).Delete xlUp

 

‘Sletter celle, cellene til høyre flyttes til venstre

Range(«C4»).Delete xlToLeft

End Sub

 

The Range.Copy and Range.Cut Methods

  • Copy kopierer et Range-objekt til et spesifisert områd eller til utklippstavelen
  • Cut kopierer et Range.objekt til et spesifisert område og fjerner innholdet fra området hvor det kopieres fra

 

Sub Kopier_og_Klipp()

Worksheets(«Makrotest»).Activate

 

‘Kopierer et område og limer det inn i et spesifisert område

Range(«A1:C3»).Copy Destination:=Range(«D8:F10»)

Range(«A1:C3»).Copy Destination:=Range(«D8»)

 

‘Kopierer et område, limer det inn i et spesifisert område og fjerner innholdet fra området ‘det kopieres fra.

Range(«A1:C3»).Cut Destination:=Range(«D8»)

End Sub

 

The Paste and PasteSpecial Methods

  • Paste limer inn innholdet av utklippstavlen
  • PasteSpecial limer inn et Range-objekt som er kopiert til et spesifisert område, tar en rekke alternativer for innliming

 

Sub Kopier_og_LimInn()

Worksheets(«Makrotest»).Activate

Range(«A1»).Copy

 

‘Limer inn kun verdien av valgt celle

Range(«C2»).PasteSpecial xlPasteValues

End Sub

 

The Parent Property on All Objects

  • Returnerer en forelderegenskap til et objekt, motsatt treversering

 

Sub Parent_Property()

Worksheets(«Makrotest»).Activate

 

‘Returnerer foreldreegenskapen til en celle, Worksheet

Debug.Print TypeName(Range(«A1»).Parent)

 

‘Returnerer navnet til foreldreegenskapen, arket, til en celle. navnet på arket

Debug.Print Range(«A1»).Parent.Name

 

‘Returnerer navnet til foreldre-foreldreegenskapen til en celle, navnet på arbeidsboken

Debug.Print Range(«A1»).Parent.Parent.Name

 

‘Returnerer navnet til foreldre-foreldre-foreldreegenskapen til en celle, Microsoft Excel

Debug.Print Range(«A1»).Parent.Parent.Parent.Name

 

‘Returnerer foreldre-foreldre-foreldreegenskapen til en celle, Application

Debug.Print TypeName(Range(«A1»).Parent.Parent.Parent)

End Sub

 

 

10. Conditionals

Boolean Expressions

  • Programutførelse basert på logiske verdier/betingelser, SANN/USANN
  • Utførelsen kan ta ulike retninger basert på disse verdiene

 

Sub Lek_med_Boolean()

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av boolske operatorer i sammenligninger

Debug.Print 3 > 5

Debug.Print 5 > 3

Debug.Print 3 < 5

Debug.Print 5 < 3

Debug.Print 3 >= 5

Debug.Print 5 >= 3

Debug.Print 3 <= 5

Debug.Print 5 <= 3

Debug.Print 1 = 1

Debug.Print 1 <> 2

Debug.Print «Hallo» = «hallo»

Debug.Print «Hallo» <> «hallo»

End Sub

 

The If Then Statement

  • Utfører en logisk test av typen Hvis A så B

 

Sub Min_If_Then()

Worksheets(«Makrotest»).Activate

 

‘ Eksempler på bruk av If Then

If 5 > 3 Then

MsgBox «5>3: TRUE»

End If

 

If «Hei» <> «HEI» Then

MsgBox «Hei <> HEI»

End If

End Sub

 

The ElseIf and Else Statements

  • Utfører en logisk test av typen Hvis A så B, ellers C

 

Sub ElseIf_Else()

Dim rng As Range

Dim ft As Font

 

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av If Then, ElseIf og Else

Set rng = Range(«A1»)

Set ft = rng.Font

 

ft.Size = 16

 

If ft.Name = «Calibri» Then

ft.Name = «Times New Roman»

ElseIf ft.Name = «Times New Roman» Then

ft.Name = «Calibri»

Else

ft.Name = «Verdana»

End If

End Sub

 

Select Case

  • Utfører en av flere grupper med instruksjoner avhengig av verdien til en test

 

Sub Min_Select_Case()

Worksheets(«Makrotest»).Activate

 

Dim CurrVal As String

Dim Dag As Long

CurrVal = Range(«A1»).Value

 

‘Eksempel på bruk av Select Case

Select Case CurrVal

Case «A», «C»

MsgBox «Hurra, det er A eller C»

Case «B», «D»

MsgBox «Hurra, det er B eller D»

Case Else

MsgBox «Vel, det er noe annet enn A, B, C eller D»

End Select

 

Dag = Day(Now)

 

Select Case Dag

Case 1 To 10

MsgBox «Første tredel av måneden»

Case 11 To 20

MsgBox «Andre tredel av måneden»

Case Else

MsgBox «Siste tredel av måneden»

End Select

 

Select Case Dag

Case Is < 10

MsgBox «Første tredel av måneden»

Case Is < 21

MsgBox «Andre tredel av måneden»

Case Else

MsgBox «Siste tredel av måneden»

End Select

End Sub

 

The AND & OR Logical Operators

  • Logiske operatorer AND og OR
  • AND er sann når alle delbetingelsene er sanne, ellers usann
  • OR er sann nå minst en av delbetingelsene er sann, ellers usann

 

Sub Min_AND_og_OR()

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av logiske operatorer AND og OR i testbetingelser

If Range(«A1»).Value = «Navn» And Range(«B1»).Value = «Alder» Then

Range(«A1»).Resize(1, 2).Font.Bold = True

End If

 

If Range(«A1»).Value = «Navn» Or Range(«B1»).Value = «Alder» Then

Range(«A1»).Resize(1, 2).Font.Bold = False

End If

End Sub

 

The NOT Operator

  • NOT inverterer sannhetsverdien til en test
  • TRUE -> FALSE eller FALSE -> TRUE

 

Sub Min_NOT()

Dim f As Font

Set f = Selection.Font

 

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av logisk operator NOT i testbetingelser

If f.Bold Then

f.Bold = False

Else

f.Bold = True

End If

 

‘ Eller

‘f.Bold = Not f.Bold

End Sub

11. Iteration

The For Next Loop

  • Gjentar en gruppe instruksjoner et spesifisert antall ganger. En syklus = en iterasjon

Sub Mine_Iterasjoner()

Dim i As Long, j As Long, k As Long, l As Long

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av For Next Loop

MsgBox «Fant » & Worksheets.Count & » ark i arbeidsboken før iterasjon.»

 

For i = 1 To 4

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

Worksheets(Worksheets.Count).Name = «Iterark_» & i

Next i

 

MsgBox «Fant » & Worksheets.Count & » ark i arbeidsboken etter iterasjon.»

 

Range(«A1»).Select

 

For j = 0 To 9

Selection.Offset(j, 0).Value = j + 1

Selection.Offset(j, 1).Value = (j + 1) * (j + 1)

Selection.Offset(j, 2).Value = (j + 1) * (j + 1) * (j + 1)

Next j

 

For k = 1 To 10

Cells(k, 1) = k

Cells(k, 2) = k * k

Cells(k, 3) = k * k * k

Next k

 

For l = 1 To 10

Cells(1, l) = l

Cells(2, l) = l * l

Cells(3, l) = l * l * l

Next l

End Sub

 

The Step Keyword

  • Angir inkrementet/økningen ved tellervariabelen

 

Sub Mitt_Steg()

Dim i As Long, j As Long, k As Long, r As Range

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av Step Keyword

For i = 2 To 10 Step 2

Debug.Print i

Next i

 

For j = 10 To 2 Step -2

Debug.Print j

Next j

 

For k = 2 To 100 Step 2

Set r = Cells(k, 1).EntireRow

r.Value = k

r.Interior.Color = vbYellow

Next k

End Sub

 

Deleting Rows

  • Sletter rader

 

Sub Slett_Rader()

Dim i As Long, SisteRad As Long

Worksheets(«Makrotest»).Activate

 

‘Eksempler på sletting av rader, bakvendt iterasjon

SisteRad = Cells(Rows.Count, 1).End(xlUp).Row

 

For i = SisteRad To 1 Step -1

If Cells(i, 1) = «SLETT» Then

Cells(i, 1).EntireRow.Delete

End If

Next i

End Sub

 

The For Each-Next Construct

  • Gjentar en gruppe med instruksjoner for hvert element i samlingen

 

Sub Min_For_Each_Next()

Dim wb As Workbook, ws As Worksheet, rng As Range, app As Application

Worksheets(«Makrotest»).Select

 

‘Eksempel på bruk av For Each-Next

‘Viser navn på alle ark i alle åpne arbeidsbøker og adresser med innholdet i et utvalg ikke-‘blanke celler i gjeldene applikasjon

 

Set app = Application

 

Debug.Print app.Name

For Each wb In Workbooks

Debug.Print «Arbeidsbok: » & wb.Name

For Each ws In wb.Worksheets

Debug.Print «Ark: » & ws.Name

For Each rng In ws.Range(«c1:c4»)

If rng.Value <> «» Then

Debug.Print rng.Address & «: » & rng.Value

End If

Next rng

Next ws

Debug.Print

Next wb

End Sub

 

The With-End With Construct

  • Utfører en rekke instruksjoner som gjentatte ganger refererer til et enkelt objekt eller en struktur, slik at instruksjonene kan bruke en forenklet syntaks når de refererer til medlemmer av objektet eller strukturen, dot-notasjon

 

Sub With_End_With()

Dim rng As Range

Worksheets(«Makrotest»).Select

 

‘Eksempel på bruk av With-End With

With Selection.Font

.Name = «Times New Roman»

.Size = 16

.Color = RGB(11, 9, 1)

.Bold = True

.Italic = True

End With

 

‘Eller  Set rng = Range(«A:A»)

 

With rng.Font

.Name = «Times New Roman»

.Size = 16

.Color = RGB(11, 9, 1)

.Bold = True

.Italic = True

End With

End Sub

 

Exit For and Review of Exit Sub

Exit For

  • Avslutter straks For-løkken hvor den forekommer
  • Utførelsen fortsetter med instruksjonen etter Next instruksjonen.
  • Exit For kan bare brukes i en For … Next eller For Each … Next sløyfe
  • Når det brukes i nestede For sløyfer, avslutter Exit For den innerste sløyfen og overfører kontrollen til neste høyere nivå

Exit Sub

  • Avslutter umiddelbart Sub prosedyre der den forkommer
  • Utførelsen fortsetter med instruksjonen etter instruksjonen som kalte Sub-prosedyren
  • Exit Sub kan bare brukes i en Sub-prosedyre

 

Sub Min_Exit()

Dim SisteRad As Long, i As Long, cell As Range, rng As Range

Worksheets(«Makrotest»).Select

‘Eksempler på bruk av Exit For og Exit Sub

SisteRad = Cells(Rows.Count, 1).End(xlUp).Row

 

For i = 1 To SisteRad

Cells(i, 1).Font.Name = «Verdana»

Cells(i, 1).Font.Size = 14

 

If Cells(i, 1).Value = «August» Then

Cells(i, 1).Select

Exit For

End If

Next i

 

Set rng = Range(«C1:G2»)

 

For Each cell In rng

If cell.Value <> «» Then

cell.Value = cell.Value * 2

Else

Exit Sub

Next cell

End Sub

 

 

12. Miscellaneous Features

The MsgBox Method In Depth, Part I

  • Viser en melding i en dialogboks og venter på at brukeren klikker på en knapp
  • Returnerer et heltall som angir hvilken knapp brukeren klikket på

 

Sub Vis_Melding_1()

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av MsgBox-metoden

MsgBox «Hei», , «Boks uten knapper og ikon»

MsgBox «Her er det knapper», vbYesNoCancel, «Knappeboks»

MsgBox «Her er det knapper med ikon», vbYesNoCancel + vbQuestion, «Knappeboks med ikon»

MsgBox «Her er det knapper med ikon og standardknapp», vbYesNoCancel + vbQuestion + vbDefaultButton1, «Knappeboks med knapper, ikon og standardknapp»

End Sub

 

The MsgBox Method In Depth, Part II

  • Viser en melding i en dialogboks og venter på at brukeren klikker på en knapp
  • Returnerer et heltall som angir hvilken knapp brukeren klikket på

 

Sub Vis_Melding_2()

Dim Respons As Long

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av MsgBox-metoden

Respons = MsgBox(«Vil du slette rad 1 ?», vbYesNoCancel + vbQuestion + vbDefaultButton2, «Slette rad»)

 

Select Case Respons

Case vbYes, 6

Rows(1).Delete

End Select

‘Eller If Respons = vbYes Then  Rows(1).Delete   End If

End Sub

 

The Application.StatusBar

  • Returnerer eller setter teksten i statuslinjen

 

Sub Min_Status()

Dim i As Long

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av Application.Statusbar

For i = 1 To 5000

Cells(i, 1).Value = i

Cells(i, 2).Value = i * i

If i Mod 100 = 0 Then

Application.StatusBar = i & «av 5000»

End If

Next i

Application.StatusBar = False

End Sub

 

The Application.ScreenUpdating

  • Endre oppdatering av skjermen til av eller på

 

Sub Min_SkjermOppdatering()

Dim i As Long, j As Long

Worksheets(«Makrotest»).Activate

 

‘ Eksempler på bruk av Application.ScreenUpdating

Application.ScreenUpdating = False

 

For i = 1 To 100

For j = 1 To 100

Cells(i, j).Value = i * j

Next j

Next i

 

Application.ScreenUpdating = True

End Sub

 

SpecialCells

  • Returnerer et Range-objekt som representerer alle celler som matcher en spesifisert type eller verdi

 

Sub Mine_SpesialCeller()

Dim r As Range

Worksheets(«Makrotest»).Activate

 

Set r = Range(«A1:C6»)

 

‘Eksempler på bruk av SpecialCells

r.SpecialCells(xlCellTypeFormulas).Select

r.SpecialCells(xlCellTypeConstants).Select

r.SpecialCells(xlCellTypeBlanks).Select

r.SpecialCells(xlCellTypeLastCell).Select

End Sub

 

The InputBox Function

  • Viser en dialogboks for innlegging av brukerdata
  • Returnerer informasjonen som er lagt inn i dialogboksen

 

Sub Min_Dialog()

Dim wsNavn As String, ws As Worksheet

Worksheets(«Makrotest»).Activate

 

‘Eksempler på bruk av InputBox

wsNavn = InputBox(«Navn på nytt ark:», Title:=»Navn», Default:=»MittArk»)

 

If wsNavn <> «» Then

Set ws = Worksheets.Add

ws.Name = wsNavn

End If

End Sub

 

The Application.InputBox Method

  • Viser en dialogboks for innlegging av brukerdata
  • Returnerer informasjonen som er lagt inn i dialogboksen

 

Sub Min_AppDialog()

Dim UserRange As Range, cell As Range

Dim r As Long, g As Long, b As Long

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av Application.InputBox

Set UserRange = Application.InputBox(«Område?», «Fargelegge», ActiveCell.Address, , , , , 8)

 

For Each cell In UserRange

r = WorksheetFunction.RandBetween(0, 255)

g = WorksheetFunction.RandBetween(0, 255)

b = WorksheetFunction.RandBetween(0, 255)

cell.Interior.Color = VBA.RGB(r, g, b)

Next cell

End Sub

 

 

13. Arrays

Intro to Arrays

  • Et sett med sekvensielt indekserte elementer som har samme interne datatyper. Hvert element i en matrise har et unikt identifiserende indeksnummer
  • Endringer som gjøres til ett element i et array påvirker ikke de andre elementene

 

Sub Mitt_Array_1()

Dim Arstider(3) As String, i As Long

Dim rng As Range

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av et Array med fast dimensjon

Arstider(0) = «Sommer»

Arstider(1) = «Høst»

Arstider(2) = «Vinter»

Arstider(3) = «Vår»

 

Set rng = Cells(1, 1)

 

With rng

.Value = «Årstider»

.Font.Color = RGB(50, 50, 0)

.Interior.Color = RGB(255, 100, 0)

.Font.Name = «Calibri»

.HorizontalAlignment = xlCenter

.Font.Italic = True

.Font.Bold = True

End With

 

For i = 0 To 3

Cells(i + 2, 1).Value = Arstider(i)

Next i

End Sub

 

Alternate Syntax for Fixed-Size Arrays

 

Sub Mitt_Array_2()

Dim Nummer(5) As Long

Dim Nummer(0 To 5) As Long, i As Long

Dim rng As Range

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av et Array med fast dimensjon

Nummer(0) = 2

Nummer(1) = 4

Nummer(2) = 6

Nummer(3) = 7

Nummer(4) = 11

Nummer(5) = 21

 

Set rng = Cells(1, 1)

 

With rng

.Value = «Nummer»

.Font.Color = RGB(50, 50, 0)

.Interior.Color = RGB(255, 100, 0)

.Font.Name = «Calibri»

.HorizontalAlignment = xlCenter

.Font.Italic = True

.Font.Bold = True

End With

 

For i = 0 To 5

Cells(i + 2, 1).Value = Nummer(i)

Cells(i + 2, 1).HorizontalAlignment = xlCenter

Next i

End Sub

 

The Option Base 1 Syntax and Write Array Values to Cells.

  • Option Base, brukes for å deklarere standard nedre grense for et Array

 

Sub Mitt_Array_3()

Dim Nummer(5) As Long

Dim Nummer(0 To 5) As Long, i As Long, rng As Range

Worksheets(«Makrotest»).Activate

 

‘Eksempel på bruk av et Array med fast dimensjon

Nummer(0) = 2

Nummer(1) = 4

Nummer(2) = 6

Nummer(3) = 7

Nummer(4) = 11

Nummer(5) = 21

 

Set rng = Cells(1, 1)

 

With rng

.Value = «Nummer»

.Font.Color = RGB(50, 50, 0)

.Interior.Color = RGB(255, 100, 0)

.Font.Name = «Calibri»

.HorizontalAlignment = xlCenter

.Font.Italic = True

.Font.Bold = True

End With

 

For i = 0 To 5

Cells(i + 2, 1).Value = Nummer(i)

Cells(i + 2, 1).HorizontalAlignment = xlCenter

Next i

End Sub

 

Sub Opt_One()

‘Option base 1, dette må deklareres helt i toppen av modulen, dropper det her

Dim KunWeekEnd(1 To 2) As String

Worksheets(«Makrotest»).Select

 

KunWeekEnd(1) = «Lørdag»

KunWeekEnd(2) = «Søndag»

 

Velger å skrive inn arrayn i et område

Range(«C1:D1»).Value = KunWeekEnd

End Sub

 

Initialize Arrays within a For Loop

  • Fyller inn verdier i en array med en For-løkke

 

Sub Trim_Verdier()

Dim Ordarray(1 To 10) As String

Dim i As Long

Worksheets(«Makrotest»).Select

 

For i = 1 To 10

Ordarray(i) = Trim(Cells(i, 1).Value)

Cells(i, 2).Value = Ordarray(i)

Next i

End Sub

 

The LBound and UBound Methods

  • LBound returnerer en verdi som angir den laveste indeksvevrerdien i et array
  • UBound returnerer en verdi som angir den høyeste indeksverdien i et array.

 

Sub Lav_og_Høy()

Dim UkeDager(3 To 9) As String

Dim i As Long

Worksheets(«Makrotest»).Select

 

UkeDager(3) = «Søndag»

UkeDager(4) = «Mandag»

UkeDager(5) = «Tirsdag»

UkeDager(6) = «Onsdag»

UkeDager(7) = «Torsdag»

UkeDager(8) = «Fredag»

UkeDager(9) = «Lørdag»

 

For i = LBound(UkeDager) To UBound(UkeDager)

Cells(i + 1, 1).Value = UkeDager(i)

Next i

End Sub

 

Dynamic Arrays

  • Et array som dimensjoneres mens koden kjøres

 

Sub Dynamisk_Array()

Dim KolonneVerdier() As String

Dim SisteRad As Long, i As Long

Worksheets(«Makrotest»).Select

 

SisteRad = Cells(Rows.Count, 1).End(xlUp).Row

 

ReDim KolonneVerdier(SisteRad – 1)

 

For i = LBound(KolonneVerdier) To UBound(KolonneVerdier)

KolonneVerdier(i) = Cells(i + 1, 1).Value

Cells(i + 1, 2).Value = Len(KolonneVerdier(i))

Next i

End Sub

 

 

The Range.RemoveDuplicates Method

  • Fjerner dupliserte data i et område

 

Sub Fjern_Duplikater()

Worksheets(«Makrotest»).Select

Range(«A1:A9»).RemoveDuplicates Columns:=Array(1) , Header:=xlYes

End Sub

 

 

14. Functions

 

VBA Functions, Part I

Innebygde funksjoner i VBA-objektet

 

Sub Moro_med_VBA_Funksjoner_I()

Debug.Print LCase(«IngenTing»)

Debug.Print UCase(«IngenTing»)

Debug.Print VBA.UCase(«ingenting»)

 

Debug.Print Len(«IngenTing»)

Debug.Print Trim(»  IngenTing   «)

Debug.Print InStr(1, «Ingen-Ting», «en-«)

End Sub

 

VBA Functions, Part II

Flere innebygde funksjoner i VBA-objektet

 

Sub Moro_med_VBA_Funksjoner_II()

Debug.Print Left(«555-555-5555», 4)

Debug.Print Right(«555-555-5555», 5)

Debug.Print Mid(«555-555-5555», 4, 5)

 

Debug.Print StrReverse(«Inge Tang»)

Debug.Print Replace(«Inge Tang», «Ta», «Ga»)

End Sub

 

The Split Function

  • Returnerer en en-dimensjonal array med et gitt antall substrings

 

Sub Moro_Med_VBA_Funksjoner_III()

 

Dim Resultater() As String

Worksheets(«Makrotest»).Activate

 

Resultater = Split(Range(«A1»), «/»)

Range(«B1:D1»).Value = Resultater

End Sub

 

The Is Family of Functions

  • Funksjoner som returnerer en Boolsk verdi True/False

 

Sub Moro_med_Is_Funksjoner()

Dim i As Long

Dim SisteRad As Long

Dim Val As Variant

Dim Kalkulasjoner(3) As Boolean

Worksheets(«Makrotest»).Activate

 

SisteRad = Cells(Rows.Count, 1).End(xlUp).Row

 

For i = 2 To SisteRad

Val = Cells(i, 1).Value

Kalkulasjoner(0) = IsNumeric(Val)

Kalkulasjoner(1) = IsDate(Val)

Kalkulasjoner(2) = IsEmpty(Val)

Kalkulasjoner(3) = IsError(Val)

Cells(i, 2).Resize(1, 4).Value = Kalkulasjoner

Next i

End Sub

 

Date and Time Functions

 

Sub Moro_med_Dato_og_tid()

Debug.Print Date

Debug.Print Time

Debug.Print Now

End Sub

 

Sub Lag_Dato()

Dim F_Dato As Date

 

F_Dato = DateSerial(1953, 9, 25)

Debug.Print F_Dato

End Sub

 

More Date and Time

 

Sub Mer_Moro_med_Dato_og_Tid()

Debug.Print Year(Now)

Debug.Print Month(Now)

Debug.Print Day(Now)

 

Debug.Print Hour(Now)

Debug.Print Minute(Now)

Debug.Print Second(Now)

 

Debug.Print Weekday(Now)

Debug.Print WeekdayName(Weekday(Now))

Debug.Print MonthName(Month(Now))

End Sub

 

Excel Worksheet Functions

 

Sub Moro_med_VLOOKUP()

Dim cv As Variant

Worksheets(«Makrotest»).Activate

 

cv = Application.WorksheetFunction.VLookup(ActiveCell.Value, Range(«A1:C5»), 3, False)

ActiveCell.Offset(0, 1).Value = cv

End Sub

 

Custom Functions

 

  • En funksjonsprosedyre er VBA-kode som utfører instruksjoner og returnerer en verdi (eller en rekke verdier)
  • Ved hjelp av en funksjonsprosedyre kan du opprette en funksjon som du kan bruke i regnearket akkurat som alle vanlige Excel-funksjoner som SUM eller VLOOKUP

 

Function F_til_C(Temperatur As Double) As Double

F_til_C = (Temperatur – 32) * (5 / 9)

End Function

 

Function Hoyde_i_Cm(Fot As Long, Tommer As Long) As Double

Dim TotalTommer As Long

 

TotalTommer = (Fot * 12) + Tommer

Hoyde_i_Cm = TotalTommer * 2.54

End Function

 

 

15. Debugging

Intro to Error Handling

  • Feil i syntaks, logikk, kompilering, run-time

The OnError and GoTo

  • Går til en bestemt seksjon ved feil

 

Sub Moro_med_Sum()

Worksheets(«Makrotest»).Select

 

On Error GoTo UgyldigData

MsgBox (Range(«A1»).Value * 5)

Exit Sub

UgyldigData:

MsgBox «Ugyldig data»

End Sub

 

The OnError Resume Next

  • Utfører neste instruksjon etter der feilen oppsto

 

Sub Divisjon()

Dim i As Long

On Error Resume Next

 

Worksheets(«Makrotest»).Select

For i = 1 To 10

Cells(i, 3).Value = Cells(i, 1).Value / Cells(i, 2).Value

Next i

End Sub

 

Error and Err.Number

 

Sub Divisjon2()

Dim i As Long

On Error Resume Next

 

Worksheets(«Makrotest»).Select

For i = 1 To 10

Cells(i, 3).Value = Cells(i, 1).Value / Cells(i, 2).Value

If (Err) Then

Debug.Print Err.Number

Debug.Print Error(Err.Number)

Err.Clear

End If

Next i

End Sub

 

Sub Vis_Feil()

Dim i As Long

Worksheets(«Makrotest»).Select

 

For i = 1 To 200

Cells(i, 1).Value = Error(i)

Next i

End Sub

 

Stepping Through Code

  • Trinnvis utførelse av VBA-instruksjonene
  • Bruk F8 for å utføre koden steg for steg. Flytte ev. gul markør til et annen kodelinje. Trykk F5 for å utføre alt

 

Sub Trinnvis_VBA()

Dim i As Long

Worksheets(«Makrotest»).Select

 

For i = 1 To 5

Cells(i, 1).Value = i

Next i

End Sub

 

Breakpoints

  • Breakpoint settes i høyre kant ved å klikke i kanten eller trykke F9 (av og på)

 

Sub Breakpoint_VBA()

Dim i As Long

Worksheets(«Makrotest»).Select

 

For i = 1 To 100

Cells(i, 1).Value = i

Next i

Range(«A1»).Value = 1 / 1

End Sub

 

 

16. Events

Introduction to Events

  • Hendelser knyttet til arbeidsbok og ark
  • Utløse en hendelse når vi feks åpner en arbeidsbok eller et ark

 

Eksempel på en hendelse knyttet til et ark/worksheet

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘Her kommer koden

End Sub

 

Eksempel på en hendelse knyttet til en arbeidsbok/workbook

 

Private Sub Workbook_Open()

‘Her kommer koden

End Sub

 

The Worksheet_SelectionChange Event

  • Hendelse knyttet til endringer i cellvalg i et ark

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.ClearFormats

Debug.Print Target.Address

Target.Interior.Color = vbRed

End Sub

 

Review of Application.EnableEvents

  • For å unngå at hendelser utløser nye hendelser og at det oppstår uønskede sideeffekter bruker vi Application.EnableEvents=False
  • Denne settes tilbake til True når hendelsen terminerer

 

Eksempel

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents=False

Cells.ClearFormats

Debug.Print Target.Address

Target.Interior.Color = vbRed

Application.Enbleevents=True

End Sub

 

The Worksheet_Change Event

  • Hendelser knyttet til endringer i en celle.

 

Eksempel

 

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 1 And Target.Row >= 2 Then

If Target.Value = «» Then

Target.Offset(0, 1).Clear

Else

Target.Offset(0, 1).Value = Target.Value * 0.453592

End If

End If

Application.EnableEvents = True

End Sub

 

The Worksheet_Activate Event

  • Hendelser knyttet til aktivering av ark

 

Eksempel

 

Private Sub Worksheet_Activate()

Application.EnableEvents = False

MsgBox «Velkommen til » & ActiveSheet.Name

MsgBox «Vær forsiktig når du endrer verdiene»

Application.EnableEvents = True

End Sub

 

 

Workbook Events and The Sh Argument

  • Hendelser knyttet til arbeidsboken

 

Eksempel

 

Private Sub Workbook_Open()

Application.EnableEvents = False

MsgBox «Velkommen til arbeidsboken»

MsgBox «Du bruker arbeidsboken » & ActiveWorkbook.Name

Application.EnableEvents = True

End Sub

 

Procedures with Boolean Arguments + The Workbook_BeforePrint Event

 

Eksempel

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.EnableEvents = False

If 5 > 3 Then

MsgBox («Utskrift er ikke mulig»)

Cancel = True

End If

Application.EnableEvents = True

End Sub

 

 

17. Userforms

Create UserForm, Toolbox, Properties, Controls

Nytt skjema med Insert->Userform

Navn: frm…

The Label and TextBox Controls

Label, etikett med tekst

Navn: lbl…

Textbox, dialogboks for tekst

Navn: tbx…

Naming Conventions

  • 3-bokstavs prefix
  • Dette gjør det mulig å utnytte intellisens i VBA-koden
  • Navnet bør gjenspeile kontrollens funksjon

Design Aesthetics

  • Bruk formateringsmenyen Format
  • Valg med Shift/Ctrl, kontroll med hvite markører er basis
  • Legg merke til Grouping/Ungrouping-funksjonen

The CommandButton Control

Knapp med tilknyttet makro

Navn: cmd…

Add Event Procedure to Control

Vis kode og velg type hendelse

Eksempel

Private Sub txtForNavn_Change()

Debug.Print txtForNavn.Value

End Sub

Unload and Hide a UserForm

Lukke og skjule skjema

  • Unload, data blir ikke bevart
  • Hide, data blir bevart

Eksempel

Private Sub cmdCancel_Click()

Unload Me

End Sub

 

Private Sub cmdSkjul_Click()

Me.Hide

Application.ScreenUpdating = True

End Sub

 

Submit the UserForm

 

Eksempel

Private Sub cmdLagre_Click()

Dim SisteRad As Long

Dim SisteNavn As String

 

SisteRad = Cells(Rows.Count, 1).End(xlUp).Row

SisteNavn = Me.txtForNavn.Value

 

Cells(SisteRad + 1, 1).Value = Me.txtForNavn.Value

Me.txtForNavn.Value = «»

Me.lblSisteNavn = «Siste navnet som ble lagret: » & SisteNavn

End Sub

 

Activate a UserForm from Procedure

  • Aktivere et skjema fra en prosedyre

 

Eksempel

Prosedyren kan tilordnes til en knapp i Excel

 

Public Sub Vis_Skjema()

frmSkjema.Show

End Sub

 

The initialize Event

  • Mulighet til tilordne verdier til kontrollene i skjema før det vises

 

Eksempel

Private Sub UserForm_Initialize()

Dim UkeDagNummer As Long

Dim UkeDag As String

 

UkeDagNummer = Weekday(Now) – 1

UkeDag = WeekdayName(UkeDagNummer)

Me.lblUkeDag = «I dag er det » & UkeDag

End Sub

 

Eksempel

Private Sub cmdSlett_Click()

Dim i As Long

 

Application.DisplayAlerts = False

For i = lbxArk.ListCount – 1 To 0 Step -1

If lbxArk.Selected(i) Then

Worksheets(i + 1).Delete

lbxArk.RemoveItem (i)

End If

Next i

Application.DisplayAlerts = True

End Sub

 

Eksempel

Private Sub cmdEndreFont_Click()

With ActiveSheet.Cells.Font

.Name = Me.cbxFont.Value

.Size = Me.cbxFontSize.Value

End With

End Sub

 

 

Eksempel på skjema med kontroller og bruk av navnekonvensjoner

 

Prosedyrer tilknyttet kontrollene over

 

Private Sub cmdCancel_Click()

Unload Me

End Sub

 

Private Sub cmdEndreFont_Click()

With ActiveSheet.Cells.Font

.Name = Me.cbxFont.Value

.Size = Me.cbxFontSize.Value

End With

End Sub

 

Private Sub cmdHandling_Click()

If Me.chkTøm.Value Then

Cells.Clear

End If

 

If Me.cmdLagre Then

ActiveWorkbook.Save

End If

End Sub

 

Private Sub cmdLagre_Click()

Dim SisteRad As Long

Dim SisteNavn As String

 

SisteRad = Cells(Rows.Count, 1).End(xlUp).Row

SisteNavn = Me.txtForNavn.Value

 

Cells(SisteRad + 1, 1).Value = Me.txtForNavn.Value

Me.txtForNavn.Value = «»

 

Me.lblSisteNavn = «Siste navnet som ble lagret: » & SisteNavn

End Sub

 

Private Sub cmdSkjul_Click()

Me.Hide

Application.ScreenUpdating = True

End Sub

 

Private Sub cmdSlett_Click()

Dim i As Long

Application.DisplayAlerts = False

For i = lbxArk.ListCount – 1 To 0 Step -1

If lbxArk.Selected(i) Then

Worksheets(i + 1).Delete

lbxArk.RemoveItem (i)

End If

Next i

Application.DisplayAlerts = True

End Sub

 

Private Sub UserForm_Activate()

Worksheets(«Makrotest»).Activate

End Sub

 

Private Sub UserForm_Initialize()

Dim UkeDagNummer As Long

Dim UkeDag As String

Dim ws As Worksheet

 

UkeDagNummer = Weekday(Now) – 1

UkeDag = WeekdayName(UkeDagNummer)

Me.lblUkeDag = «I dag er det » & UkeDag

 

Me.lbxArk.RowSource = «»

 

For Each ws In Worksheets

lbxArk.AddItem ws.Name

Next ws

 

With cbxFont

.RowSource = «»

.AddItem «Arial»

.AddItem «Calibri»

.AddItem «Verdana»

End With

 

With cbxFontSize

.AddItem 12

.AddItem 14

.AddItem 24

.AddItem 36

End With

End Sub

 

18. Hendelser knyttet til Arbeidsbok og Ark

Workbook

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.EnableEvents = False

If 5 > 3 Then

MsgBox («Utskrift er ikke mulig»)

Cancel = True

End If

Application.EnableEvents = True

End Sub

 

Private Sub Workbook_Open()

Application.EnableEvents = False

MsgBox «Velkommen til arbeidsboken»

MsgBox «Du bruker arbeidsboken » & ActiveWorkbook.Name

Application.EnableEvents = True

End Sub

 

Private Sub Worksheet_Activate()

Application.EnableEvents = False

MsgBox «Velkommen til » & ActiveSheet.Name

MsgBox «Vær forsiktig når du endrer verdiene»

Application.EnableEvents = True

End Sub

 

Worksheet

 

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 1 And Target.Row >= 2 Then

If Target.Value = «» Then

Target.Offset(0, 1).Clear

Else

Target.Offset(0, 1).Value = Target.Value * 0.453592

End If

End If

 

Debug.Print Target.Address

Application.EnableEvents = True

End Sub

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

Cells.ClearFormats

Debug.Print Target.Address

Target.Interior.Color = vbRed

Application.EnableEvents = True

End Sub

Søkbar kryssreferanse for EXCEL-funksjoner Engelsk – Norsk
with Ingen kommentarer

Kryssref. for EXCEL-funksjoner Engelsk – Norsk Kilde: https://www.excelguru.no/oversikt-over-excel-funksjoner-pa-engelsk-og-norsk/

Vis formater for dato og tid
with Ingen kommentarer

Her ser du en liste med de fleste tegnene som kan brukes til å formatere dato og tid i VBA: Tegn Eksempel Forklaring m 2 Måned (numerisk uten 0) mm 02 Måned (numerisk med 0) mmm Feb Måned (forkortet tekst) … Read More

Visual Basic Editor (Excel)
with Ingen kommentarer

This code will add new code module named NewModule to the VBProject of the active workbook. The type of VBComponent is specified by the value of the parameter passed to the Add method. Sample Code Adding A Module To A … Read More

Application Object
with Ingen kommentarer

Use the Application property to return the Application object. The following example applies the Windows property to the Application object.   Application.Windows(«book1.xls»). Activate The following example creates an Excel workbook object in another application and then opens a workbook in … Read More