Use Excel Worksheet like Application

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1378 Views - Last Post: 05 June 2013 - 03:12 PM Rate Topic: -----

#1 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Use Excel Worksheet like Application

Posted 03 June 2013 - 12:16 PM

The big picture:

It used to be in this particular workflow I'm modifying that a program called "FormFlow" was used to enter data and then create .DBF files that are uploaded (transferred?) into AS400 system. Higher ups have stated that it is necessary to move the workflow out of using "FormFlow" application (for some reason). Powers that be decided to move forward with using an Excel Spreadsheet as the replacement component for this functionality. The spreadsheet has now been mocked up, but lacks the process functionality. This is where I currently stand.

  • One of the tabs in my worksheet acts as a "Form". This form allows users to fill out an inspection. Several fields on the form are using VLOOKUP() to auto-fill sections based on ID Numbers.
  • One of the tabs acts as a data table. This tab is used to store what is entered into the main form as a record, like values in a data table.
  • What I need to do is code 5 command buttons on the main form tab. A "NEXT", "PREV", "CLEAR", "SAVE" & "CONVERT".
  • NEXT & PREV need to cycle up and down through the "data table" page, displaying that records information on the main form as records are cycled through.
  • SAVE needs to save what is entered on the main form to the next available row on the "data table" page.
  • CONVERT needs to take all records in the "data table" page and place them in a dBase (.DBF) file.
  • CLEAR as in all programs needs to clear the form for new entry.


I am very green when it comes to programming an Excel Spreadsheet. Based on what I was able to find so far, it looks like the convert tab "records" to .DBF is going to be very difficult if impossible. To start the easy part I tried setting up the CLEAR functionality. I did a "Record Macro" and from button click deleted the contents of each cell I wanted to cleared, however, when I deleted the contents it also deleted the VLOOKUP() formulas that are in use for certain cells.

The few .DBF files I was able to find for review I am unable to open with Notepad++...

My first question then becomes how to code a CLEAR button that will clear contents of specified cells without deleting the formula associated with it?

Any help at all on this would be very appreciated :/ I apologize significantly for any major misuses of vocabulary. Excel is not something I ever dabble in.

Is This A Good Question/Topic? 0
  • +

Replies To: Use Excel Worksheet like Application

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 03 June 2013 - 12:35 PM

View PostAnalyticLunatic, on 03 June 2013 - 07:16 PM, said:

My first question then becomes how to code a CLEAR button that will clear contents of specified cells without deleting the formula associated with it?

Deleting the content of a cell will not delete any associated formulas in other cells. It may cause the functions to display as errors, if referencing an empty cell would normally cause this behaviour.

It may be that, when you recorded your macro, you deleted some cells that contained formulas. Try recording again. There is also the possibility that you had the option to Use Relative References selected when recording the macro and, when you ran the macro, it deleted the content of different cells (to the ones you originally deleted).

Deleting cell content:

Range("A1").ClearContents


Quote

The few .DBF files I was able to find for review I am unable to open with Notepad++...

You cannot open database files in a (simple) text editor. Writing data to a database is not necessarily complicated. It is complicated, however, if you know little about them, so you may need to study database fundamentals.

This post has been edited by andrewsw: 03 June 2013 - 12:38 PM

Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 03 June 2013 - 12:43 PM

I trust that you are not intending to create your application as a shared spreadsheet file?! This won't work IMO.

This post has been edited by andrewsw: 03 June 2013 - 12:43 PM

Was This Post Helpful? 0
  • +
  • -

#4 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Re: Use Excel Worksheet like Application

Posted 03 June 2013 - 12:54 PM

View Postandrewsw, on 03 June 2013 - 07:35 PM, said:

Deleting the content of a cell will not delete any associated formulas in other cells. It may cause the functions to display as errors, if referencing an empty cell would normally cause this behaviour.

It may be that, when you recorded your macro, you deleted some cells that contained formulas. Try recording again. There is also the possibility that you had the option to Use Relative References selected when recording the macro and, when you ran the macro, it deleted the content of different cells (to the ones you originally deleted).


After taking a moment to breathe and think it through I see what I did wrong. You are right andrewsw, I deleted all user-input area on the form including the ones that get their values based on VLOOKUP() functions. I need only clear the contents of cells not using the VLOOKUP()'s.

Things are progressing forward now. When I clear the appropriate fields now, all the VLOOKUP() cells show a value of "#N/A" until the cells they are looking up are given values. While not a large issue, is there a way to show these as BLANK instead of "#N/A"?

View Postandrewsw, on 03 June 2013 - 07:43 PM, said:

I trust that you are not intending to create your application as a shared spreadsheet file?! This won't work IMO.


At this point I am the low man on the totem pole with all major decision making players on vacation at the moment. I am certainly with you in hopes this is not intended to be a shared spreadsheet file!

Personally I don't know why it was decided to make Excel act like Access instead of using Access as Access, but that's why I make the small bucks. This project is just interesting enough of a request that I'm willing to do it just to say I have done it before.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 03 June 2013 - 12:59 PM

Excel 2007 or 2010:

=IFERROR(VLOOKUP(E4,E6:H11,3,FALSE),"")

Excel 2003-:

=IF(ISNA(VLOOKUP(E4,E6:H11,3,FALSE)),"",VLOOKUP(E4,E6:H11,3,FALSE))

This post has been edited by andrewsw: 03 June 2013 - 12:59 PM

Was This Post Helpful? 1
  • +
  • -

#6 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Re: Use Excel Worksheet like Application

Posted 03 June 2013 - 01:17 PM

View Postandrewsw, on 03 June 2013 - 07:59 PM, said:

Excel 2007 or 2010:

