Problems with a VB addin for Excel

[readingdancer]

Hi,

I have written an Excel add-in using VBA that I am now trying to
convert to a VB
addin using Visual Studio.

I have the add-in working, and my code adds menu items within Excel,
when
these are clicked they pop
up windows forms.

My problem is from these forms I am trying to access the Worksheets,
and I
seem unable to work out
how to access these objects? In the form_Activated subroutine I am
trying to
copy all the currently selected
cells into an array.

The VBA code I am trying to replicate within the VB Addin is:

Dim oCell As Range
Dim cnt As Integer, a As Integer

cnt = -1

FromAddress.Text = ""

MessageBox.Text = ""

For Each oCell In Selection

ReDim Preserve NumberToSendToo(cnt + 1) ' This is the array to store
the
valid numbers

If ValidateNumber(oCell.Value) Then ' This function checks that the
cells
contain valid (phone) numbers

cnt = cnt + 1

NumberToSendToo(cnt) = Convert2InternationalNumber(oCell.Value) '
This
function converts the numbers to the international format.

End If

Next

If (cnt + 1) > 0 Then

If (cnt + 1) = 1 Then

ValidNumberLabel.Text = "1 Valid Number Found" ' This is a label on
the
form used to show the number of valid numbers found

Else

ValidNumberLabel.Text = CStr(cnt + 1) + " Valid Numbers Found"

End If

Else

ValidNumberLabel.Text = "No Valid Numbers Selected"

Send_Command.Enabled = False

End If

If you can help you'll make my last few hours of frustration not feel
quite
so bad!

Thanks in advance,


Chris

 

[Peter T]

In a quick glance a couple of things you might change

Dim oCell As Range

Dim oCell As Excel.Range ' requires Excel checked in Project / references

For Each oCell In Selection

For Each oCell In xlApp.Selection

where xlApp has been Set to the Excel instance

or
dim wb as Excel.Workbook
dim ws as Excel.Worksheet
set ws = xlApp.ActiveWorkbook.ActiveSheet
For Each oCell In ws.Selection

In other words everything needs to be explicitly referenced back to xlApp,
including some Excel/VBA functions, eg

set r = xlApp.Union(r1,r2)

Another one to watch for

"Cells(a,b)" should not exist anywhere without being prefixed with a dot and
attached to a reference to the worksheet.

Regards,
Peter T

PS I just noticed

ValidNumberLabel.Text = CStr(cnt + 1) + " Valid Numbers Found"

shouldn't that be
ValidNumberLabel.Text = CStr(cnt + 1) & " Valid Numbers Found"

 

[readingdancer]

Thanks Peter

In the end I managed to get it working, your comments will I'm sure be
useful to someone else in the future!

Thanks again!

Chris