[PDF] [PDF] Excel - Programmation VBA - LaBRI

[PDF] Excel vba array function multidimensional - AWS Simple Storage

Excel vba array function multidimensional You can declare arrays with as many dimensions as you need, even in excess of 20, although you will probably not 

[PDF] Programming Excel/VBA Part II (A Fring)

レ Multidimensional arrays are VBA variables which can hold more than one item related to several index sets (up to 60) · e g a two dimensional array is a 

[PDF] VBA Notes for Professionals

treated differently by the VBA Compiler ReDim Employees(100, 5) 'declaring an 2D array that can store 100 employees with 6 elements of information each, but 

[PDF] Arrays and Collections - Access All In One

Multi-Dimensional Arrays At this point, VBA is aware that myIntegerArray will be an array Another type of array that VBA implements, is the Variant Array

[PDF] Excel - Programmation VBA - LaBRI

2 ARRAYS VBA programming - Hervé Hocquard - University of Bordeaux Multidimensional arrays – Dim MyTable(1 to 10, 1 to 10) As Integer • Assignment

[PDF] Excel vba array length - Squarespace

The following is an introduction to the basic use of arrays for Excel VBA, as well Multi-dimensional arrays are also common in VBA programs, especially when 

[PDF] Arrays in Visual Basic - VbNet

2 sept 2016 · If you are looking for help on arrays in Visual Basic for Applications (VBA), You can create a multidimensional array by using nested array 

[PDF] Working with Array Functions and DLLs in Excel VBA

Let us first look at arrays in Excel and how to use them in VBA parameters for curve fitting or multidimensional integration where the routine is written in C

[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

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