Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Data Protection Best Practice: AKA: Real Sheet Protection Mushman(Woof!)[_2_] Excel Discussion (Misc queries) 4 December 30th 09 02:20 AM
Best Practice - Transfer Data Neon520 Excel Programming 0 February 17th 09 10:39 PM
Best Practice - Transfer Data Neon520 Excel Programming 2 February 17th 09 10:25 PM
Activate Sheet Best Practice (Y/N?) when xFer data between sheets? [email protected] Excel Programming 2 July 3rd 07 04:52 PM
Transfer data from XL into Access: best practice quartz[_2_] Excel Programming 2 June 24th 05 09:30 PM


All times are GMT +1. The time now is 12:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"