Remover aspas em VBA

Replace(A, Chr(34), B),
onde
A = texto onde as aspas aparecem
B = o que será colocado no lugar das aspas
Chr(34) = Código referente às aspas

Anúncios

Converter texto para número e inserindo fórmulas pelo vba

Selecionar o Range e executar o código a seguir.

Public Sub Altera()
Dim rngCelula As Range
For Each rngCelula In Selection
rngCelula.FormulaLocal = rngCelula.Value
Next rngCelula
End Sub

Para inserir fórmulas:
Range(“B2”).Formula = “=[depreciação.xlsx]home!$A$2”

Altura de linha mesclada

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth

Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
End If
End With
End Sub

Exibir formulário através de variável

Para executar determinados formulários do VBA através de variáveis, é só usar o código a seguir:
Na verdade nem precisa declarar uma variável.
Simples, simples.
Como não pensei nisso antes?!?!?!?

       X = InputBox("Show which UserForm?: ")       VBA.UserForms.Add(X).Show

Fonte: http://support.microsoft.com/kb/182411/pt-br

Conveter formatos de dados no VBA

Sub ConvertToBoolean()Dim lNum As IntegerDim strText As StringlNum = 0MsgBox CBool(lNum)lNum = 1 'Or higherMsgBox CBool(lNum)strText = "A"MsgBox CBool(strText = "B")MsgBox CBool(strText <> "B")End Sub

Sub ConvertToByte()Dim lNum As DoublelNum = 169.85MsgBox CByte(lNum)End Sub

Sub ConvertToCurrency()Dim lNum As DoublelNum = 999.989876MsgBox CCur(lNum)End Sub

Sub ConvertToDate()Dim lNum As LongDim strText As StringlNum = 38543MsgBox CDate(lNum)strText = "February 14, 1995"MsgBox CDate(strText)strText = "5:45:55 PM"MsgBox CDate(strText)End SubSub ConvertToDouble()Dim vCurr, dDoublecCurr = CCur(234.456784)dDouble = CDbl(cCurr * 100.1)MsgBox dDoubleEnd Sub

Sub ConvertToDecimal()Dim vDec, decNumvDec = "10000000.0587"decNum = CDec(vDec + 1)MsgBox decNumEnd Sub

Sub ConvertToInteger()Dim vStr, intNumvStr = "1000.5"intNum = CInt(vStr)MsgBox intNumEnd Sub

Sub ConvertToLong()Dim vStr, lngNumvStr = "1000000.589765"lngNum = CLng(vStr)MsgBox lngNumEnd Sub

Sub ConvertToSingle()Dim vStr, sinNumvStr = "987654.589765"sinNum = CSng(vStr)MsgBox sinNumEnd Sub

Sub ConvertToString()Dim vStrvStr = 10000MsgBox IsNumeric(strNum)MsgBox IsNumeric(CStr(strNum))End Sub

Sub ConvertToVariant()Dim vStrvStr = 1000MsgBox CVar(vStr & "000" + 9)End Sub

Fonte: http://www.ozgrid.com/VBA/conversion-functions.htm

Controle MultiPage

Loop Through UserForm Controls

Excel VBA UserForms and their associated controls are a great way to present/collect

data from users. There are often occasions however when we need to loop through

all controls on a UserForm, or only certain specified controls.

Loop Through All Controls

Use the code below to loop through all Controls on a UserForm

Private Sub CommandButton1_Click()

Dim cCont As Control

For Each cCont In Me.Controls

‘DO STUFF HERE

Next cCont

End Sub

Loop Through Specific/Specified Controls

Use the code below to loop through only specified Controls on a UserForm.

Private Sub CommandButton1_Click()

Dim cCont As Control

For Each cCont In Me.Controls

If TypeName(cCont) = “TextBox” Then

‘DO STUFF HERE

End If

Next cCont

End Sub

Loop Through Specific Controls on a Specified Page of a MultiPage

Control

Use the code below to loop through only specific Controls on a specified page of

a MultiPage Control. Note that Pages(0) is always the first page of any MultiPage

Control.

Private Sub CommandButton1_Click()

Dim cCont As Control

For Each cCont In Me.MultiPage1.Pages(0).Controls

If TypeName(cCont) = “TextBox” Then

‘DO STUFF HERE

End If

Next cCont

End Sub

Loop Through Specified Controls on all Pages of a MultiPage Control

Use the code below to loop through specified controls on all pages of a MultiPage

Control.

Private Sub CommandButton1_Click()

Dim pPage As Page, cCont As Control

For Each pPage In Me.MultiPage1.Pages

For Each cCont In pPage.Controls

If TypeName(cCont) = “ComboBox” Then

‘DO STUFF HERE

End If

Next cCont

Next pPage

End Sub