[PDF] [PDF] VBA programming - Hervé Hocquard - University of Bordeaux - LaBRI

View - Download [PDF] VBA programming - Hervé Hocquard - University of Bordeaux - LaBRI

Previous PDF

Next PDF

1VBA programming -Hervé Hocquard -University of Bordeaux

Hervé Hocquard

http://www.labri.fr/perso/hocquard 2

ARRAYS

VBA programming -Hervé Hocquard -University of Bordeaux 3

Arrays(1)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻDeclaration

-Index starts at 0 by default:

ͻOption Base 1

ͻMultidimensionalarrays

-Dim MyTable(1 To 10,1 To 10) As Integer

ͻAssignment

-MyTable(2)=17 'one dimension -MyTable(3,4) = 125 'twodimensions 99
4

Arrays(2)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻDynamicarrays

ͻCreation

-Dim MyTable() As Integer

ͻResizing

-ReDimMyTable(NumberElements)

ͻResize keeping the data already present

-ReDimPreserve MyTable(NumberElements) 5

ArrayswithǥArray(3)

VBA programming -Hervé Hocquard -University of Bordeaux -Structure to display the content:

Dim month As Variant

Dim m As Variant

month = Array("January", "March", "August", "December")

For Eachm Inmonth

MsgBoxm

Nextm

Dim monthAs Variant

Dim i As Integer

month = Array("January", "March", "August", "December")

For i = 0To3

MsgBoxmonth(i)

Nexti 6

Basic functionarrays(4)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻFunctions on arrays:

-Lbound: smallest index of the array -(Lbound,i) : smallest dimension index iof the array -Ubound: larger index -(Ubound,i) : largest dimension index iof the array -Erase: clears the arrayfrom memory 7

Basic function arrays -example (5)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻExamples of array functions

Dim month As Variant, i As Integer

month = Array("January", "March", "August", "December")

For i = LBound(month)ToUBound(month)

MsgBoxmonth(i)

Nexti

Erasemonth

Give the lower boundGive the upper bound

8

STRINGS

VBA programming -Hervé Hocquard -University of Bordeaux 9

Functions on character strings (1)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻConcatenation: & ("Hervé" & " Hocquard ")

ͻPeriodic construction:

-String (20,"x"): repeatsthe character'x' 20 times -Space(10):generates a sequence of 10 spaces

ͻBurst: Split (chain,separator)

-s = Split ("c: \windows \system32 \driver.dll", "\") -s must be of type Variant 10

Functions on character strings (2)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻLength: Len(chain)

ͻPositioning: InStr(string,character)

-pos = InStr("The weather is nice","h") returns9

ͻLowercase, uppercase:

ͻLCase("HellO") returns" hello "

ͻUCase("HellO") return "HELLO"

ͻCharacter selection: Mid, Left, Right

ͻLeft("Hocquard",3) returns"Hoc"

ͻRight("Hocquard",3) returns"ard"

ͻMid("Hocquard",5,2) returns"ua"

11

Functions on character strings (3)

VBA programming -Hervé Hocquard -University of Bordeaux parameters

ͻSame operation as in Excel

ͻFormat (12121.13,"## '###. 00") returns 12'121.13

ͻFormat ("Hello","<") ÙUCase("Hello")

ͻFormat (Date,"yy/mmmm/dd ")

-The Date function returns the current date. This must be formatted before display in a dialog box, otherwise it will be displayed in the form specified in the regional options (dd/mm/yy)

ͻOther functions here:

12

USER-DEFINEDDATA TYPES

Structured type

Simple or structured fields

VBA programming -Hervé Hocquard -University of Bordeaux 13

User-DefinedData Types

VBA programming -Hervé Hocquard -University of Bordeaux ͻVBA lets you create custom, or user-defined, data types. A user-defined data type can ease your work with some types of data. ͻUnlikearrays, this structured type allows you to group data of different types. ͻExample: a book is identified by a code, a title, one or more authors, a publisher and possibly the date of publication. ͻBook is a User-DefinedData Type variable; each of these five data is a field that can be simple or structured. 14

User-DefinedData Types

VBA programming -Hervé Hocquard -University of Bordeaux ͻThe User-DefinedData Types are declared in VB with the word Type.

ͻSyntax:

Type RecordName

Field1 As type1

Field2 As type2

End Type

ͻExample:

Type bookType Madate

code As IntegerdayAs Integer titleAs String * 40 monthAs Integer authorAs String * 50 yearAs Integer editor As String * 50 End Type date As Madate

End Type

Simple

fields

Structured

field 15

User-DefinedData Types -Example (1)

VBA programming -Hervé Hocquard -University of Bordeaux

ͻExample:

Type bookType Madate

code As IntegerdayAs Integer titleAs String * 40 monthAs Integer authorAs String * 50 yearAs Integer editor As String * 50 End Type date As Madate

End Type

ͻTo access a control:

Dim livre As book

livre.author= "Durand" livre.date.year= 1980 'we see here that we could replace livre by an array in the type livre(9).author = "Durand" if it is the ninth book on the list ... 16

User-DefinedData Types -Example (2)

VBA programming -Hervé Hocquard -University of Bordeaux A student is defined by his name, first name, date of birth and his note:

PrivateType Student

nameAs String * 40 firstnameAs String * 40 dateofbirthAs Date note As Double

End Type

A class can contain at most 30 students:

ConstNbMax= 30 'for the maximum number of students

PrivateType Class

list(NbMax) As Student 'the list is an array of students nbr As Integer 'the real number of students

End Type

We then declare the class of students:Dim c As Class 17

User-DefinedData Types -Exercise

VBA programming -Hervé Hocquard -University of Bordeaux ͻThe previous example will be completed in the next course on graphical interfaces ...

ͻHow to define a matrix?

ͻCreate a program that displays the number of rows and columns of a matrix entered on Sheet1 of the WorkBook.

18

End of Part 6

VBA programming -Hervé Hocquard -University of Bordeaux 19

Thanks

VBA programming -Hervé Hocquard -University of Bordeaux

Thank you

Herve Hocquard( hocquard@labri.fr )

quotesdbs_dbs17.pdfusesText_23

[PDF] multifamily energy efficiency rebate program

[PDF] multigraph

[PDF] multilayer switch configuration

[PDF] multilevel feedback queue implementation

[PDF] multilevel feedback queue scheduling tutorialspoint

[PDF] multilevel feedback queue scheduling code in java

[PDF] multilevel feedback queue scheduling program in c++

[PDF] multilevel inverter block diagram

[PDF] multilevel inverter ppt

[PDF] multilevel inverter project report

[PDF] multilevel inverter switching pattern

[PDF] multilevel inverter thesis

[PDF] multilevel inverters syllabus

[PDF] multilevel queue scheduling

[PDF] multimedia powerpoint presentation examples