PDA

View Full Version : Advice on storage data into array



Maradona_10
02-17-2012, 07:16 PM
Hi all,

This is my first post on the forum and I'm really excited.:hi:

So here it goes: I would like to have an advice ( to have a more efficient coding) on how to store data using arrays. I have an excel sheet with conductor types (rows) and its characteristics ( diameter, unit weight, strength etc... columns)

I was thinking to use many one dimension array (as many columns there are in the excel sheet) to do this:
arrayConductor ( "drake", "Curlew", etc.....)
arrayDiameter (15,30,32, etc....)
arrayUnitWeight (19,36,21, etc...)
arrayetc....

My problem is that:

1) Probably there is a more efficient way to do this (multi-dimensional arrays?)

2) It will be very hard to verify/correct if all the characteristics are on the right conductor. For example, for the conductor Curlew all data about it (diameter, unit weight, etc...) is on the second item on all array, but what about the 32th conductor? You gotta count the 32th data on every array. To me this is a great way to make errors.

Thank you in advance for your help.:thumb

GTO
02-18-2012, 12:47 AM
Greetings Maradona and welcome to VBAX :hi:

By what you have stated so far, a 2-dimensional array (the same thing as a worksheet) would seem to be the way to go.

Could you dummy up a worksheet with some data (preferably in .xls format) and attach it? I imagine it will help us see what you are wanting to do.

Again, welcome to the forum,

Mark

Paul_Hossler
02-18-2012, 08:06 AM
Also, what is the intended use of the data?

Validate inputs, use for later processing, etc.?



Paul

Maradona_10
02-18-2012, 11:32 AM
Hi Paul,

Well with all these data I would like to make a bunch of calculations (inputs) like wind pressure therefore you need the cable diameter, unit weight etc..

Thanks!

Maradona_10
02-18-2012, 11:39 AM
Greetings Maradona and welcome to VBAX :hi:

By what you have stated so far, a 2-dimensional array (the same thing as a worksheet) would seem to be the way to go.

Could you dummy up a worksheet with some data (preferably in .xls format) and attach it? I imagine it will help us see what you are wanting to do.

Again, welcome to the forum,

Mark
Thanks GTO,

I think ill go with a 2D array, but I would rather not attach a .xls to my code. I think the safest way to go (I could be wrong) is to insert the data into the code, so that nobody could modify it by accident. I work in an office where we all work on a network.

The macro I'm trying to develop needs all these basic data to later on make a bunch of calculation such as, wind pressure, conductor sag, thermal ampacity of the conductor, etc...

Thanks and this forum rocks!!!:bow:

Paul_Hossler
02-18-2012, 12:42 PM
I think the safest way to go (I could be wrong) is to insert the data into the code, so that nobody could modify it by accident.


Another way would to put the data into a worksheet, and then make it Hidden or VeryHidden

I've never like embedding a lot to data into the VBA

You can still load the WS data into an 2D array

If you have macros, are you planning to password protect the workbook?

If so, then a VeryHidden WS can only be made visible by VBA or someone with the WB's password

Paul

Maradona_10
02-19-2012, 04:57 PM
Thanks Paul,

Well I don't want to password protect the workbook, but only the vba code. I realize now that putting the data in the code could be very long and easy to make mistakes so I'll go with your idea.

I never used before the very hidden function, but I guess the way it works is just like the hidden function with a password?

Thanks!

Paul_Hossler
02-19-2012, 06:16 PM
If you protect the data sheet as VeryHidden, it can't be made visible by the user, only by VBA or access to the VBE.

So if you PW protect the VBA code, you don't have to protect the workbook

VeryHidden really doesn't have a PW, but I use a macro that is not exposed to the user and just run it to toggle. I just don't tell people the name of the macro :rotlaugh:

If you're interested, here's a little example

Paul