=IFERROR(VLOOKUP(E4,E6:H11,3,FALSE),"")

Excel 2003-:

=IF(ISNA(VLOOKUP(E4,E6:H11,3,FALSE)),"",VLOOKUP(E4,E6:H11,3,FALSE))


That did the trick! Could you give me any examples of, for instance, taking the value of Cell [A3] and storing it in the first empty cell of column A on another worksheet? (for the SAVE functionality)
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 03 June 2013 - 01:22 PM

Probably..

Worksheets("SomeOther").Range("A1").End(xlDown).Offset(1,0).Value = [A3]
Was This Post Helpful? 1
  • +
  • -

#8 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 06:28 AM

Ok, I've now got the CLEAR and SAVE functionality in place (though I need someone higher up to tell me how 20 something fields on a form fill 40 something columns in the "data table").

What would be the best practice for cycling through the records on the data table page and displaying the information of each "record" on my "form"? I know how to do this in Access, but Excel...

I'm having trouble grasping how to register which "row" the cycler is at so it can know whether to display "up" or "down" in the table.
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 06:50 AM

In VB.NET et al. there are a number of ways to hook up a form to database tables, so that they remain in-sync. Here, you are using a worksheet as your form and a worksheet table as your database, so it requires a little effort ;).

The data-worksheet should just be a single table, starting at A1, with a header-row, etc. - exactly like a database-table. No merged cells (bleugh!) or odd notes: Nothing other than the table!!

Anyways, on opening the file you might create two global variables:

Dim lngRows As Long
Dim lngCurrentRow As Long
lngRows = Worksheets("DataTable").Range("A1").CurrentRegion.Rows.Count
lngCurrentRow = 1    'don't forget to account for the header-row in your code

It is possible to create Classes in VBA, representing the current row. Depends how professional you want to be ;).

Good luck!

You should also (eventually) set the data-worksheet's Visible property to xlVeryHidden so that it can only be unhidden with code.
Was This Post Helpful? 1
  • +
  • -

#10 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 08:49 AM

Quote

A little work...


Riiiiiiight lol ^^

While I hope to never have a similar project again, I am trying to code it as professionally as I can manage. I've managed some headway, getting the first record in the table to load in the fields on open.

Now I'm having issues with some Get/Set Functions that I'm trying to use to retrieve and update the value of a global variable (the global variable "row" denotes which row to look at when loading data from the table).

I created a module called "GetSetRowValue" and have the below code:

Public row As Integer

Public Function GetRowValue()
    Return row
End Function

Public Function IncreaseRowValue() As Integer
    Set row = row + 1
End Function

Public Function SetRowValue(value As Integer)
    row = value
End Function


In my vba for the workbook on_Open() I use SetRowValue(2) to load the form for the first time. I'm now trying to use IncreaseRowValue() on the click event of my NEXT button, but it keeps saying expected "="? It also appears vba does not like my attempts to use "Return" when getting the value of the global variable?

I'm not seeing why my SetRowValue(2) in the opening code works, but the editor is throwing a hissy-fit when I type IncreaseRowValue()?
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 09:05 AM

If you receive error messages you should post them together with confirmation of which line they refer to in your (posted) code.

The first thing I would do is to change the name from "row"; Row is a property in the Excel Object Model. iRow, intRow even aRow would suffice. (If there might be more the 32767 rows then you should be using Long rather than Integer.)

You do not use Set with an Integer (primitive) variable, only with object variables.

If your row-variable is going to be global then your functions are IMO redundant, particularly as they are each a single line. Just make these changes inline.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 09:11 AM

Alternatively, you might make your row-variable a property of ThisWorkbook using Property Let/Get.

Added: There is an introduction to VBA Classes here. I'm not suggesting you have to use these - it's up to you ;).

This post has been edited by andrewsw: 04 June 2013 - 09:17 AM

Was This Post Helpful? 0
  • +
  • -

#13 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 11:35 AM

It's slow, but it's progress. How do I retrieve the the last filled value in a column?

For instance, when users go to add a new record, I want to auto-fill the REPTID field by adding in the next appropriate value. I need to get the last entry from column B on my other worksheet, in this case "SR0252" is the value, and then fill the field on my form with value "SR0253".

Any help appreciated. Really pulling my own teeth today.
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 12,946
  • Joined: 12-December 12

Re: Use Excel Worksheet like Application

Posted 04 June 2013 - 12:06 PM

Dim strLastReptID As String
'if column B has no gaps..
strLastReptID = Range("B1").End(xlDown).Value
'otherwise, another way..
strLastReptID = Range("B20000").End(xlUp).Value

Personally, I would use the row-variable that I earlier defined (based on CurrentRegion) to locate the last cell using Offset.

Of course you'll then need to extract, and increment, the number from the ReptID.

This post has been edited by andrewsw: 04 June 2013 - 12:09 PM

Was This Post Helpful? 1
  • +
  • -

#15 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 222
  • View blog
  • Posts: 1,035
  • Joined: 25-June 12

Re: Use Excel Worksheet like Application

Posted 05 June 2013 - 06:26 AM

This may be another dumb line of questioning, but here goes:

Currently I use the Developer Tab and go to "Insert" and drag a command button onto the spreadsheet of my "form". I'm then prompted to assign a macro to it. I cancel out of that dialogue, change the text of the button, and then select the button and click "View Code". When clicking "View Code" a new module is created, thus I have different modules for each of my command buttons currently.

  • Can you show me how to get all of these commands in one macro (or even in the code of the worksheet (Sheet 2)) for ease of review?
  • Is there a way to change the name of my buttons from Button10, Button11, etc. to something more meaningful?

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2