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

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
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)
    iws.name = "Scratch"

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

    '' paste instructsions
    sws.Rows.Copy
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteValuesAndNumberFormats
    sws.Rows.Copy
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteFormats
    sws.Rows.Copy
    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)
    nws.name = "Billing & Revenue"
    nws.Activate
    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

    '*** SECOND AND THIRD ONE TO IDENTIFY *****
    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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Not exactly what you asked about but when you do
 Dim destrow, i, j As Integer

Open in new window

only j is an Integer and the other two are Variants. So instead do
Dim destrow As Integer, i As Integer, j As Integer

Open in new window

or
Dim destrow As Integer
Dim i As Integer
Dim j As Integer

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Oh, and by the way...

    Dim nmlist As Name
    For Each nmlist In Names
        ' do something
    Next

Open in new window

Author

Commented:
Hey Martin, When I do a Dim nmlist as Name - I get an error:  Run-time error '13' Type mismatch.  Please note attachments.
error1.PNG
error2.PNG
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Now it seemed to work with Dim nmlist as Names.... though.  Testing it further.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Names is the collection of Named Ranges while Name is a single Named Range.
 
In the code that you originally posted and also in the pictures you didn't show what you want to do with nmlist. My code shows how to use nmlist to iterate through the Names collection.

Author

Commented:
Thank you for your help.

Dim nmlist as Names   was the one that worked for me.   I never could have arrived to the correct answer without your help.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial