Converting Array to Integers

**cporter5** · 06-28-2010, 05:38 PM

So basically i have an Array called my Array declared as fallowing ...

Public MyArray(1 - 5) As Integer

The Array Is populated as fallowing ...


MyArray(1)=1 
MyArray(2)=2
MyArray(3)=3
MyArray(4)=4
MyArray(5)=5

What i want it to find a way to turn the Array of Integers into a Single Integer

If

MyArray(1)=1
MyArray(2)=2
MyArray(3)=3
MyArray(4)=4
MyArray(5)=5

then

Public ConvertedArray as Integer = 12345

Thank you

**shg** · 06-28-2010, 05:48 PM

    Dim ai(1 To 5) As Integer
    Dim i As Long
    
    ai(1) = 1
    ai(2) = 2
    ai(3) = 3
    ai(4) = 4
    ai(5) = 5
    
    i = WorksheetFunction.SumProduct(ai, Array(10000, 1000, 100, 10, 1))
    MsgBox i

**Leith Ross** · 06-28-2010, 05:53 PM

Hello cporter5,

Here is another way...

Sub ConvertArrayToInteger()

  Dim MyArray(1 To 5) As Variant
  Dim N As Integer

    MyArray(1) = 1
    MyArray(2) = 2
    MyArray(3) = 3
    MyArray(4) = 4
    MyArray(5) = 5

    N = CInt(Val(Join(MyArray, "")))
  
End Sub

**cporter5** · 06-28-2010, 07:40 PM

okay im having trouble with this stupid error .... Runtime error 9

my code looks like this ...

Dim Password(1 to 5) As Variant
Dim N As Integer

Public Sub Worksheet_Activate()
N = 1
End Sub



Sub Activate()
Dim i As Integer
i = CInt(Val(Join(Password, "")))
MsgBox i

End Sub



Sub Button1()

 Password(N) = 1
 N = N + 1
    
Dim shpTemp As Shape
    
    Set shpTemp = ActiveSheet.Shapes(Application.Caller)
    With shpTemp
        If .ThreeD.BevelTopType = msoBevelRelaxedInset Then
            .ThreeD.BevelTopType = msoBevelCircle
        Else
            .ThreeD.BevelTopType = msoBevelRelaxedInset
        End If
    End With
     
End Sub

The line Password(N)=1 comes up with the out of bounds error '9' ... i dont understand because N = 1 ... and 1 is between 1 and 5 .... this code should store the number 1 Password(1) and then move it so that Password(2) should be ready to accept an input.

**Leith Ross** · 06-28-2010, 08:28 PM

Hello cporter5,

It appears you defined a Worksheet event inside a standard VBA module...

Public Sub Worksheet_Activate()
  N = 1
End Sub

N will remain zero because this code will never be run. Cut this code from the module and following the directions below.

How to Save a Worksheet Event Macro

Copy the macro using CTRL+C keys.
Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
Left Click on View Code in the pop up menu.
Paste the macro code using CTRL+V
Make any custom changes to the macro if needed at this time.
Save the macro in your Workbook using CTRL+S

**cporter5** · 06-28-2010, 10:05 PM

it is a module ... that line of code i added in attempt to make it work .... i think the reason that it doesnt work is because N is never given the initial value of 1 ... and i dont know where to put it ... ideal i would like to have N set to 1 somewhere in a Module scope, because i have several procedures that use the N variable ...

**Leith Ross** · 06-28-2010, 10:34 PM

Hello cporter5,

It may be easier to find the problem if you post your workbook for review.

**cporter5** · 06-28-2010, 10:42 PM

Here is my Excel 2007 workbook ... Before the comments start ... i got bored waiting for response so i went a bit over bored on the graphic element ...

**Leith Ross** · 06-28-2010, 10:48 PM

Hello cporter5,

You are correct about the variable not be initialized. The easiest way is to initialize the variable when the workbook first opens. Copy this code into the ThisWorkbook module.

Private Sub Workbook_Open()
  N = 1
End Sub

**Leith Ross** · 06-28-2010, 11:02 PM

Hello cporter5,

You should declare your variables in Module3 as below:

Public Password(1 To 5) As Variant
Public N As Integer

**cporter5** · 06-28-2010, 11:27 PM

i made both of your changes ... but it didnt seem to fix the problem ... when i compile it still comes up with error number 9.... on that same line of code ...

**Leith Ross** · 06-29-2010, 12:05 AM

Hello cporter5,

I don't have Excel 2007 yet, so this workbook is a 2003 version of yours. I get don't error the subscript out range error. I get a error with the Shape properties. Try this out and see how it works.

**cporter5** · 06-29-2010, 12:23 AM

thanks its working now ... is there a line of code that i can add that will reset the macro ... right now the sequence can only be done once ... i would like as soon as its done once ... to be able to do the whole sequence 3 times ... so i would get 3 sets of a 5 digit number ...