Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation using VBA, best practice?
I have 11 columns of data I want to validate before printing a report.
The plan is to write VBA code to do validate cell contents in loops. In this case I want to see non-blanks in some columns, and either numeric or specific text strings in other columns. Is there a best practice, or is looping through every row of every column in question and checking the cell contents standard practice? TIA, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation using VBA, best practice?
I have 11 columns of data I want to validate before printing a
report. The plan is to write VBA code to do validate cell contents in loops. In this case I want to see non-blanks in some columns, and either numeric or specific text strings in other columns. Is there a best practice, or is looping through every row of every column in question and checking the cell contents standard practice? TIA, Mike You'll find doing this in memory orders of magnitude more efficient than looping cells directly. Simply 'dump' the entire range to be vaidated into a variant to get a 2D array. Then loop the array as desired... Dim vData, n&, j& vData = Range("A1:L100") For n = 1 To UBound(vData) For j = 1 To UBOund(vData, 2) 'loops row n col by col Debug.Print vData(n, j) For n = 1 To UBound(vData, 2) For j = 1 To UBOund(vData) 'loops col n row by row Debug.Print vData(n, j) HTH -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation using VBA, best practice?
On 4/24/2016 9:04 PM, GS wrote:
I have 11 columns of data I want to validate before printing a report. The plan is to write VBA code to do validate cell contents in loops. In this case I want to see non-blanks in some columns, and either numeric or specific text strings in other columns. Is there a best practice, or is looping through every row of every column in question and checking the cell contents standard practice? TIA, Mike You'll find doing this in memory orders of magnitude more efficient than looping cells directly. Simply 'dump' the entire range to be vaidated into a variant to get a 2D array. Then loop the array as desired... Dim vData, n&, j& vData = Range("A1:L100") For n = 1 To UBound(vData) For j = 1 To UBOund(vData, 2) 'loops row n col by col Debug.Print vData(n, j) For n = 1 To UBound(vData, 2) For j = 1 To UBOund(vData) 'loops col n row by row Debug.Print vData(n, j) HTH That definitely helps, thanks GS. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation using VBA, best practice?
That definitely helps, thanks GS.
Glad to help! Being a VBer yourself, you'll find working with VBA an easy transition. Be aware, though, that as of Office 2010 the macro language is VBA7 for both x32/x64 versions. FWIW I use fpSpread.ocx to duplicate my Excel apps as stand-alone Win apps (VB6.exe). The same code is mostly used for both, changes being how object refs are handled. Working with a spreadsheet is pretty much same as working with a grid control. Excel is just a glorified grid control (IMO) with its own set of methods, properties, and functions. One feature you may find valuable during your transition is the Macro Recorder. While the code it generates will always need cleaning up, it will point you in the right direction in terms of learning the Excel Object Model. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation using VBA, best practice?
On 4/25/2016 11:19 AM, GS wrote:
That definitely helps, thanks GS. Glad to help! Being a VBer yourself, you'll find working with VBA an easy transition. Be aware, though, that as of Office 2010 the macro language is VBA7 for both x32/x64 versions. FWIW I use fpSpread.ocx to duplicate my Excel apps as stand-alone Win apps (VB6.exe). The same code is mostly used for both, changes being how object refs are handled. Working with a spreadsheet is pretty much same as working with a grid control. Excel is just a glorified grid control (IMO) with its own set of methods, properties, and functions. One feature you may find valuable during your transition is the Macro Recorder. While the code it generates will always need cleaning up, it will point you in the right direction in terms of learning the Excel Object Model. Yes VBA is pretty easy to get quickly, that was a great feature for MS to add to the office suite. I ran into the 64 bit issue with a FindWindowLike module I had used on 32 bit machines, found this page so I could get he function declarations right. I haven't had a lot of programming jobs doing this but that may change soon, so this is good stuff to know. http://www.jkp-ads.com/articles/apideclarations.asp I know where to come if I get stuck :) I'll check out the macro recorder, I've never used it but it sounds like it could be quite useful. Thanks again for all of your help, I'm going to show the updated sheet to the manager Friday and see how she likes it, I think she'll be pleased seeing all of her ideas implemented and working. Best Regards, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Best Practice - Transfer Data | Excel Programming | |||
Best Practice - Transfer Data | Excel Programming | |||
Activate Sheet Best Practice (Y/N?) when xFer data between sheets? | Excel Programming | |||
Transfer data from XL into Access: best practice | Excel Programming |