Avatar of stephenlecomptejr
Flag for United States of America asked on

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

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
    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)
    nws.name = "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

Open in new window

* CodingVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

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

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

Open in new window

Martin Liss

Oh, and by the way...

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

Open in new window


Hey Martin, When I do a Dim nmlist as Name - I get an error:  Run-time error '13' Type mismatch.  Please note attachments.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Now it seemed to work with Dim nmlist as Names.... though.  Testing it further.
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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 Liss

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.