troubleshooting Question

Need help to identify Excel objects as to what type that should be declared as for an Excel VBA operation!

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
* CodingVBAMicrosoft ExcelMicrosoft Office
7 Comments1 Solution99 ViewsLast Modified:
I have the following code that starts out like so:
What I'm having difficulty is dim the correct types for these three types of objects:  nmlist,    formcol and formcolend?

So far I have it declared as the following...

Dim nmlist As Variant
    Dim formcol As Variant
    Dim formcolEnd As Variant
    Dim destrow, i, j As Integer

But what I would like to determine is what nmlist, formcol and formcolend should be declared - not just variant?  I'm guessing that formcol and formcolend should be strings.... but I really have no clue what nmlist is and it's really important I find out!

Can anybody familiar with Excel VBA coding - determine what nmlist, formcol and formcolend should be declared as?

I put in the remarks where they exist in the code below .... ( '*** FIRST ONE TO IDENTIFY ***** and the final one:     '*** SECOND AND THIRD ONE TO IDENTIFY *****)

Thank you in advance for the answer or guess.

Public Sub GenerateOneReport(ByVal sFullName As String)
On Error GoTo Err_This

    Rem iterate through the main worksheet
    Dim awb, nwb As Workbook
    Dim pws, iws, nws, sws, tws As Worksheet
    Dim fn As String

    'originally not in the code.... added to guess
    Dim nmlist As Variant
    Dim formcol As Variant
    Dim formcolEnd As Variant
    Dim destrow, i, j As Integer

    Dim lastProj As String
    Dim thisProj As String
    Dim rgname As String
    Dim pt As PivotTable
    Dim sBodyText As String
    Dim sDirectory As String
    Dim sSubjectLine As String
    Dim sFileName As String
    Dim iArea As Integer

    sDirectory = Application.Worksheets("master").Range("StorageDir").Value

    Set awb = ActiveWorkbook
    '' status
    Application.StatusBar = "Starting process to create report for: " + sFullName

    '' fill the worksheet
    Set nwb = Workbooks.Add

    '*** FIRST ONE TO IDENTIFY *****
    Set nmlist = nwb.Names

    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)

    '' scratch tab
    Set iws = nwb.Worksheets(3) = "Scratch"

    '' do the instructions worksheet
    Set iws = nwb.Worksheets(1) = "Instructions"
    Set sws = awb.Worksheets("instructions")

    '' paste instructsions
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteValuesAndNumberFormats
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteFormats
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteColumnWidths

    '' old way sws.Rows.Copy Destination:=iws.Cells(1, 1)

    '' do the billing worksheet
    Set nws = nwb.Worksheets(2) = "Billing & Revenue"
    lastProj = ""

    '' get storage dir and other variables
    sDirectory = awb.Worksheets("master").Range("StorageDir").Value
    sSubjectLine = "Billing & Revenue Worksheet for " + awb.Worksheets("master").Range("PeriodEnding").Text

    formcol = awb.Worksheets("master").Range("FormulaCol").Value
    formcolend = awb.Worksheets("master").Range("FormulaColEnd").Value

    '' get source
    Set sws = awb.Worksheets("detail")

    '' copy the first 2 rows
    destrow = 1
    sws.Rows(1).Copy Destination:=nws.Rows(destrow)
    destrow = destrow + 1
    sws.Rows(2).Copy Destination:=nws.Rows(destrow)
    destrow = destrow + 1
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros