Excel 2003(VBA) - Custom Identifier / Function / UDF

Question

I'm currently rewriting a small stock system for my work, and trying to speed up the program as it's dog slow and I have only been doing VBA for 2 weeks now.

In Excel 2003 Edition.

My issue (I think) is creating a identifier(s).

I have two and they are as follows:

   Dim QuickView As String
   QuickView = ActiveWorkbook.Range("a1:c200").Copy


   Dim Stock As String
   Stock = ActiveWorkbook.Range("c1:c200").Copy

My users currently select a file(WORKBOOK) from an open dialogue and I am importing the data in the ranges specified.

However, when I come to call these functions I get "Object does not support this property or method".

im unsure if this should be a UDF, as i can't see anywhere where you can write your own VBA function opposed to write a function in VBA for Excel to use.

Answer 1

In your two examples, both "QuickView" and "Stock" should be variants, not strings.

Dim Stock As Variant
Stock = ActiveWorkbook.Range("c1:c200").Copy

Remember, you do NOT need to assign ranges to a variable in order to copy (or cut) cell values to another location. Instead, you can do it like this:

ActiveWorkbook.Sheets("Sheet1").Range("c1:c200").Copy
ThisWorkbook.Sheets("Sheet1").range("c1")

The convention is copy_from [SPACE] put_it_here.

Note: In my example above, the values would be copied into Sheet1 of the workbook that contains the running code. The workbook running the VBA is always ThisWorkbook.