New to Excel programming

[Erik-wikstrom]

Hi

I am working on a project where I need to do some Excel programming,
while I have programming experience I have never done it in Excel
before and I have some questions that I hope you could help me with.

What is the best way to save data (the value of variables) in the
workbook so that I can retrieve them when I open the document the next
time? I have found the CustomProperties property of the Worksheets and
I was wondering if there was some better way.

Can I remove an object from the CustomProperties collection?

How do I add a newline to a string? The best I could do was to use the
String(1, 10).

Can someone explain the difference between a class and a module? Is a
module kind of like a C++ namespace?

It is possible to pass arguments by reference, is it also possible to
declare variables that are references or is that dependent on their
type?

It seems like it is not possible to add a user-defined type to a
Collection, is that true and what is the solution? Currently I do not
use UDTs instead I make all of them classes instead, considering that
most of them only contain a few public variables I get a lot of small
classes this way, is there a better way of doing things?

And lastly a kind of off-topic question that someone might have the
answer to: What is the best way to communicate between Excel and
MATLAB? I currently use COM, are there any better way?

 

[JMB]

I can't answer all of your questions, but I will try to help with a few of
them:

What is the best way to save data (the value of variables) in the
workbook so that I can retrieve them when I open the document the next
time?

Don't know if it is "best", but you can save values to a worksheet (which
can be hidden) and read/write the values to/from the worksheet using the
Workbook_Open and Workbook_Close event handlers (these event handlers s/b put
in the ThisWorkBook module. Select Workbook from the left drop box and
"Open" or "Close" from the right drop box and XL will set up a skeleton for
you to work with).

How do I add a newline to a string? The best I could do was to use the
String(1, 10)

You could use vbcrlf or Chr(10) to add a line break.

It is possible to pass arguments by reference, is it also possible to
declare variables that are references or is that dependent on their
type?

You can declare variables that reference objects, but they have to be
declared as a variant, object, or a specific object type (usually, the more
specific the better). Also, object variables require the Set keyword when
they are assigned to an object.
Dim wksTest As Worksheet
Dim rngTest As Range
Set wksTest = Worksheets("Sheet1")
Set rngTest = wksTest.Range("A1:B20")
msgbox(rngTest.Address(,,,true))

It seems like it is not possible to add a user-defined type to a
Collection, is that true and what is the solution? Currently I do not
use UDTs instead I make all of them classes instead, considering that
most of them only contain a few public variables I get a lot of small
classes this way, is there a better way of doing things?

I think you are correct that UDT's can't be added to collections. One
option is to store them in an array. If the size of the array is not known
until run time, check VBA help for "Redim"

Public Type TestType
Value1 As Long
Value2 As Long
End Type

Sub test()
Dim tt As TestType
Dim arr(1 To 10) As TestType

For i = LBound(arr) To UBound(arr)
arr(i).Value1 = i
arr(i).Value2 = i * 2
Next i

For i = LBound(arr) To UBound(arr)
MsgBox arr(i).Value1 & vbcrlf & arr(i).Value2
Next i

End Sub

 

[Bill Renaud]

<<How do I add a newline to a string?>>
In VBA code, I normally use vbNewline. It has a value of 13 decimal (same
as vbCr).

FYI: vbLf has the value 10 decimal, and produces nicer results if you are
pasting the results in a worksheet cell. You will see a line wrap in the
cell without the square box character.

Try these simple test routines:

Sub MsgBoxTest()
MsgBox "This is a test of line wrap." & vbNewLine & _
"This should be line 2 in the message.", _
vbInformation + vbOKOnly, _
"MsgBox Test of vbNewLine"
End Sub

Sub Test()
Cells(1, 1).Value = "Test" & vbNewLine & "this"
Cells(2, 1).Value = "Test" & vbCr & "this"
Cells(3, 1).Value = "Test" & vbLf & "this"
End Sub

After running subroutine Test, put the value 5 in cells B1:B3, and the
following formulas:
C1: =CODE(MID(A1,B1,1))
.... then fill down through C3.

 

[Erik-wikstrom]

On Nov 10, 5:39 am, "Bill Renaud" <[email protected]>
wrote:

[snip]

Thanks to both you and JMB for your answers.

 

[myemail.an]

And lastly a kind of off-topic question that someone might have the
answer to: What is the best way to communicate between Excel and
MATLAB? I currently use COM, are there any better way?

Try searching this forum and Matlab's with google groups.

I remember a colleague of mine who managed to control Excel quite
extensively from Matlab with ActiveX programming; I know it's
possible, but I don't remember the details, sorry.