Excel VBA code understanding

Question

I'm trying build a excel based input form, I have found something online and I'm trying to understand these codes:

Dim Hsheet,Isheet As Worksheet
Dim NextRow, oCol As Long
Dim MyRng, MyCell As Range
Dim MyCopy, ClearCells As String

Set Hsheet = Worksheet("InputForm")
Set ISheet = Worksheet("Database")

This is the part I don't understand, can someone explain to me please?

With Hsheet
     nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With Isheet
    Set myRng = .Range(MyCopy)

    If Application.CountA(myRng) <> myRng.Cells.Count Then
        MsgBox "Please fill in all the cells!"
        Exit Sub
    End If
End With

And also this part, can someone explain to me please?

With Hsheet
    .Cells(nextRow, "a").Value = Application.UserName
    oCol = 1
    For Each myCell In MyRng.Cells
        Hsheet.Cells(NextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With

Thanks in advance :)

With Isheet
  On Error Resume Next
     With .Range(ClearCells).Cells.SpecialCells(xlCellTypeConstants)
          .ClearContents
          Application.Goto .Cells(1) ', Scroll:=True
     End With
  On Error GoTo 0
End With

That part of the code finds the next row where you can add new data — Siddharth Rout, Jan 01 '13 at 19:15
Sure.. I have already started typing.. it's gonna be a long post so you will have to be patient with me :) — Siddharth Rout, Jan 01 '13 at 19:22
@Nucleus Maybe should you create a separate question for each part that you don't understand ? — wip, Jan 01 '13 at 19:25

score 4 · Answer 1 · edited May 23 '17 at 10:24

I can explain what the code does but there are few things which I would like to mention :)

A

Dim Hsheet,Isheet As Worksheet
Dim NextRow, oCol As Long
Dim MyRng, MyCell As Range
Dim MyCopy, ClearCells As String

This is not the right way to declare the variables/objects For example if you consider this line which is

Dim Hsheet,Isheet As Worksheet

Here, only Isheet has been declared as a worksheet and not Hsheet. The Hsheet automatically becomes a variant. The right way is

Dim Hsheet As Worksheet, Isheet As Worksheet
Dim NextRow As Long, oCol As Long
Dim MyRng As Range, MyCell As Range
Dim MyCopy As String, ClearCells As String

B

With Hsheet
     nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

What this code does is it tries to find the last row which has data in Col A and then offsets one row down to get the next empty row so that you can write to it.

Another way to write the same thing is mentioned here So the above code can also be written as

With Hsheet
     nextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
End With

C

With Isheet
    Set myRng = .Range(MyCopy)

    If Application.CountA(myRng) <> myRng.Cells.Count Then
        MsgBox "Please fill in all the cells!"
        Exit Sub
    End If
End With

I believe MyCopy is supposed to hold some value which I cannot see it in your code. Assuming that it holds a valid cell address, what the code is trying to do is to ensure that all cells are filled up by comparing the cells count vs the number of cells filled up.

D

With Hsheet
    .Cells(NextRow, "a").Value = Application.UserName
    oCol = 1
    For Each myCell In MyRng.Cells
        Hsheet.Cells(NextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With

This is also pretty straightforward. The code stores the UserName in the next available cell in Col A and then stores the values from Range MyRng in Sheet Isheet in Col A of Sheet Hsheet

HTH

+1, very thorough. Though I'd like to point out to the OP that `Applcation.UserName` returns only the `UserName` stored within the current Excel application... and should not be considered a definitive way of determining the user because it can be changed at any time. — Daniel, Jan 01 '13 at 19:42
@Nucleus Welcome to SO! Feel free to *accept* an answer that is helpful (you'll get a badge for accepting an answer!). On StackOverflow the way to say "Thanks!" is to accept an answer. That being said I'd point out that this lazy usage of `With` blocks is not contributing in a positive way to code readability; you might want to keep `With` blocks for successive accesses to the same object - typically for setting property values of a newly created object... — Mathieu Guindon, Feb 28 '13 at 21:27

wip · Accepted Answer · 2013-01-01T19:53:57.297

Your piece of code seems to do some copy from one sheet to another, adding the user name on top of a range.
However the string variable MyCopy does not seem to be initialized, therefore I don't think running this macro as is would produce any desired result (unless the Range function returns some cells when called with an empty string ? I don't know its specs).

I don't remember Excel VBA perfectly, but I think that :

Cells(.Rows.Count, "A") selects the cell located on the last row, column "A".
End(xlUp) moves the selection to the top of the range of contiguous non-empty cells (like pressing CTRL + UP in Excel, I think).
Offset(1, 0) moves the selection to one cell to the bottom. Row returns that cell's row number.

So your first code block sets the row number of the second row of the last range of non-empty cells in column A, to the variable nextRow .

You can follow the same reasoning to understand the purpose of all the other code blocks. I suggest your search MSDN's VBA for Excel documentation websites to get more information about the meaning of each function you don't understand yet.

Here is the documentation to the End property : http://msdn.microsoft.com/en-us/library/office/aa214585(v=office.11).aspx — wip, Jan 01 '13 at 19:45

Excel VBA code understanding

2 Answers2