Year: 2018

[VBA] Exercises vol.2

Agnieszka

Long time since my last post:)

Today it’s so cold outside, that I finally could write next VBA code for your exercies.

Check this out

Open Your Excel and write the following six headers

Date1/Date2/Name1/Name2/Result1/Result2

choose different data format for first column a other one for second column.

f,e 12.12.2017 in DATE1 and 12 dec 2017 in DATE2

Write some names or companies, that are the same by have some small differences between like Carrefour Ltd in Name1 and Carrefour in Name2

Copy this code and see the effect of this exercise.

Sub CompareData()

Dim i, j As Integer

i = 2
j = 2

Arkusz1.Cells(i, 4) = Split(CStr(UCase(Trim(Arkusz1.Cells(i, 3)))))

For i = 2 To 10

If CDate(Arkusz1.Cells(i, 1).Value) = _
CDate(Arkusz1.Cells(i, 2).Value) Then
Arkusz1.Cells(i, 5).Value = "ok"
Else: Arkusz1.Cells(i, 5) = "not ok"
End If

If Arkusz1.Cells(i, 3) = Arkusz1.Cells(i, 4) Then
Arkusz1.Cells(i, 6) = "ok"
Else: Arkusz1.Cells(i, 6) = "not ok"
End If

If Arkusz1.Cells(i, 5).Value = "not ok" Then
Arkusz1.Cells(i, 5).Select
With Selection.Font
.Color = vbRed
End With

End If
If Arkusz1.Cells(i, 6).Value = "not ok" Then
Arkusz1.Cells(i, 6).Select
With Selection.Font
.Color = vbRed
End With

End If
Next i

End Sub

There is a way to optimse this code, but I will introduce this method in some other post:)

have a nice evening!

[VBA] Arrays

Agnieszka

we can create, in fact, multi dimensional tables, but mostly we can meet one or two dimensionals. General method is to assign a value beginning with 0. If we want to declare value starting with 1 we need to use a special fraze: Option Base 1 .

Also there’s no need to declare variable at the beginning, we can do it after.

Let’s write some simple codes :

1.simple example for one-dimensional

'simple example for one-dimensional 

Sub TableEx1()
'declare data type for one-dimensional table
Dim Tbl(3) As String
'declare figures and the meaning
Tbl(0) = "Quarter 1"
Tbl(1) = "Quarter 2"
Tbl(2) = "Quarter 3"
Tbl(3) = "Quarter 4"

'use in practise
MsgBox Tbl(0)
End Sub

'--------------------------------------------

'three-dimensional for date format dd-mm-yyyy beginning with 1 

Sub TableEx2()
'declare data type for three-dimensional table

Option Base 1
Dim Tbl(1 To 31, 1 To 12, 1950 To 2000) As Variant

Tbl(28, 10, 1989) = InputBox("What was the name of your birthday? _
Put birthdate x,x,x")

Tbl(28, 10, 1989) = WeekdayName(2, False, vbMonday)

'use in practise
MsgBox Tbl(28, 10, 1989)
End Sub

2.Two-dimensional

'for rows and columns

Sub TableEx3()
'declare data type for two-dimensional table
Dim Tbl(0, 1) As Variant
Dim x As Integer
Dim y As Integer

Tbl(0, 0) = "Header"
Tbl(0, 1) = 12345

Range("A1") = Tbl(0, 0)
Range("A2") = Tbl(0, 1)

End Sub

3.Declaring the variable after

Sub TableEx4()

Dim Tbl()
ReDim Tbl(1 To 12, 6) ' declare as two-dimensional
ReDim Tbl(1 To 50, 10, 2015 To 2030) 'declare as three-dimensional

End Sub

4. Array & Loop

Sub Looparray()

Dim numbers(1 To 3) As Integer

For i = 1 To 3

numbers(i) = InputBox("Podaj liczbę nr " & i)

Next i

For Each Number In numbers
MsgBox Number
Next

End Sub

5. Function Array, Lbound, Ubound

Sub TableEx5()
Dim table As Variant
table = Array(1, 2, 3, 4, 5)
Dim size As Integer
'size = UBound(table, 1) 'result is 4
'size = UBound(table, 1) - LBound(table, 1) + 1 'result is 5
'size = UBound(table, 1) - LBound(table, 1) 'result is 4
size = UBound(table, 1) + 1 - LBound(table, 1) 'result is 5

MsgBox size

End Sub

6. Redim Preserve

Sub ArrayEX1()

Dim numbers() As Integer
Dim size, i As Integer

size = WorksheetFunction.CountA(Worksheets(1).Columns(1))

ReDim numbers(size)

For i = 1 To size

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

Next i

MsgBox numbers(size)

ReDim Preserve numbers(3)

MsgBox numbers(1)
End Sub