[VBA] unit conversion using drop down list

**frankyuen1028** · 04-24-2016, 07:11 AM

Hi everyone,

This is my first post in this forum and nice to meet you all here.

I am a new learner of vba, so I still have a lot of technical stuff that I am not yet familiar with and you can think of me as a beginner.

The problem that I have with my database or I am trying to solve is that:

Background
- I have a table which contains some data lets say 2 by 2 for the sake of simplicity
- I would like to create a drop down list which contains different units for distance/lengths for example, meters, inches, and the original data table is in meters as a default
- when the user select an option from the drop down list, let's say inches, all the data on the table will convert into inches from meters.

A general overview
Input (one): an option from the drop down list
Process: all the data on the table times a constant value which depends on the input
Output (many): all the data on the table will be converted into different values that correspond to the input

My thoughts
I know there are always different methods to go about it, however, I would like to have a process that involves automatic calculation, for example, the developer only requires to have another worksheetwhich stores all the conversion units which will be used by excel to process the data, rather than having many different result tables for each unit which is manually obtained by timing each data with the constant.

I am not sure whether I have explained it clearly, feel free to ask any questions or provide suggestions that would achieve the same result.

Thanks a lot!

Frank

**vidhyakrr** · 04-24-2016, 07:21 AM

Please attach a sample file, will try to get an idea for the VBA

**cytop** · 04-24-2016, 07:54 AM

Easiest to do with 2 grids. The first contains the data in a known unit of measurement, the 2nd contains the converted values.

But it can be done with 1 grid, the only complication is that everything must be converted back to a default measurement system before converting again to the desired system.

For example - Add some numbers to G5:I7 (Totally arbitrary, it's just what I highlighted). To help validate the results make these simple units (1, 2, 3...), Add a Data Validation list to F3, I used 'Inches,Feet,Yards, MM, CM, M'. Inches are my 'standard', the factors below will have to change if that changes.

Add the following code to the worksheet code module

Please Login or Register  to view this content.

It's not the best code, but illustrates the idea. Also it may introduce rounding errors but I don't think you'll need that sort of precision. You can always format the cells with as many decimal places as needed as remove the Rounding in the code. Also, you cannot edit the values unless the 'standard' unit of measurement is selected.

If you decide to use 2 grids then it's a simple matter of looping through the 'static' grid calculating the new values in the second using whatever factor for the selection. No need for 2 passes.