Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

George Spell > VBA > Flashcards

VBA Flashcards

learn VBA

1
Q

Select all cells in Current region, VBA and keyboard?

A

Selection.CurrentRegion.Select; Ctrl+Shift+8 (*)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Return the color index to a Message Box

A

MsgBox Selection.Interior.ColorIndex

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How do you force the declaration of variables?

A

Tools/Options (in VBA). Add tick to “Require Variable Declaration”.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What does Option Explicit do?

A

Make you declare all variables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the scope of a variable

A

How far it reaches

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How is a variable declared locally (within a single procedure)?

A

By using a Dim or Static statement within the procedure.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How is a variable declared within the current module?

A

By using a Dim statement before the first Sub or function.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What does a Public statement do?

A

Declares a variable in all Subs and Functions in all modules in the current workbook

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What does Static do when used in place of Dim?

A

Freezes the variable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the shortcut for Step Over in debugging?

A

Shift + F8

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Shortcut for Debug run to cursor (runs up until the cursor)

A

Ctrl + F8

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How do you add a Watch

A

Debug menu, Add Watch

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How do you step out of a sub procedure

A

Ctrl + Shift + F8

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

When debugging, how do you add a break point and what does it do?

A

LHB in left margin. It breaks the sub.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How do you halt your code if debugging when a criteria is satisfied?

A

Insert “Debug.Assert” with a criteria such as “s < 5”. Or you can use “Debug Menu, Add Watch.. (Break When Value is True)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How do you change from absolute to relative references when recording a Macro?

A

Developer Tab, Use Relative References

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is counter intuitive about Debug.Assert

A

Debug.Assert runs until the criteria is FALSE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What should you avoid when using Dim in functions

A

Redimming of arguments

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

in functions, why (a as Double, b as Double) As Double

A

Because we also DIM th output

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How do you debug a function?

A

Break point. In Excel, click into the function, hit Enter. in VBA you can now step through.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Why should you avoid message boxes in cells?

A

Because answer does not get returned to cell and because if copy to multiple cells, you get as many pop ups!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

The order of VBA operators

A

^, -, *,/ ,\, mod, +/-, string, relational operators, logical operators

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are properties

A

Attributes of objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What are methods

A

actions to be taken on objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What are events

A

happenings that objects respond to

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

How do you refer to the range “A1” in the worksheet “Data”

A

