We help IT Professionals succeed at work.

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

96 Views
Last Modified: 2017-04-06
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

Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
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 LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
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

Author

Commented:
Now it seemed to work with Dim nmlist as Names.... though.  Testing it further.
Protect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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 LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions