+ Reply to Thread
Results 1 to 6 of 6

Problems getting started, Defining variables and creating functions

  1. #1
    Registered User
    Join Date
    08-08-2019
    Location
    Netherlands
    MS-Off Ver
    MS office 2016
    Posts
    3

    Problems getting started, Defining variables and creating functions

    Hello all,

    I am an engineering student currently working at an engineering firm until the end of the summer. At work I am asked to create a system so that progress can be reported more easily. The problem should be a fairly easy problem but I have some problems getting started with VBA.

    To report the progress I want to create a CommandButton in excel which creates a table where the progress of several (sub)projects are shown. At my company we already have a tool which exports a excel file in which all the need data is shown (if filled in correctly by engineers and project managers). I would like to create a excel file where the data exported form the tool can be stored and a CommandButton which creates a table with the processed data is created.

    - I understand that there are nicer ways to solve this problem but for now this is the simplest and quickest way to solve the problem. -

    In university I have done some programming in Matlab, this where mathematical operations. Since i have only experience in Matlab I am used to working with scalars, vectors and matrices only. I have asked a colleague to help me get started and he explained a little bit about defining your variables. So I already have my workbook and worksheets defined correctly.

    When I start writing my code I keep on bumping on some errors and even google cannot give me the correct answers. I will try to explain my problem as clearly as possible.

    To be able to collect the data I need to create a range. Because the exported data can change the range needs to be variable.
    Please Login or Register  to view this content.
    This gives me the correct numbers of Row to use for my range, however I have trouble using this number as the limit of my range. I cannot find a way to use this a my range.
    Please Login or Register  to view this content.
    I now have defined H as a Value but I cannot use this in a function? Can't I use a Long variable?

    I have been working on this single problem for two days and I seem to be not understanding something. I really like the whole programming thing and hope to understand VBA and create some usefull macros. I am forever grateful to who can help me get started.
    Last edited by thyspaleis; 08-09-2019 at 04:28 AM.

  2. #2
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    399

    Re: Problems getting started, Defining variables and creating functions

    Are you using the instruction

    Please Login or Register  to view this content.
    at the beginning of your code?
    What error does the VBA compiler detect?

    The following code works fine

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  3. #3
    Registered User
    Join Date
    08-08-2019
    Location
    Netherlands
    MS-Off Ver
    MS office 2016
    Posts
    3

    Re: Problems getting started, Defining variables and creating functions

    Thank you for you help.

    The Option Explicit is in the beginning of my code.

    The Error which VBA is giving is:
    Run-time error '1004':
    Application-defined or object-defined error

    I have puzzled around a bit in my code and found out that this also happens when in define my range with numbers instead of a variable.
    Please Login or Register  to view this content.
    This error does not pop up however when I define my range using the names of the cells.
    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    399

    Re: Problems getting started, Defining variables and creating functions

    The instruction

    Please Login or Register  to view this content.
    will refer to Cells in the currently active sheet.

    From what you wrote I guess you are using different Worksheets, so I would suggest to add the name of the Sheet where you want the range to be defined, for instance

    Please Login or Register  to view this content.
    Actually my previous code will fail (with the same 1004 error) if you add a new Sheet and run the macro from there.

    Your second instruction instead is working because
    Please Login or Register  to view this content.
    is fully qualified by
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-08-2019
    Location
    Netherlands
    MS-Off Ver
    MS office 2016
    Posts
    3

    Re: Problems getting started, Defining variables and creating functions

    Thanks you so much, that was indeed the problem.

    I think I had didn't understand the structure yet.

    So if I understand it correctly in this code.
    Please Login or Register  to view this content.
    wbBook is the workbook where the range is stored
    Worksheets("One") is in which worksheet the range is stored
    and then
    Range(Worksheets("One").Cells(1, 1), Worksheets("One").Cells(2, 2)) is the range taken from a worksheet "One" and the different Cells. For what I understood I already stated that the range should be taken from Worksheets("One") before the .Range

    So if I create a variable I have to assign this to a Worksheet. Isn't it possible to create a variable which is not linked to a worksheet? It seems to like extra work if you want to use one variable in multiple sheets.

    Anyway. I am really helped getting started. Thank you for this.

  6. #6
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    399

    Re: Problems getting started, Defining variables and creating functions

    Quote Originally Posted by thyspaleis View Post
    For what I understood I already stated that the range should be taken from Worksheets("One") before the .Range
    The objects Range and Cells had me stumped quite often at the beginning, and I still fail at them sometimes.
    You can find a lot of tutorials online but the main lesson is that Cells needs to be qualified, otherwise your code will behave erratically.

    Quote Originally Posted by thyspaleis View Post
    So if I create a variable I have to assign this to a Worksheet. Isn't it possible to create a variable which is not linked to a worksheet?
    Well, a variable (e.g. the number of rows) can be defined globally. But a range is a specific area linked to a worksheet. For instance the following code

    Please Login or Register  to view this content.
    creates two ranges in two different sheets using the same variable. The macro will run from both sheets. Please note however that I'm not checking for their existence.
    The With statement is used to save typing, otherwise we should write

    Please Login or Register  to view this content.
    HTH
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hi My Name is Mike Aujla started learning VB and excel functions
    By mikeaujla in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-22-2015, 12:47 AM
  2. Problems with defining global variables
    By sbe70 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-18-2015, 05:33 AM
  3. Defining variables in VBA
    By beni12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-26-2011, 03:01 PM
  4. Getting started with functions (Programming?)
    By neophyte in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2010, 09:24 AM
  5. defining the variables as values
    By sportsranker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2009, 05:00 PM
  6. VBA defining variables
    By Jeff in forum Excel General
    Replies: 2
    Last Post: 11-03-2005, 07:40 PM
  7. solver and defining all variables different than one another
    By excel_excel_excel in forum Excel General
    Replies: 0
    Last Post: 07-19-2005, 03:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1