Worksheet (“Data”).Range(“A1”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is a difference between a range and a selection

A

A range does not change

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

How to you assign a value to a variable “x” via an Input Box

A

x = InputBox (“????”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

How do you assign the value of the 2nd cell across and 2nd cell down to x

A

x = Selection.Cells (2,2)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

How do you move 2 cells down and 2 cells to the right

A

ActiveCell.Offset (2,2)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

How many variables is it good practice to have?

A

As many as possible as you can monitor in the Locals window

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

How do you assign a cell to a variable?

A

Dim Rng as Range Set RNG = Workbooks (“WorkbookName.xlsx”).Worksheets (“SheetName”).Cells (1,1)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

How do you assign a range to a variable?

A

Dim Rng as Range Set Rng = Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).Range(“A1:D4”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

How do you select range by address?

A

Range(“A1:D4”).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

How do you select cells by row and column?

A

Cells(1,1).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

How do you change the value of a range by address?

A

Range(“A1:D4”).Value = “Text Here”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Using the cells function, select A1 in 2 ways

A

Cells(1,1).Select Cells(1,”A”).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

How do you select the Row and Column of the active cell?

A

Cells(ActiveCell.Row,ActiveCell.Column)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

How do you move the cursor to row 1 but stay in the same column?

A

Cells(1,ActiveCell.Column).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

How do you select column D

A

Columns(“D”).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

How do you select rows 4 and 9

A

Range(“4:4,9:9”).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

How do you get to the bottom of a range?

A

Selection.End(x1Down).Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Give an example of how to use a message box to make a decision about whether to delete or not?

A

Sub CellNavigation() If MsgBox(“Delete data?”, vbYesNo) = vbYes _ Then Cells.Delete MsgBox “Data Deleted” Else MsgBox “Data Not Deleted” End If End Sub

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What are the VBA Message Box Buttons available?

A

vbOKOnly displays OK vbOKCancel displays OK - Cancel vbAbortRetryIgnor vbYesNoCancel vbYesNo vbRetryCancel

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What is the vb value of the Cancel button on the Message Box

A

vbCancel

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

How would you describe the difference between the active cell and the selected cell?

A

many cells can be selected, but only one may be the active cell at any given time

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

If the active cell is A1, what cell will ActiveCell.Offset(1,2) select

A

C2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

What can be data can be extracted from the following cell which is the active cell ABC-123 which is in cell B3?

A

ActiveCell.Address ActiveCell.Address(0,0) = B3 AcitveCell.Column = 2 ActiveCell.Row = 3 ActiveCell.Width = 48 ActiveCell.height = 15 ActiveCell.interiorColor = 16777215 Left(AciveCell,3)=ABC Len(AciteCell) = 7

49
Q

How would you return the Color Index of cell A1 to a message box

A

MsgBox Range(“A1”).Interior.ColorIndex

50
Q

How would you change the color index of cell A1 to 48?

A

Range(“a1”).Interior.ColorIndex = 48

51
Q

Copy A1:A4 to B1:B4

A

Range(“A1:A4”).Copy Range(“B1:B4”)

52
Q

Write script to count the rows and columns in A1:C5 using nr and nc as variables.

A

Dim nr As Integer, nc As Integer nr = Range(“A1.C5”).Rows.Count nc = Range(“A1.C5”).Columns.Count

53
Q

How do you get data into subs?

A

Input box Active Cell (chosen before sub run) Selection (chosen before sub run) A fixed range

54
Q

How do you get data out of subs

A

Message box Active Cell (chosen before sub run) Selection (chosen before sub run) A fixed range

55
Q

What does Cells (5,2) refer to in range terms?

A

Range (“E2”)

56
Q

What does the property cells refer to?

A

All the cells in the corresponding object.

57
Q

What does “Selection” have in common with “object”

A

Most of the time they can be used interchangeably.

58
Q

For “Program Flow”, what are the 3 fundamental structures

A

Sequence Selection Repetition

59
Q

what is the answer to 5 mod 2

A

5/2 which is 2 and 1/2, so the mod is 1

60
Q

Write code to produce a message if an InputBox number is even

A

Sub MessageIsEven() Dim x As Integer x = InputBox(“enter value”) If x Mod 2 = 0 Then MsgBox (“even steven”) End If End Sub

61
Q

What is the basic structure for multi-alternative if..then

A

If Then ElseIf Then ElseIf Then Elseif Then End If

62
Q

What is the value of a variable if you do not set its value?

A

0

63
Q

What is the normal structure for a basic “General Loop”?

A

Do

pre-test code block

IfThen Exit Do

post-test code block

Loop

If required MsgBox or other output

64
Q

Simple Do Loop for testing if x > 5

A

Sub DoLoop() Dim x As Integer Do x = x +2 If x > 20 Then Exit Do x = x -1 Loop MsgBox (x) End Sub

65
Q

What is difference between a basic loop and a Do While loop

A

With the basic loop, you exit when the condition is true. For a do While you keep going while the condition is true.

66
Q

Code for Do Until i=6

A

Sub dountil() Dim i As Integer Do i = i + 1 Loop Until i = 6 MsgBox i End Sub

67
Q

Write code to validate that % input is between 0 and 100, outputing a message if not validated.

A

Sub ValidateInput() Dim P As Double Do P = InputBox(“Enter percent conversion:”) If P >= 0 And P <= 100 Then Exit Do MsgBox (“Percentage must be between 0 and 100”) Loop End Sub

68
Q

Sub to delete cells in a given column using ActiveCell

A

Sub Delete99() Dim nr As Integer, i As Integer nr = Selection.Rows.Count For i = 1 To nr If ActiveCell = -998 Or ActiveCell = -999 Then ActiveCell.Clear End If ActiveCell.Offset(1, 0).Select Next i End Sub

69
Q

Sub to delete cells in a given column using Selection where value is –999

A

Sub Delete999() Dim nr As Integer, i As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = -999 Then Selection.Cells(i, 1) = “” End If Next i End Sub

70
Q

Write a sub to bubblesort

A

Sub bubblesort() Dim n As Integer, i As Integer, j As Integer Dim Temp As Double n = Selection.Rows.Count For i = 2 To n For j = 2 To n If Selection(j - 1, 1) > Selection(j, 1) Then Temp = Selection.Cells(j, 1) Selection.Cells(j, 1) = Selection.Cells(j - 1, 1) Selection.Cells(j - 1, 1) = Temp End If Next j Next i End Sub

71
Q

how do you get the month from a cell with a date in it?

A

Month(Range(“A1”))

72
Q

What value will Weekday(Date, vbMonday) return if the current day is Monday?

A

1

73
Q

What is the general form of a one way if statement?

A

General Form:- If Then statements End If

74
Q

Give an example of a one way if statement. Also show as a one line statement.

A

If x = 6 Then y = 2 End If If x = 6 Then y =2

75
Q

How do you test if an activecell is empty

A

If IsEmpty(ActiveCell) Then

76
Q

How to you replace the active Cell with zero if it is empty (sub)? How can you modfiy the second line of code?

A

Sub ReplaceBlankWithZero() If ActiveCell = “” Then ActiveCell = 0 End If End Sub Replace with If IsEmpty (AcitveCell) Then

77
Q

Write a sub to add 20 to the active cell if +ve and deduct 20 if -ve

A

Sub testcell() If ActiveCell > 0 Then ActiveCell = ActiveCell + 20 Else ActiveCell = ActiveCell - 20 End If End Sub

78
Q

Function that will output “biz” if the number is divisible by 3, “buz” if the number is divisible by 5, and “bizbuz” if the number is divisible by 3 and 5.

A

Function bizbuz(x As Integer) As String

If x Mod 3 = 0 Then

If x Mod 5 = 0 Then

bizbuz = “bizbuz”

Else bizbuz = “biz”

End If

Else If x Mod 5 = 0 Then

bizbuz = “buz”

End If

End If

End Function

79
Q

What is the basic structure for a nested 2 level IF

A

If If Else End If Else If End If End If

80
Q

How would you create a function grade in VBA that would assign a letter grade to a numeric score? 90 =< score<100 A 80 =< score<90 B 70 =< score<80 C 60 =< score<70 D score < 60 F

A

Function Grade(score As Double) As String If score >= 90 Then Grade = “A” ElseIf score >= 80 Then Grade = “B” ElseIf score >= 70 Then Grade = “C” ElseIf score >= 60 Then Grade = “D” Else Grade = “F” End If End Function

81
Q

Create a VBA sub that will: Choose a number between 1 and 10 (inclusive) then perform the “guessing game” until the user guesses the chosen number.

A

Sub guessingame() Dim g As Integer, r As Integer r = WorksheetFunction.RandBetween(1, 10) Do g = InputBox(“have a guess between 1 and 10”) If g = r Then Exit Do MsgBox (“guess again”) Loop End Sub

82
Q

Create a function that will count the number of the first n integers that are divisible by either 3 or 5?

A

Function Divisible(n As Integer) As Integer Dim i As Integer, c As Integer For i = 1 To n If i Mod 3 = 0 Or i Mod 5 = 0 Then c = c + 1 End If Next i Divisible = c End Function

83
Q

Create a VBA sub that will count the number of 5’s in a selection (column vector)?

A

Sub CountFives() Dim nr As Integer, i As Integer, c As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = 5 Then c = c + 1 Next i MsgBox (“There are “ & c & “ fives in your selection”) End Sub

84
Q

Find the 7s in a selection in Column A and output the row number of each instance of a 7 to column c starting in row 1.

A

Sub FindSeven() Dim i As Integer, nr As Integer, c As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = 7 Then c = c + 1 Range(“C” & c) = i End If Next i End Sub

85
Q

At what value to indicees exit?

A

At one greater than their last value.

86
Q

Add to each element in a selection (array)

A

Sub AddFive()
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer
nr = Selection.Rows.Count
nc = Selection.Columns.Count
For i = 1 To nr
For j = 1 To nr
Selection.Cells(i, j) = Selection.Cells(i, j) + 5
Next j
Next i
End Sub

87
Q

Create a 2 by 2 Array

A

Option Base 1
Sub CreateArray()
Dim A(2, 2) As Integer
A(1, 1) = 3
A(1, 2) = 5
A(2, 1) = 4
A(2, 2) = 1
Range(“A1:B2”) = A
End Sub

88
Q

Create a VBA function that counts the number of elements in an array that are divisible by n

A

Function CountDivisibleByN(rng As Range, n As Integer) As Integer
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer, c As Integer
nr = rng.Rows.Count
nc = rng.Columns.Count
For i = 1 To nr
For j = 1 To nc
If rng.Cells(i, j) Mod n = 0 Then
c = c + 1
End If
Next j
Next i
CountDivisibleByN = c
End Function

89
Q

Add 5 to each element in a selection but place the result in a different range (2 rows down, same column)

A

Sub AddFive()
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer
nr = Selection.Rows.Count
nc = Selection.Columns.Count
For i = 1 To nr
For j = 1 To nc
ActiveCell.Offset(nr + i, j - 1) = Selection.Cells(i, j)
Next j
Next i
End Sub

90
Q

How do you Create a 2 x 2 Array?

A

Sub CreateArray()
Dim A(2, 2) As Integer
A(1, 1) = 3
A(1, 2) = 5
A(2, 1) = 4
A(2, 2) = 1
Range(“A1:B2”) = A
End Sub

91
Q

How would you populate cells A1 to B2 using VBA and an input box to collect the inputs from the user?

A

Sub CreateArray()
Dim A(2, 2) As Integer, ia As Integer, j As Integer
For i = 0 To 1
For j = 0 To 1
A(i, j) = InputBox(“Please ener element “ & i & “,” & j)
Next j
Next i
Range(“a1:b2”) = A
End Sub

92
Q

How do you dim an array when you don’t know the dimensions? And what do you do once you know the array dimensions

A

a. Dim A() As Integer
b. ReDim A (nr,nc) as integer

93
Q

How do you make an array start at 1 instead of zero?

A

Type “Option Base 1” before the sub

94
Q

Code to take a selection in Excel and store in VBA as Static array

A 
Sub ImportArray()
Static A() As Variant
A = Selection
MsgBox A(2, 2)
End Sub
95
Q

How to you populate a selection in Excel from an array in VBA using a fomula

A

Option Base 1
Sub ExportArray()
Dim i As Integer, j As Integer
Dim A(3, 3) As Integer
For i = 1 To 3
For j = 1 To 3
A(i, j) = 2 * i + j
Next j
Next i
Selection = A
End Sub

96
Q

Export an array to a specified range in VBA with a formula to calc the VBA values to export?

A

Sub ExportArray()
Dim i As Integer, j As Integer
Dim A(3, 3) As Integer
For i = 1 To 3
For j = 1 To 3
A(i, j) = 2 * i + j
Range(“B2:D4”).Cells(i, j) = A(i, j)
Next j
Next i
End Sub

97
Q

Function to count the number of items divisible by a specified integer n in a selection specified.

A

Function CountDivisibleByN(rng As Range, n As Integer) As Integer
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer, c As Integer
nr = rng.Rows.Count
nc = rng.Columns.Count
For i = 1 To nr
For j = 1 To nc
If rng.Cells(i, j) Mod n = 0 Then
c = c + 1
End If
Next j
Next i
CountDivisibleByN = c
End Function

98
Q

Array Function for returning whether a cell has a value of n. 1 for true, zero for false.

A

Option Base 1
Function DivisibleByN(rng As Range, n As Integer) As Variant
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer
Dim B() As Variant
nr = rng.Rows.Count
nc = rng.Columns.Count
ReDim B(nr, nc) As Variant
For i = 1 To nr
For j = 1 To nc
If rng.Cells(i, j) Mod n = 0 Then
B(i, j) = 1
End If
Next j
Next i
DivisibleByN = B
End Function

99
Q

Create a VBA array function “SortVector” that sorts an input vector in ascending order

A

Function SortVector(rng As Range)
Dim i As Integer, j As Integer, n As Integer
Dim Temp As Integer, A() As Variant
n = rng.Rows.Count
ReDim A(n, 1)
A = rng
For i = 2 To n
For j = 2 To n
If A(j - 1, 1) > A(j, 1) Then
Temp = A(j, 1)
A(j, 1) = A(j - 1, 1)
A(j - 1, 1) = Temp
End If
Next j
Next i
SortVector = A
End Function

100
Q

What must Array functions output as?

A

Variant

101
Q

ReDim preserve sub to return a shopping list based on user input?

A

Option Explicit
Option Base 1
Sub ShoppingList()
Dim i As Integer, ans As Integer, L() As Variant
Do
ans = MsgBox(“Would you like to add an item to the shopping list?”, vbYesNo)
If ans = 7 Then Exit Do
i = i + 1
ReDim Preserve L(i) As Variant
L(i) = InputBox(“Enter new item:”)
Loop
Range(“A1:A” & i) = WorksheetFunction.Transpose(L)
End Sub

102
Q

Create a VBA array function called diagonals that extracts the diagonal elements of a square matrix and places them in a column vector

A

Option Base 1

Function Diagonals(rng As Range)
Dim i As Integer, n As Integer
Dim D()
n = rng.Rows.Count
ReDim D(n, 1)
For i = 1 To n
D(i, 1) = rng.Cells(i, i)
Next i
Diagonals = D
End Function

103
Q

Use a message box to outpu part of a string using Mid in VBA

A

Sub Strings()
Dim s As String
s = “pneumonoultranisc”
MsgBox Mid(s, 5, 7)
End Sub

104
Q

Find the length of a string in VBA

A
105
Q

Sub Strings()
Dim s As String
s = “pneumonoultranisc”
MsgBox Len(s)
End Sub

A
106
Q

Find text position of “tran” in a string

A

Sub Strings()
Dim s As String
s = “pneumonoultranisc”
MsgBox InStr(s, “tran”)
End Sub

107
Q

Find the position of the second No in the string “pneumonoultraniscno”

A

Sub Strings()
Dim s As String
s = “pneumonoultraniscno”
MsgBox InStr(15, s, “no”)
End Sub

108
Q

Msg Box a string in upper case

A

Sub Strings()
Dim s As String
s = “pneumonoultraniscno”
MsgBox UCase(s)
End Sub

109
Q

What are VBA options for concatenation?

A

“&”

Join

110
Q

Join 3 strings with “A”, “B”, and “C” in them

A

Option Base 1
Sub JoinStrings()
Dim B(3) As String
B(1) = “A”
B(2) = “B”
B(3) = “C”
MsgBox Join(B)
End Sub

111
Q

Join 3 strings with “A”, “B”, and “C” in them with spaces removed

A

Option Base 1
Sub JoinStrings()
Dim B(3) As String, Joinedmsg As String, msg As String
B(1) = “A”
B(2) = “B”
B(3) = “C”
Joinedmsg = Join(B)
For i = 1 To Len(Joinedmsg) Step 2
msg = msg + Mid(Joinedmsg, i, 1)
Next i
MsgBox msg
End Sub

112
Q

Join 3 strings with “AB”, “CDE”and “FG” in them with spaces removed

A

Option Base 1
Sub JoinStrings()
Dim B(3) As String, Joinedmsg As String, msg As String
Dim i As Integer
B(1) = “AB”
B(2) = “CDE”
B(3) = “FG”
Joinedmsg = Join(B)
For i = 1 To Len(Joinedmsg)
If Not Mid(Joinedmsg, i, 1) = “ “ Then
msg = msg + Mid(Joinedmsg, i, 1)
End If
Next i
MsgBox msg
End Sub

113
Q

Ask user for a sentence and split into a vector in VBA

A

Sub SplitAndJoin()
Dim sentence As String, A() As String
sentence = InputBox(“Please enter a sentence.”)
A = Split(sentence, “ “)
End Sub

114
Q

Ask user for a sentence and msg box one word at a time.

A

Sub SplitAndJoin()
Dim sentence As String, A() As String
sentence = InputBox(“Please enter a sentence.”)
A = Split(sentence, “ “)
For i = 0 To UBound(A)
MsgBox A(i)
Next i

MsgBox Join (A)
End Sub
115
Q

Ask user for a sentence and msg box one word at a time and Msg Box the whole sentence

A

Sub SplitAndJoin()
Dim sentence As String, A() As String
sentence = InputBox(“Please enter a sentence.”)
A = Split(sentence, “ “)
For i = 0 To UBound(A)
MsgBox A(i)
Next i
MsgBox Join(A)
End Sub

116
Q

Take a string in a column in VBA and combine into a single word.

eg, the folowing vector is selected

G

A

T

T

A

C

A

A

Sub CombineColumn()
Dim B As Object, msg As Variant, i As Integer, nr As Integer
Set B = Selection
nr = Selection.Rows.Count
For i = 1 To nr
msg = msg + B(i)
Next i
MsgBox msg
End Sub

117
Q

extract part numbers from 67-345-23

A

Option Base 1
Function parts(s As String) As Variant
Dim L As Integer
Dim firstdash As Integer, seconddash As Integer
L = Len(s)
Dim p(3) As Variant
firstdash = InStr(1, s, “-“)
seconddash = InStr(firstdash + 1, s, “-“)
p(1) = Left(s, firstdash - 1)
p(2) = Mid(s, firstdash + 1, seconddash - firstdash - 1)
p(3) = Right(s, L - seconddash)
parts = p

End Function

118
Q

Take a string from a user and export to a text file.

A
119
Q

When do you need to use WorksheetFunction.Transpose(?)

A

on string vectors because they are row vectors.

George Spell (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions