Vba Basic

Question 1

insert row

Answer 1

Rows(i & “:” & i ).Insert Shift:=xlDown

Question 2

delete row

Answer 2

Rows(i & “:” & i).Delete shift:=xlUp

Question 3

export table DB

Answer 3

Call JDdumpvariable2d(output,false)

Question 4

open XML:

Answer 4

Workbooks.OpenXML “C:\TEMP\AMF\feedback_20150116.xml”

Question 5

convert column number to letter

Answer 5

ColumnLetter = Split(Cells(1, ColumnNumber).Address, “$”)(1)

Question 6

Loop function:

Answer 6

in RealTimeFunction

Application.OnTime Now + TimeValue(“00:00:10”), “RealTimeFunction”

Question 7

save PDF:

Answer 7

ActiveSheet.Range(“B1:H60”).ExportAsFixedFormat Type:=xlTypePDF, fileName:=myFileName

Question 8

insert image from net:

Answer 8

Sheets(1).Shapes.AddPicture “http://www.testsite.com/pix/temp1.png” , msoFalse, msoTrue, 100, 100, 500, 600

Question 9

send email:

Answer 9

Dim olMail As Object
Dim OlApp As Object
Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.CreateItem(0)
With OlMail
    .To = myTo
    .Subject = mySubject
    '.Body = myBody
    .htmlBody = myHTMLbody
    .Attachments.add myfullPath
    .Display
End With

Question 10

is file there:

Answer 10

isFileThere = (Dir(fileName) <> “”)

Question 11

is Folder Exist

Answer 11

isFileThere = (Dir(myPath, vbDirectory) <> “”)

Question 12

find all Files in folder:

Answer 12

fileName = Dir(myPath & "*.*")
Do While fileName <> ""
   'code
   fileName = Dir()
Loop

Question 13

try to create folder

Answer 13

On Error Resume Next
‘create folder
mkdir Location
On Error Goto 0

Question 14

checkOpenFileName

Answer 14

Dim Wb As Workbook
For Each Wb In Workbooks
If InStr(Wb.Name, myFileName) <> 0 Then ‘found it
Next Wb

Question 15

change in Worksheet event:

Answer 15

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
mycolumn = Target.Cells.Column
myline = Target.Cells.Row

Question 16

change in selection event

Answer 16

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Question 17

Color a cell in red than back to blank

Answer 17

Cells(1, 1).Interior.Color = 255

Cells(1, 1).Interior.Color = xlNone

Question 18

Open Workbook

Answer 18

Workbooks.Open myPathFileName

Question 19

init Userform

Answer 19

Sub UserForm_Initialize()

Question 20

Copy file from one location to another and rename it

Answer 20

FileCopy oldDirectory & OldFileName, newDirectory & NewFileName

Question 21

get a random number 0-100

Answer 21

Randomize

mynum = Application.RoundUp(Rnd() * 100, 0)

Question 22

Read a text file line by line

Answer 22

Dim fs, f As Variant
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(pathFileName, 1, , 0)
 While f.atendofline = False
        sloc = f.readline
'do things
Wend
f.Close

Question 23

Open text file to append it

Answer 23

Sub OpenTextFileTest
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fs, f
Set fs = CreateObject(“Scripting.FileSystemObject”)
Set f = fs.OpenTextFile(“c:\testfile.txt”, ForAppending, TristateFalse)
f.Write “Hello world!”
f.Close
End Sub

Question 24

Connect to DB

Answer 24

Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim oCmd As ADODB.Command
sConnString = "..."
    Set conn = New ADODB.Connection
    conn.CommandTimeout = 60
    Set rs = New ADODB.Recordset

If left(UCase(mysql), 6) = "SELECT" Then
        Set rs = conn.Execute(mysql)
        If Not rs.EOF Then Call JDrstomat(rs, result)
    Else
        Set oCmd = New ADODB.Command
        With oCmd
            .ActiveConnection = conn
             .CommandType = adCmdText
            .CommandText = mysql
            .Execute , , adExecuteNoRecords
        End With

End If

Question 25

Format the column M with number comma separation

Answer 25

Columns(“M:M”).NumberFormat=“#,##0”

Question 26

Risize columns

Answer 26

Columns(A:B).EntireColumn.Autofit

Question 27

Remove autocorrect

Answer 27

Application.autocorrect.replacetext=false

Question 28

Email on behalf of someone

Answer 28

Set OutMail = OutApp.CreateItem(0) | Set OutMail.SendUsingAccount = OutApp.Session.Accounts.Item("xxx@xxx.com")